Plan for PowerPivot Integration with Reporting Services

You can build Reporting Services reports as a way to visualize PowerPivot data. Reporting Services offers rich data visualization through charts and maps, additional rendering formats, and comprehensive subscription and delivery options.

This topic contains the following sections:

Version and Server Mode Considerations

Client Dependencies

Authentication and Authorization Considerations

Creating Reports based on PowerPivot data

Importing report data into a PowerPivot workbook

Publishing reports and workbooks to a server

Deploying Reporting Services and PowerPivot for SharePoint in the same farm

Version and Server Mode Considerations

Reporting Services can run on a broader range of SharePoint products and technologies than PowerPivot for SharePoint. In order to use the server products together in the same deployment, your SharePoint server or farm must be SharePoint Server 2010, with Excel Services, Secure Store Service, and Claims to Windows Token Service enabled. PowerPivot data access on a server is supported only through SharePoint 2010 servers. This is the only server deployment model that is supported.

The report server must be SQL Server 2008 R2 Reporting Services. This version includes the report data feed export feature that allows you to use report data in a PowerPivot workbook.

The report server can be operating in native mode or in SharePoint integrated mode, but you will benefit from a more seamless integration if the report server is configured for SharePoint integrated mode. For example, you can use the same set of SharePoint users, groups, and permissions to allow access to server operations and documents. Another benefit to deploying both server products in the same farm is the addition of a Report Builder launch command to PowerPivot Gallery. Starting reports from PowerPivot Gallery simplifies development because the data source connection is created for you.

A report server that runs in native mode supports the data feed export feature. You can import a report feed from a native mode report server and use it in a PowerPivot workbook on a client workstation. If you want to then publish that workbook to a SharePoint server, and schedule regular data refresh to retrieve newer data from that report, you will need to create a Secure Store Service target application to provide credentials that can be used to connect to a native mode report server. For more information, see Configure and Use Stored Credentials for PowerPivot Data Refresh.

Client Dependencies

Reporting Services includes two applications for authoring reports. Both Report Builder and Report Designer in Business Intelligence Development Studio (BIDS) can be used to create reports that include PowerPivot data. Unless you installed these applications on a computer that also has PowerPivot for Excel, you will need to install Analysis Services client libraries to support connections to a PowerPivot workbook that is accessed from a SharePoint site.

To support PowerPivot data connections from Report Builder, you must have SQL Server 2008 R2 ADOMD.NET on your workstation computer. This client library is installed with PowerPivot for Excel, but if you are using a computer that does not have this application, you must download and install ADOMD.NET from the SQL Server 2008 Feature Pack page on the Microsoft web site.

To support PowerPivot connections from Report Designer, you must have the SQL Server 2008 R2 OLE DB Provider for Analysis Services on your workstation computer. You can also download and install it from the SQL Server 2008 Feature Pack page if you are using Report Designer on a computer that does not have this version of the provider.

If the PowerPivot workbook is on your computer and not a server, you must have an installation of PowerPivot for Excel. PowerPivot for Excel provides a local data processor that loads and calculates PowerPivot data. It also includes the client libraries, so if you installed PowerPivot for Excel, you have all of required client software.

A report server that supports connections to PowerPivot data must also have the SQL Server 2008 R2 version of the ADOMD.NET provider. If PowerPivot for SharePoint is not installed on the same computer, you must download and install ADOMD.NET from the SQL Server 2008 Feature Pack page on the Microsoft web site.

Authentication and Authorization Considerations

Integration between a report server and a PowerPivot server requires Windows authentication. Windows authentication is used to support data refresh and data import scenarios for PowerPivot workbooks that are published to a SharePoint site. This means that the web applications for which you configure Reporting Services and PowerPivot feature integration must use the Windows authentication provider. In addition, the Claims to Windows Token Service must be running on SharePoint servers that have a deployment of PowerPivot for SharePoint.

Because PowerPivot data access on a server is through SharePoint, SharePoint permissions are used to control access to a PowerPivot workbook on a server. If you save Reporting Services reports to a native mode report server that is outside the farm, the report will need to have connection information that is composed of the SharePoint URL to the workbook, and the report user will need to be a SharePoint user, with SharePoint permissions to the workbook. You will need to map native mode report server role assignments to the equivalent SharePoint groups and permission levels on SharePoint. For example, a user who is assigned Browser permissions on a report server will also require View Only permissions on the SharePoint server.

Similarly, if you are using a native mode Reporting Services report as a data source for a PowerPivot workbook (that is, you are using the data feed export option in Reporting Services to import data to a PowerPivot workbook), the account of the SharePoint user who accesses the PowerPivot workbook must have a Reporting Services role assignment on the report server.

Saving reports and PowerPivot workbooks to the same SharePoint farm is easier to manage if the report server is configured for SharePoint integrated mode. You can use the same SharePoint users, groups, and permissions to control access to both document types.

Creating Reports based on PowerPivot data

You can use Report Builder or Report Designer in Business Intelligence Development Studio (BIDS) to create new reports based on PowerPivot workbooks. You can also start Report Builder from within PowerPivot Gallery to create new ad hoc reports based on PowerPivot workbooks that are published to the same location.

Only reports (.rdl), and not report models (.smdl), can be based on PowerPivot data.

In contrast with Excel reports that can include embedded PowerPivot data, Reporting Services reports always access PowerPivot data as an external data source. The data is never embedded in the report. For this reason, be sure that the authentication subsystems and permissions are defined correctly across the servers (see Authentication and Authorization Considerations in this topic for more information).

Report layout of PowerPivot data is based on the same techniques you use when working with Analysis Services data in a report. The underlying data interface is MDX, so you use the MDX query designer to create the datasets used in your report. For instructions on how to connect to PowerPivot data from each authoring tool, see Create a Reporting Services Report with PowerPivot Data.

Importing report data into a PowerPivot workbook

SQL Server 2008 R2 Reporting Services can stream report data as an Atom data feed directly to a PowerPivot workbook. You can immediately and easily repurpose a report to use its data for further analysis in PowerPivot.

You can use either Report Manager or report server pages in a SharePoint site to export report data to the PowerPivot window. PowerPivot for Excel must be installed on the computer if you want a seamless data transfer from the report to the workbook. If it is installed, the PowerPivot for Excel detects the data feed as it is streamed and opens tables in the PowerPivot window to contain the imported data. The report server streams the report as multiple data feeds to PowerPivot tables in the window. Each data region is imported as a separate, related table. If PowerPivot for Excel is not installed, you will be prompted to save the .atomsvc document that specifies a connection to the report. You can use this document in later import operations to generate data feeds from the report.

Reporting Services reports can be very valuable data sources for PowerPivot workbooks. Reporting Services includes rich features for querying a wider range of data sources, including integration with SQL Server Integration Services and SAP. Built-in data processing features can provide you with a report that contains data that is already calculated and aggregated, with scheduled updates that are at a finer grain than what you can get with other tools. Any report that includes valuable data that is already arranged into a useful format can be used in a PowerPivot workbook when you import that report into the workbook.

For more information about importing data, see Import Data from a Reporting Services Report.

Publishing reports and workbooks to a server

This section describes server features that apply to reports and PowerPivot workbooks.

Using Report Server features for Reports that contain PowerPivot data

Reporting Services includes server-side features for report snapshots, report history, subscription and delivery, and shared data sources. Setting up snapshots (either report execution snapshots or report history snapshots) for reports that contain PowerPivot data is useful if the PowerPivot workbook is also configured for data refresh. If the workbook is not specifically configured for data refresh, then the data is static, consisting of the rows and columns that were returned in the original import operation. However, if the workbook is configured for data refresh, you can benefit from using the snapshot and subscription features that Reporting Services provides.

Reporting Services also provides shared data sources that let you manage data source connection information separately from the reports that use that data source. You can create a shared data source that specifies Microsoft SQL Server Analysis Services as the type, with the connection string pointing to the SharePoint URL of a PowerPivot workbook. You can then use the shared data source in multiple reports to connect to the same PowerPivot workbook.

For more information about how to create a shared data source for a PowerPivot workbook, see Create a Reporting Services Report with PowerPivot Data. For more information about creating shared data sources in SharePoint, see How to: Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode).

PowerPivot Gallery is a PowerPivot for SharePoint feature that you can use as a common location for storing and accessing PowerPivot workbooks and Reporting Services reports. PowerPivot Gallery previews reports and workbooks as thumbnail images so that you can see what a document contains before you open it. A snapshot service renders each document and presents small images of each page.

For SharePoint web applications that also have report server feature integration, PowerPivot Gallery provides immediate access to Report Builder. You can use the Create a New Report option in PowerPivot Gallery to launch Report Builder using the PowerPivot workbook as a data source for the report.

Note that there are file proximity and permission requirements for enabling preview image generation. For reports, preview images are only generated if the report uses a PowerPivot workbook as a data source. In addition, reports and workbooks must be in the same library and secured with the same permissions. For more information, see Securing a PowerPivot workbook on SharePoint.

Deploying Reporting Services and PowerPivot for SharePoint in the same farm

Installing Reporting Services and PowerPivot for SharePoint in the same SharePoint 2010 farm enables the use of integrated features. These include PowerPivot Gallery, which offers rich preview for both PowerPivot workbooks and Reporting Services reports, easy access to Report Builder for creating reports based on PowerPivot workbooks, and data feeds that let you easily consume Reporting Services reports as data sources in a PowerPivot workbook.

To install Reporting Services and PowerPivot for SharePoint, you must run Setup twice, once to install PowerPivot for SharePoint, and again to install Reporting Services. Running Setup twice is necessary because PowerPivot for SharePoint is installed through setup roles, and these roles do not include Reporting Services.

There is no recommended installation order for installing these features. However, it is strongly recommended that you verify you have a working installation of one feature before installing the next feature.

Each feature component has its own set of configuration tasks for integrating the feature with SharePoint. Be sure that integration is configured correctly before adding new feature components to your deployment. For more information about installation scenarios, see How to: Install PowerPivot for SharePoint and Reporting Services.

For more information about configuration tasks, see Configuration (PowerPivot for SharePoint) and Configuring Reporting Services for SharePoint 2010 Integration.