Book description
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.
Sivakumar Harinath
is a Senior Test Manager on the SQL Server Analysis Services team and a
coauthor of Professional Microsoft SQL Server Analysis Services 2008
with MDX.
Ron Pihlgren is a 15-year Microsoft veteran and member of the
SQL Server Analysis Services team as a senior test manager.
Denny Guang-Yeu Lee is a member of the SQL Customer Advisory
team and a coauthor of Professional Microsoft SQL Server Analysis
Services 2008 with MDX.
