Plan for PowerPivot Integration with Excel Services
Through PowerPivot for Excel and PowerPivot for SharePoint, SQL Server 2008 R2 introduces client and server components that pair with Excel client and server products, adding new capability for aggregating large-scale, heterogeneous data in Excel workbooks and then sharing those workbooks within a SharePoint 2010 farm.
On a SharePoint farm, PowerPivot server components and Excel Services perform query processing and rendering for published Excel workbooks that contain PowerPivot data. Excel Services is the rendering engine for PowerPivot workbooks that are published on SharePoint.
Note
Although Excel Services gives you the ability to edit a document on a server within a browser window, you cannot use online editing to edit a PowerPivot workbook. PowerPivot does not support read-write connections to data that is loaded in memory on an application server. To edit data, you must download the file to your local computer and open the workbook in a Excel 2010 application that has the PowerPivot for Excel add-in installed.
This topic explains how the server systems work together, how connections are established, and the configuration settings that enable seamless interaction.
This topic contains the following sections:
Server Dependencies
Data Requests and Excel Services and PowerPivot Processing
Server Connections Within the Farm
Server Configuration Settings
Server Dependencies
Farm topologies that include application servers that have Excel Services, but do not have a PowerPivot for SharePoint installation on the same computer, must be updated to use newer Analysis Services client libraries. For more information, see How to: Install the Analysis Services OLE DB Provider on an Excel Services Computer.
Data Requests and Excel Services and PowerPivot Processing
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 in a browser window. You can view the fully-processed workbook in a browser window or in an Excel 2010 desktop application that has the PowerPivot for Excel add-in installed.
The following diagram is high level illustration that 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 retrieve the workbook from the content library and saves it to disk. The Analysis Services service extracts the PowerPivot data from the workbook and loads it into memory. 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 or objects in a PowerPivot workbook are 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.
Server Connections Within the Farm
Connections to PowerPivot data originate from client or server applications that query PowerPivot data using the Analysis Services client libraries. The client libraries (either ADOMD.NET, AMO, or the Analysis Services OLE DB provider) determine whether the PowerPivot workbook is inside or outside the farm. The client libraries route the request differently depending on whether the workbook URL is internal or external to the farm.
Requests for inside the farm are sent directly to a PowerPivot service application, bypassing the PowerPivot Web service. The most common example of this type of request is for PowerPivot data embedded in an Excel workbook. In this scenario, Excel Calculation Services uses the Analysis Services OLE DB provider to find a PowerPivot service application in the farm. Direct connections to a PowerPivot service application are not restricted to just Excel Calculation Services. Other applications, such as Reporting Services reports or PerformancePoint dashboards, will also make direct connections to a PowerPivot service application if the report or dashboard that queries the data is running in the same farm.
Requests for outside the farm typically come from client or server applications that request just the PowerPivot data. The most common example of this type of request is when a PowerPivot workbook is used as an external data source in Report Builder or as a data source to another Excel workbook. In this scenario, outside connections retrieve just the PowerPivot data inside an Excel workbook, ignoring any data visualization and interaction objects that are also in the same workbook.
Outside connections are sent to the PowerPivot Web service in the source farm. The PowerPivot Web service finds and establishes a connection to a PowerPivot Service application, which in turn forwards the query to the Analysis Services instance that loads and returns the data. For more information about this service, see the PowerPivot Web Service (PowerPivot for SharePoint).
Note
Opening a PowerPivot workbook in Excel (for example, by entering a SharePoint URL to the workbook in the File Open dialog box) does not involve PowerPivot for SharePoint server components. Opening a server document in Excel 2010 downloads the file from SharePoint to the client workstation. In this scenario, the Excel desktop application and the PowerPivot for Excel add-in, via the in-process version of the Analysis Services VertiPaq engine, load and render the data locally.
The following diagram illustrates different paths a request might follow depending on whether the request is from Excel Services within the farm, or a request from a client tool such as Report Builder or Excel that run outside the farm. Requests for inside the farm use WCF connections to other service applications. Requests for outside the farm use HTTP connections that are received by the PowerPivot Web service.
.gif "component connections and transports")
Connections to PowerPivot data are formulated by the Analysis Services OLE DB provider. Services and applications that consume PowerPivot data must use one or more of the SQL Server 2008 R2 versions of the Analysis Services client libraries. These libraries are installed with PowerPivot for SharePoint or with PowerPivot for Excel. Because they are redistributable, you can also download and install them from the SQL Server 2008 R2 Feature Pack web page on this Microsoft web site.
Server Configuration Settings
Integration between PowerPivot for SharePoint and Excel Services works best if you adjust some of the Excel Services configuration settings.
In Trusted Locations, ensure that Excel Services is configured to use the following property values:
Maximum Workbook Size is set to 50 megabytes or more if your workbooks exceed this size
Maximum Chart or Image Size is set to 10 megabytes or more.
Allow External data is set to 'Trusted data connection libraries and embedded' to allow data connections to be processed using published connection information and embedded connection strings within the workbook file.
Warn on Data Refresh is turned off to allow thumbnail images in PowerPivot Gallery. You can turn this feature on, if desired, but be sure to add PowerPivot Gallery to trusted locations. For more information, see Create a trusted location for PowerPivot sites.
For more information about how to specify these settings in SharePoint Central Administration or through PowerShell script, see Install PowerPivot for SharePoint on an Existing SharePoint Server and PowerShell Configuration Script (PowerPivot for SharePoint).