PowerPivot Overview
PowerPivot refers to a collection of applications and services that provide an end-to-end solution for creating and sharing business intelligence using Excel and SharePoint.
PowerPivot integrates with Excel and SharePoint. In an Excel environment, PowerPivot for Excel provides a familiar authoring and analytical experience on the workstation. In a SharePoint farm, PowerPivot for SharePoint adds server-side applications and features that support PowerPivot data access and management for workbooks that you publish to SharePoint. PowerPivot server components load the data, process queries, perform scheduled data refresh, and track server and workbook usage in the farm.
.gif "3 tier diagram of client, middle, backend add-ins")
This topic contains the following sections:
What is PowerPivot for Excel
What is PowerPivot for SharePoint
Querying PowerPivot Data in SharePoint
Who is PowerPivot for?
What is PowerPivot for Excel?
PowerPivot for Excel is an authoring tool that you use to create PowerPivot data in an Excel workbook. You use Excel data visualization objects such as PivotTables and PivotCharts to present the PowerPivot data that you embed or reference in an Excel workbook (.xlsx) file.
PowerPivot for Excel supports self-service business intelligence in the following ways.
Current row-and-column limitations in Excel are removed so that you can import much more data.
A data relationship layer lets you integrate data from different sources and work with all of the data holistically. You can enter data, copy data from other worksheets, or import data from corporate databases. You can build relationships among the data to analyze it as if it all originated from a single source.
Create portable, reusable data. Data stays inside the workbook. You do not need manage external data connections. If you publish, move, copy, or share a workbook, all the data goes with it.
PowerPivot data is fully and immediately available to the rest of the workbook. You can switch between Excel and PowerPivot windows to work on the data and its presentation in PivotTables or charts in an interactive fashion. Working on data or on its presentation are not separate tasks. You work on both together in the same Excel environment.
PowerPivot for Excel lets you import, filter, sort many millions of rows of data, far beyond the one million row limit in Excel. Sort and filter are extremely fast because the operations are performed by a local Analysis Services VertiPaq processor that runs inside Excel.
More importantly, PowerPivot for Excel lets you build relationships between data that is from completely different data sources by mapping columns that contain similar or identical data. When you build relationships in the data, you create something entirely new in Excel that can be used in PivotTables, PivotCharts, or any Excel data presentation object.
Saved data is stored inside the Excel workbook. The data is highly compressed, creating a file that is a manageable size on a client workstation.
In the end, the user has a single workbook (.xlsx) file that contains embedded data that is extracted and processed by an internal processor, but rendered exclusively through Excel. Compression and processing is through the Analysis Services VertiPaq engine. Query processing runs transparently in the background to provide the large scale data support in Excel. Sorting and filtering is very fast because it is performed by the local Analysis VertiPaq engine.
What is PowerPivot for SharePoint?
PowerPivot data in an Excel workbook is an Analysis Services data source that requires an Analysis Service server instance in SharePoint integrated mode to load the data and respond to interactive queries that are issued from an Excel workbook. On a client workstation, the Analysis Services server runs in-process within Excel. On a SharePoint farm, Analysis Services runs on an application server where it is paired with the PowerPivot System Service to handle server-side requests for PowerPivot data. Sharing PowerPivot workbooks in a farm is enabled through Excel Services and SQL Server PowerPivot for SharePoint.
PowerPivot for SharePoint adds services and infrastructure for loading and unloading PowerPivot data. In contrast with other large, corporate BI data sources that are limited in number and closely managed by database specialists, PowerPivot data is managed by services and infrastructure. At any given time, there might be tens or hundreds of PowerPivot workbooks open in memory on application servers in the farm. The PowerPivot System Service tracks this activity, setting up new connections to data that is already loaded in memory, and caching or unloading data if it is no longer used or when there is contention for system resources. Server health data and other usage data is gathered and presented in reports to give you insight into how well the system is performing.
Querying PowerPivot Data in SharePoint
When you view a PowerPivot workbook from a SharePoint library, the PowerPivot data that is inside the workbook is detected, extracted, and processed separately on Analysis Services server instances within the farm, while Excel Services renders the presentation layer. You can view the fully-processed workbook in a browser window or in an Excel 2010 desktop application that has the PowerPivot add-in.
The following diagram shows how a request for query processing moves through the farm. Because PowerPivot data is part of an Excel 2010 workbook, a request for query processing occurs when a user opens an Excel workbook from a SharePoint library and interacts with a PivotTable or PivotChart that contains PowerPivot data.
.gif "Data processing request diagram")
Excel Services and PowerPivot for SharePoint components process different parts of the same workbook (.xlsx) file. Excel Services detects PowerPivot data and requests processing from a PowerPivot server in the farm. The PowerPivot server allocates the request to an Analysis Services service instance, which extracts the data from the workbook in the content library and loads the data. Data that is stored in memory is merged back into the rendered workbook, and passed back to Excel Web Access for presentation in a browser window.
Not all data in a PowerPivot workbook is handled by PowerPivot for SharePoint. Excel Services processes tables and cell data in a worksheet. Only PivotTables, PivotCharts, and Slicers that go against PowerPivot data are handled by the PowerPivot service.
Who is PowerPivot for?
SQL Server PowerPivot client and server applications support self-service business intelligence that puts powerful analytics within your reach, enabling you to find better information and insight into the numbers that drive decisions, objectives, and initiatives throughout your organization. Together, PowerPivot for Excel and PowerPivot for SharePoint provide new tools and infrastructure for:
Excel users who know how to structure, analyze, and calculate multidimensional data in workbooks and PivotTables.
SharePoint users who use team sites and document management features to store information and collaborate with colleagues.
Database professionals and IT pros who want to delegate business data development tasks to those who need the data most, but want to retain sufficient controls so that data can be secured, monitored, reproduced, and archived.