PowerPivot Overview for IT Administrators
Collaboration and document management on SharePoint is important to how you use the business intelligence solutions that you create in PowerPivot for Excel. This topic introduces the server features in PowerPivot for SharePoint that help you use and manage shared PowerPivot workbooks. It also explains the role of IT administrators in deploying the server and monitoring PowerPivot data access in a SharePoint environment.
What is PowerPivot for SharePoint?
Managing PowerPivot servers in SharePoint Central Administration
Tasks for the SharePoint administrator
Finding technical information about PowerPivot server components and operations
What is PowerPivot for SharePoint?
PowerPivot for SharePoint is a SQL Server feature that is available in the SQL Server 2008 R2 product release. It is not built into SharePoint, but you can install it alongside SharePoint to enable the following scenarios.
Term |
Definition |
View PowerPivot data in workbooks and reports using a web browser |
Viewing PowerPivot data in a web browser requires the PowerPivot server software that makes data interaction possible in a browser window. The PowerPivot server software loads the data in the background when you open a workbook or report that uses PowerPivot as a data source. Each time you slice, drill-down, and filter the data, the PowerPivot server software processes the operation in the background and provides the filtered or recalculated data. PowerPivot server software runs on SharePoint. On a SharePoint server, PowerPivot server components load data in memory on SharePoint application servers, recalculate data, and then unload the data when it is no longer used. The PowerPivot system is self-maintaining in that it monitors which PowerPivot workbooks are actively used. |
PowerPivot Gallery for rich preview and document creation options |
PowerPivot Gallery is special-purpose SharePoint library that is recommended for storing and viewing PowerPivot workbooks. Unique to PowerPivot Gallery is the ability to generate thumbnail images of worksheets and named ranges that visualize PowerPivot data. It also provides options for creating new reports and workbooks based on the existing workbooks you already published to PowerPivot Gallery. |
Schedule data refresh |
You can configure data refresh schedules for the PowerPivot workbooks that you own and publish to a SharePoint site. In the schedule configuration pages on SharePoint, you can specify workbook and data source connection information, and choose which data sources to update at different times. You can also view data refresh history to verify that your data is up to date. |
Monitor PowerPivot availability and operations |
PowerPivot Management Dashboard is added to SharePoint Central Administration when PowerPivot for SharePoint is installed. SharePoint administrators can use this dashboard to view reports about server health, query response metrics, and workbook usage data that identifies high-value PowerPivot workbooks on SharePoint. Administrators can also monitor overall data refresh operations through consolidated reports that show whether data refresh is succeeding or failing overall. Central Administration is an administrator-only application that is available to SharePoint administrators. If you want to view any of the reports or information that PowerPivot Management Dashboard provides, check with your SharePoint administrator. |
Querying PowerPivot Data in an Excel Workbook
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 PowerPivot for Excel.
The following diagram shows how a request for query processing moves through different SharePoint applications. 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.
Managing PowerPivot servers in SharePoint Central Administration
PowerPivot for SharePoint is fully integrated into the SharePoint environment. Administrators can use Central Administration to manage the services, service accounts, service associations, feature availability, event configuration, and processing modes.
As mentioned earlier, SharePoint administrators can also use the PowerPivot Management Dashboard to monitor overall server health and performance. If the built-in reports are not sufficient, an administrator can create custom reports in Excel to supplement or replace the reports that are provided.
Tasks for the SharePoint administrator
Although PowerPivot workbooks are published and maintained by the people who create them, SharePoint administrators support that effort by performing the following tasks:
Installing and configuring the PowerPivot server software. Only SharePoint administrators can add new software to a SharePoint server.
Installing and configuring Reporting Services in SharePoint integrated mode. Reporting Services is a SQL Server feature that you can install and configure for SharePoint integration. Reporting Services is required if you want to create reports based on published workbooks in PowerPivot Gallery.
Creating PowerPivot Gallery or other locations for storing PowerPivot data. This task can also be performed by a site collection administrator.
Setting up permissions on the libraries or locations where data is stored. This task can also be performed by a site collection administrator.
Monitoring server health by reviewing the reports in the PowerPivot Management Dashboard.
For more information, follow the links in the next section.
Finding technical information about PowerPivot server components and operations
PowerPivot for SharePoint is a feature of SQL Server 2008 R2. Refer to SQL Server 2008 R2 Books Online on TechNet or MSDN for technical information about the following areas: