Synopses & Reviews
With PowerPivot, Microsoft brings the power of Microsoft's business intelligence tools to Excel and SharePoint users. Self-service business intelligence today augments traditional BI methods, allowing faster response time and greater flexibility. If you're a business decision-maker who uses Microsoft Office or an IT professional responsible for deploying and managing your organization's business intelligence systems, this guide will help you make the most of PowerPivot.
Professional Microsoft PowerPivot for Excel and SharePoint describes all aspects of PowerPivot and shows you how to use each of its major features. By the time you are finished with this book, you will be well on your way to becoming a PowerPivot expert.
This book is for people who want to learn about PowerPivot from end to end. You should have some rudimentary knowledge of databases and data analysis. Familiarity with Microsoft Excel and Microsoft SharePoint is helpful, since PowerPivot builds on those two products.
This book covers the first version of PowerPivot, which ships with SQL Server 2008 R2 and enhances Microsoft Office 2010. It provides an overview of PowerPivot and a detailed look its two components: PowerPivot for Excel and PowerPivot for SharePoint. It explains the technologies that make up these two components, and gives some insight into why these components were implemented the way they were. Through an extended example, it shows how to build a PowerPivot application from end to end.
The companion Web site includes all the sample applications and reports discussed.
What This Book Covers
After discussing self-service BI and the motivation for creating PowerPivot, the book presents a quick, end-to-end tutorial showing how to create and publish a simple PowerPivot application. It then drilsl into the features of PowerPivot for Excel in detail and, in the process, builds a more complex PowerPivot application based on a real-world case study. Finally, it discusses the server side of PowerPivot (PowerPivot for SharePoint) and provides detailed information about its installation and maintenance.
Chapter 1, "Self-Service Business Intelligence and Microsoft PowerPivot," begins Part I of the book. This chapter describes self-service BI and introduces PowerPivot, Microsoft's first self-service BI tool. It provides a high-level look at the two components that make up PowerPivot - PowerPivot for Excel and PowerPivot for SharePoint.
Chapter 2, "A First Look at PowerPivot," walks you through a simple example of creating a PowerPivot application from end to end. In the process, it shows how to set up the two components of PowerPivot, and describes the normal workflow of creating a simple PowerPivot application.
Chapter 3, "Assembling Data," starts off Part II of the book, and explains how to bring data into PowerPivot from various external data sources. It also introduces the extended example that you will build in this and subsequent chapters.
Chapter 4, "Enriching Data," shows how to enhance the data you brought into your application by creating relationships and using PowerPivot's expression language, Data Analysis Expressions (DAX).
Chapter 5, "Self-Service Analysis," describes how to use your PowerPivot data with various Excel features, such as PivotTables, PivotCharts, and slicers to do analysis. Chapter 5 also delves further into DAX, showing how to create and use DAX measures.
Chapter 6, "Self-Service Reporting," shows how to publish your PowerPivot workbook to the server side of PowerPivot (PowerPivot for SharePoint), and make use of its features to view and update PowerPivot reports. It also shows how to use the data in a PowerPivot workbook as a data source for reports created in other tools such as Report Builder 3.0 and Excel.
Chapter 7, "Preparing for SharePoint 2010," is the first chapter in Part III of the book. It describes the components of SharePoint 2010 that are relevant for PowerPivot, and looks at how PowerPivot for SharePoint interacts with those components.
Chapter 8, "PowerPivot for SharePoint Setup and Configuration," provides instructions on how to set up and configure a multi-machine SharePoint farm that contains PowerPivot for SharePoint.
Chapter 9, "Troubleshooting, Monitoring, and Securing PowerPivot Services," gives tips on how to troubleshoot PowerPivot for SharePoint issues. It also shows how to monitor the health of your PowerPivot for SharePoint environment, and discusses relevant security issues.
Chapter 10, "Diving into the PowerPivot Architecture," describes at a deeper level the architecture of PowerPivot, both client and server. It also explains the Windows Identity Foundation and discusses the use of Kerberos in the context of PowerPivot for SharePoint.
Chapter 11, "Enterprise Considerations," talks about common PowerPivot for SharePoint enterprise considerations: capacity planning, optimizing the environment, upgrade considerations, and uploading performance.
Appendix A provides instructions for setting up the data sources that are used to build the SDR Healthcare extended example in Chapters 3 through 6.
Additionally, two "bonus" elements are available online at this book's companion Web site:
- Appendix B is a comprehensive DAX reference that describes all the DAX functions and provides code snippets that show how to use them.
- A special chapter describes real-world scenarios in which PowerPivot is used to solve common problems.
- When used with the MDX query language, SQL Server Analysis Services allows developers to build full-scale database applications to support such business functions as budgeting, forecasting, and market analysis.
- Shows readers how to build data warehouses and multi-dimensional databases, query databases, and use Analysis Services and other components of SQL Server to provide end-to-end solutions
- Revised, updated, and enhanced, the book discusses new features such as improved integration with Office and Excel 2007; query performance enhancements; improvements to aggregation designer, dimension designer, cube and dimension wizards, and cell writeback; extensibility and personalization; data mining; and more
Professional SQL Server Analysis Services 2008 with MDX
shows readers how to build sophisticated business intelligence solutions using SQL Server Analysis Services, along with SQL Server Integration Services and Reporting Services. The authors begin with an overview showing how Analysis Services and MDX can be used to build data warehouses and multi-dimensional databases. They then quickly move to a tutorial that describes the development process for building dimensions and cubes from various data sources.
The authors next show how MDX can be used to query databases to provide sophisticated analysis of business problems. They will demonstrate a variety of techniques against real-world case scenarios. They will also show how Analysis Services can be used with other components of SQL Server, including Integration Services, Reporting Services, and Data Mining to provide comprehensive, end-to-end solutions.
The 2008 book will be nearly 50ew and revised to cover new features and enhancements, including:
- Improved integration with Office and Excel 2007
- Query performance enhancements
- Improvements to aggregation designer, dimension designer, cube and dimension wizards, cell writeback
- Extensibility and personalization
- Data mining
- New features such as Extensibility support, and possibly read-only databases
- Extensive changes and enhancements in the SQL Server Analysis Services Tools in Business Intelligence Development Studio / SQL Server Management Studio.
The new features of Analysis Services 2008 make it even easier to use andbuild your databases for efficient and improved performance. This authoritative book, written by key members of the Analysis Services product team at Microsoft, explains how to best use these enhancements for your business needs. The authors provides you with valuable insight on how to use Analysis Services 2008 effectively to build, process, and deploy top-of-the-line business intelligence applications.
You'll explore everything that Analysis Services 2008 has to offer and examinethe important features of this product with the help of step-by-step instructionson building multi-dimensional databases. Within each chapter, you will not only learn how to use the features, but you'll also discover more about the features at a user level and what happens behind the scenes to make things work. You'll get a look at how features really operate, enabling you to understand how to use them to their full potential. Plus, you'll sharpen your ability to debug problems that you might not have been able to otherwise.
What you will learn from this book
The basic concepts of using Analysis Services and the common operations you need to design your databases
How to create multi-dimensional databases (such as multiple measure groups, business intelligence wizards, key performance indicators, and more)
Methods for extending MDX via external functions
Ways to administer your Analysis Services programmatically and design and optimize your cube for best performance
How data mining along with Microsoft Office 2007 makes it easy to use and effective to perform analysis on data
Who this book is for
This book is for database and data warehouse developers and administrators interested in exploiting the power of business intelligence and leveraging the SQL Server 2008 tool set.
Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
About the Author
was born in Chennai, India. Siva has a Ph.D. in Computer Science from the University of Illinois at Chicago. His thesis title was "Data Management Support for Distributed Data Mining of Large Datasets over High Speed Wide Area Networks." Siva has worked for Newgen Software Technologies (P) Ltd., IBM Toronto Labs, Canada; National Center for Data Mining, University of Illinois at Chicago; and has been at Microsoft since February of 2002. Siva started as a Software Design Engineer in Test (SDET) in the Analysis Services Performance Team and currently is a Senior Test Lead in the Analysis Services team. Siva's other interests include high - performance computing, distributed systems, and high-speed networking. Siva is married to Shreepriya and has twins, Praveen and Divya. His personal interests include travel, games, and sports (in particular carrom, chess, racquet ball, and board games). You can reach Siva at Sivakumar.email@example.com.
Matt Carroll is currently a Senior Development Lead on the SQL Server Integration Services team at Microsoft. Prior to this, he spent 10 years working on the SQL Server Analysis Services team as a developer and then development lead. He's presented on Analysis Services at VSLive and compiled and edited the whitepaper "OLAP Design Best Practices for Analysis Services 2005."
Sethu Meenakshisundaram has more than 20 years of Enterprise System Software Development experience. Sethu spent a good portion of his career at Sybase Inc. in architecture, development, and management building world class OLTP and OLAP Database Systems. Sethu was instrumental in developing and leading highly complex clustered systems of Adaptive Server Enterprise. Early in the '90s, Sethu developed a version of Sybase Adaptive Server running on the Windows platform. Most recently he was an Architect in the SQL Server BI team driving technology and partner strategy. Prior to Microsoft, Sethu managed all of Server development as Senior Director at Sybase including building teams in the U.S., India, and China. He is currently a Vice President in charge of Technology Strategy at SAP Labs, USA.
Rob Zare is a program manager on the SQL Server development team. He's worked on the product since shortly before the first service pack of SQL Server 2000. During that time, he's focused primarily on Analysis Services, though for the next major release of SQL Server he'll be focused on Integration Services. He is the co-author of Fast Track to MDX and regularly speaks at major technical conferences around the world.
Denny Lee is a Senior Program Manager based out of Redmond, WA in the SQLCAT Best Practices Team. He has more than 12 years experience as a developer and consultant implementing software solutions to complex OLTP and data warehousing problems. His industry experience includes accounting, human resources, automotive, retail, web analytics, telecommunications, and healthcare. He had helped create the first OLAP Services reporting application in production at Microsoft and is a co-author of "SQL Server 2000 Data Warehousing with Analysis Services" and "Transforming Healthcare through Information [Ed. Joan Ash] (2008)". In addition to contributing to the SQLCAT Blog, SQL Server Best Practices, and SQLCAT.com, you can also review Denny's Space (http://denster.spaces.live.com). Denny specializes in developing solutions for Enterprise Data Warehousing, Analysis Services, and Data Mining; he also has focuses in the areas of Privacy and Healthcare.
Table of Contents
Part I: Introduction.
Chapter 1: Introduction to Data Warehousing and SQL Server 2008 Analysis Services.
Chapter 2: First Look at Analysis Services 2008.
Chapter 3: Introduction to MDX.
Chapter 4: Working with Data Sources and Data Source Views.
Chapter 5: Dimension Design.
Chapter 6: Cube Design.
Chapter 7: Administering Analysis Services.
Part II: Advanced Topics.
Chapter 8: Advanced Dimension Design.
Chapter 9: Advanced Cube Design.
Chapter 10: Advanced Topics in MDX.
Chapter 11: Extending MDX Using External Functions.
Chapter 12: Data Writeback.
Part III: Advanced Administration and Performance Optimization.
Chapter 13: Programmatic and Advanced Administration.
Chapter 14: Designing for Performance.
Chapter 15: Analyzing and Optimizing Query Performance.
Part IV: Integration with Microsoft Products.
Chapter 16: Data Mining.
Chapter 17: Analyzing Cubes Using Microsoft Office Components.
Chapter 18: Using Data Mining with Office 2007.
Chapter 19: Integration Services.
Chapter 20: Reporting Services.
Part V: Scenarios.
Chapter 21: Designing Real-Time Cubes.
Chapter 22: Securing Your Data in Analysis Services.
Chapter 23: Inventory Scenarios.
Chapter 24: Financial Scenarios.
Chapter 25: Web Analytics.
Appendix A: MDX Functions.