Power Pivot Data Access
Applies To: SQL Server 2016
This topic describes the ways in which data is retrieved from a Power Pivot workbook that is published to a SharePoint library.
Power Pivot data is stored inside an Excel workbook. The connection string is a URL to a workbook on a SharePoint site.
Power Pivot data is most often used by the workbook that contains it, as the data behind PivotTables and PivotCharts. Alternatively, Power Pivot data can also be used as an external data source, where a workbook, dashboard, or report connects to a separate Excel (.xlsx) file in SharePoint and retrieves the data for subsequent use. Client tools that typically use Power Pivot data are Excel, Power View, other Reporting Services reports, and PerformancePoint.
On the desktop, the Power Pivot add-in uses AMO and ADOMD.NET to create, process, and query the Power Pivot data in the client workspace.
On a SharePoint farm, Excel Services uses the local MSOLAP OLE DB provider to connect to Power Pivot data. The provider sends the connection request to a Power Pivot for SharePoint server in the farm. That server loads the data, runs the query, and returns the result set.
When you view a Power Pivot workbook from a SharePoint library, the Power Pivot 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 Power Pivot add-in.
The following diagram shows how a request for query processing moves through the farm. Because Power Pivot 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 Power Pivot data.
Excel Services and Power Pivot for SharePoint components process different parts of the same workbook (.xlsx) file. Excel Services detects Power Pivot data and requests processing from a Power Pivot server in the farm. The Power Pivot 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 Power Pivot workbook is handled by Power Pivot for SharePoint. Excel Services processes tables and cell data in a worksheet. Only PivotTables, PivotCharts, and Slicers that go against Power Pivot data are handled by the Power Pivot for SharePoint.