Specifications
189
C H A P T E R 1 0
Self-Service Analysis with
PowerPivot
M
any business intelligence (BI) solutions require access to centralized, cleansed data
in a data warehouse, and there are many good reasons for an organization to
continue to maintain a data warehouse for these solutions. There are even self-service
tools available that allow users to build ad hoc reports from this data. But for a variety of
reasons, business users cannot limit their analyses to data that comes from the corporate
data warehouse. In fact, their analyses often require data that will never be part of the
data warehouse, such as miscellaneous spreadsheets or text les prepared for specic
needs or data obtained from third parties that might be used only once.
Users can spend a great deal of time gathering data from disparate sources and then
manually consolidating and integrating the data in the form of one or more Microsoft
Excel workbooks. PivotTables and PivotCharts are popular tools for performing analyses,
but Excel requires all the data for these objects to be consolidated rst into a single table
or to be available in the form of a cube in a SQL Server Analysis Services database. What
does the user do when the insight is so useful that the spreadsheet needs to be shared
with others on a frequent basis with fresh data?
Sometime users are also constrained by the volume of data that they want to analyze.
Excel 2007 can support one million rows of data, but what if the user has data that is
more than a million rows? These users need a tool that enables them to analyze huge
sets of data without dependence on IT support.
Microsoft SQL Server 2008 R2 comes to the rescue for these users with two new
features to meet these needs—SQL Server PowerPivot for Excel 2010 and SQL Server
PowerPivot for SharePoint 2010. PowerPivot for Excel gives analysts a way to integrate
large volumes of data outside of a corporate data warehouse, whether they are creating
reports to support decision making or prototyping solutions that will eventually be
part of a larger BI implementation. To provide multiple users with centralized access to
reports developed with PowerPivot for Excel, information technology staff can implement
PowerPivot for SharePoint. This server-side PowerPivot product provides the necessary
infrastructure to manage, secure, refresh, and monitor these PowerPivot reports efciently.