Delen via

  • Artikel

Use PowerPivot Workbooks on SharePoint

After you create a PowerPivot workbook, you can share it with other people in all of the normal ways that you share files. In many cases, however, it is better to share the workbook by using SharePoint. When you publish the workbook to a SharePoint site that has PowerPivot enabled, anyone with permission can go to the URL address to view or modify the file, or use the workbook as a data source for Reporting Services reports or other PowerPivot workbooks.

When you use PowerPivot data in an Excel workbook or a Reporting Services report, you must have the PowerPivot software that loads and calculates PowerPivot data in the background. On a client workstation, PowerPivot for Excel provides the data processing software that loads and calculates the data. However, a server environment requires a server-based solution. PowerPivot server software, running on SharePoint 2010, provides the background processing that enables you to use a Web browser to access workbooks and reports that use PowerPivot data.

This topic contains the following sections:

Use PowerPivot Server Software on SharePoint 2010

Use PowerPivot Gallery on SharePoint

Use PowerPivot Workbooks in Other Scenarios

Use PowerPivot Server Software on SharePoint 2010

Publishing PowerPivot workbooks to SharePoint has numerous benefits, but the primary benefit is that people can slice and filter data in a server document in the same way that they do when using a local workbook in Excel.

Data interaction is supported for the workbook you view in a browser, without having to download the file to your workstation to view or use the data in Excel. Data access and interaction in a server environment requires that you have SharePoint 2010 as well as the PowerPivot server software. PowerPivot server software is not available outside of a SharePoint environment.

About PowerPivot for SharePoint

PowerPivot for SharePoint is additional server software in the SQL Server 2008 R2 product release that enables PowerPivot data access in SharePoint. 

PowerPivot for SharePoint adds the following functionality:

  • PowerPivot data access on a server. When you publish a workbook or report that uses PowerPivot data, the PowerPivot server loads the data on a SharePoint server and makes it available to workbooks or reports that consume the data. Although Excel Services and Reporting Services can render PowerPivot data that you add to workbooks and reports, these servers cannot load and calculate the PowerPivot data. The PowerPivot server software performs this role for PowerPivot data that is accessed in a SharePoint environment.

  • PowerPivot Gallery, a special-purpose SharePoint document library that provides rich preview and document management for published PowerPivot workbooks as well as other document types. You can use PowerPivot Gallery to share and access workbooks or Reporting Services reports, preview a large file without having to open it first, schedule data refresh, or create an entirely new workbook or report based on PowerPivot data already published to the gallery. For more information, see Use PowerPivot Gallery in this topic.

  • Automatic data refresh, to set your PowerPivot workbooks to refresh automatically with the latest data at a regularly scheduled interval.

  • Data feed management. You can create and use data service documents in SharePoint. PowerPivot for SharePoint adds the content type for service documents that point to URLs that provide the feed.

  • Usage reporting on PowerPivot data access in Central Administration. SharePoint administrators can help you determine who is using your reports and how often.

PowerPivot Gallery is a special-purpose library in SharePoint that provides a preview of Excel workbooks that contain PowerPivot data. It also adds options for configuring a data refresh schedule or creating a Reporting Services ad hoc report based on a published PowerPivot workbook.

PowerPivot Gallery is not a built-in feature of SharePoint. It must be added by a person who manages your site or application, on a SharePoint computer that has PowerPivot for SharePoint server components installed on it. After it is added, you can find and use it the same way you use Shared Documents.

PowerPivot Gallery combines an accurate representation of the file contents with facts about document origin. You can see immediately who created the document and when it was last modified. Preview varies depending on whether you use Gallery view or Theatre view with the library. In Gallery view, you can pause on individual worksheets in the workbook to bring a sheet into focus in the preview area.

Screenshot of report gallery at 30%

Theatre view has a different layout. The preview area is centered, with individual worksheets revolving to the front as you pause on them.

PowerPivot Gallery generates preview images of Excel workbooks and Reporting Services report definition (.rdl) files that let you see the document before opening it from the library. 

Previewing Workbooks

PowerPivot Gallery will create preview images for Excel workbooks that contain embedded PowerPivot data or have a connection to PowerPivot data that is published in a different workbook in the same gallery.

Most workbooks use embedded PowerPivot data. However, if the PowerPivot data is accessed through a connection to PowerPivot data in a second workbook, both workbooks must be in the same PowerPivot Gallery in order to generate a preview image. PowerPivot Gallery cannot connect to other sites or libraries to retrieve the data.

Preview images are based on how the workbook is rendered by Excel Services. The representation in PowerPivot Gallery should be identical to what you see when you view a workbook in the Excel Web viewer. However, preview has a limited surface area, and portions of a workbook or report might be trimmed to fit the available space. You can always open a workbook or report to view the document in its entirety. 

Previewing Reports

PowerPivot Gallery can create preview images for Reporting Services reports that meet the following criteria:

  • The report contains PowerPivot data.

  • Both the PowerPivot data and the report are in the same gallery.

Unlike Excel workbooks, reports never contain embedded PowerPivot data. This means that the PowerPivot data in a report is always accessed as an external data source when you open the report. Because PowerPivot Gallery cannot retrieve data from other servers or sites to create preview images, it won't create preview images for a report unless the workbook that contains the embedded PowerPivot data is published in the same library.

PowerPivot Gallery is a location on SharePoint. It is a document library, similar to Shared Documents, that a site owner can create as a location for storing PowerPivot workbooks. As with all libraries that are created on SharePoint, the site owner who creates it can give it any name, so you might need to ask the site owner for instructions on how to find the library if you do not see PowerPivot Gallery on your site. 

Excel 2010 provides several ways to save a file to a SharePoint library. You can use Save As to specify a fully-qualified SharePoint path to a library. Alternatively, in Excel 2010, you can enter part of a server URL and browse a SharePoint site using the Save As dialog box to find a library that you want to use. For more information, see Save to SharePoint.

PowerPivot Gallery adds an Open New Excel Workbook option so that you can work with published data in a new workbook. It also adds a Create Report Builder Report option so that you can build ad hoc reports based on the workbooks that are published to the library. Data source connection is created automatically so that all you have to do is design the report. For more information about how to create reports based on published workbooks, see Create a Reporting Services Report with PowerPivot Data.

Note

PowerPivot Gallery does not add or configure SQL Server Reporting Services. A SharePoint administrator must install and configure it for SharePoint integration.

Use PowerPivot workbooks in Other Scenarios

Although it is not recommended, you can use PowerPivot workbooks with partial or no server hosting.

No Server Support

You can use PowerPivot workbooks without any server software by sharing documents via public folders, e-mail, portable media, or file servers. In this scenario, anyone who wants to view PowerPivot data must have Excel 2010 and the PowerPivot for Excel installed on their workstation. This scenario is not recommended because it does not include server software that lets you view and access PowerPivot data in workbooks or reports that you open in a browser from a server. 

Limited Hosting in SharePoint

You can upload or publish PowerPivot workbooks to a SharePoint site that does not have Excel Services or the PowerPivot server features, but you will not be able to view that data in an online format. By itself, SharePoint does not include a data engine for loading PowerPivot data on a server. This means that reports or workbooks that consume PowerPivot data will not display that data when the report or workbook is opened in a browser. In this scenario, using SharePoint by itself, users who want to view documents that contain PowerPivot data must download the report or workbook to a client workstation that has Excel 2010 and PowerPivot for Excel installed.