This content is part of the Essential Guide: Maximizing the benefits of Excel add-ins: PowerPivot and Power View
Problem solve Get help with specific problems with your technologies, process and projects.

Map data flow from Microsoft PowerPivot to Excel, SharePoint

Microsoft PowerPivot, when used with Excel and SharePoint, lets business users manipulate data in Excel workbooks. But how do PowerPivot components fit into those environments?

Microsoft PowerPivot provides an end-to-end system for creating and collaborating on Excel workbooks to deliver enterprisewide business intelligence (BI). To take full advantage of these capabilities, users must use PowerPivot in conjunction with Excel and Microsoft SharePoint Server, which means installing PowerPivot for Excel on client computers and PowerPivot for SharePoint on SharePoint farms.

Though PowerPivot for Excel and PowerPivot for SharePoint are interrelated, they are separate products and, as such, can be examined individually; this will help to conceptualize how each one works -- how the primary PowerPivot components fit into the Excel and SharePoint environments and how data flows within and between those environments. Keep in mind, however, that they’re meant to be used together for a single BI system.

For more on Microsoft PowerPivot

Learn how to import SQL Server data to Microsoft PowerPivot

Put the data to work; create a chart in Microsoft PowerPivot

Sound policy key to managing data quality in Microsoft PowerPivot

PowerPivot for Excel
PowerPivot for Excel is a SQL Server 2008 R2 add-in developed specifically for Excel 2010. The add-in lets you import millions of rows of data into an Excel workbook from one or more data sources, such as SQL Server, other relational database management systems, Microsoft Access, text files, SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), Excel workbook files and data feeds.

PowerPivot for Excel adds an extra window to the Excel workspace that’s used exclusively to manage the imported data and establish relationships between that data. You can then use the familiar Excel tools as well as additional PowerPivot tools to manipulate and analyze the information. Figure 1 provides an overview of how the PowerPivot components fit into the Excel environment. The PowerPivot add-in lets you create PowerPivot workbooks, which are enhanced Excel workbooks that support the PowerPivot features and host the imported data.

Figure 1. PowerPivot for Excel imports data into the Excel workbook and enhances Excel capabilities.Figure 1. PowerPivot for Excel imports data into the Excel workbook and enhances Excel capabilities.

PowerPivot for Excel includes the PowerPivot add-in assembly and the VertiPaq engine. The assembly loads PowerPivot into Excel and facilitates communication between PowerPivot and Excel’s COM Interop, a Component Object Model (COM) assembly that allows COM objects and .NET objects to interact. As a result, PowerPivot can call into the Excel object model and respond to events and callbacks raised by Excel.

The VertiPaq engine uses the SSAS object model to process PivotTable and PivotChart queries as well as Data Analysis Expressions (DAX) statements. It is the VertiPaq engine that lets PowerPivot import, filter and sort millions of rows of data within a single workbook.

The VertiPaq engine supports two additional components: 

  • VertiPaq database module, which uses embedded extensible markup language (XML) data, referred to as a custom XML part, to maintain the PowerPivot database. The XML part and data are embedded in the PowerPivot workbook.
  • Data cache, which stores temporary files during save operations. This is the only PowerPivot data that resides outside the PowerPivot workbook.

All the pieces that make up a PowerPivot workbook -- the PowerPivot data and Excel features, such as PivotTables and PivotCharts -- are embedded in a single XLSX file. After you create the file, you can post it to a SharePoint server configured with PowerPivot for SharePoint for collaboration with other users.

PowerPivot for SharePoint
Not surprisingly, PowerPivot’s implementation in SharePoint is a bit more complex than in Excel. PowerPivot works with Excel Services to let users view, manipulate and modify PowerPivot workbooks. Figure 2 shows the components that make up a PowerPivot for SharePoint implementation.

Figure 2. PowerPivot for SharePoint components integrate with the Excel Calculations Services in SharePoint Server.Figure 2. PowerPivot for SharePoint components integrate with the Excel Calculations Services in SharePoint Server.

PowerPivot workbooks are stored within the SharePoint content databases. The Analysis Services service, which drives SSAS, manages the data within the workbooks and makes it available to other components. The service also updates the data as part of the data refresh operations, which are data updates scheduled in the SharePoint Timer service, a Windows service that manages scheduled SharePoint-related tasks. By scheduling regular data refreshes, business users can ensure that the workbook data is kept up to date with data from the original data sources.

The Analysis Services service also interfaces with the PowerPivot System service, which processes requests for workbook data, monitors server health, coordinates load balancing requests and collects usage data. The PowerPivot System service interfaces with the PowerPivot Web service to make PowerPivot workbooks available to external applications such as Excel and SSRS.

Excel Calculation Services retrieves the PowerPivot data through the PowerPivot System service but accesses other workbook components directly from the SharePoint database. Excel Calculation Services interfaces with the following services to present the read-only workbooks to clients other than PowerPivot for Excel and SSRS:

  • PowerPivot Gallery, which provides users with a custom SharePoint content library for PowerPivot workbooks.
  • Excel Web Access, which renders PowerPivot workbooks on a Web page and lets users view and manipulate the data.
  • Excel Web Services, which facilitates programmatic access to the PowerPivot workbooks.

The Excel and PowerPivot components work together to let readers view and manipulate data in a PowerPivot workbook. However, the only way to create and modify PowerPivot data is by using an Excel client configured with PowerPivot for Excel. You can use Excel to create a workbook and then post it to SharePoint or you can retrieve a workbook from the SharePoint site, modify it on your workstation in Excel and then check the file back in. All other methods of accessing PowerPivot data through SharePoint are limited to read-only access. Users can still manipulate the data that they’re viewing, but their changes will not be propagated back to the SharePoint content library. For that, you need Excel.

Together, Microsoft PowerPivot for Excel and PowerPivot for SharePoint provide a BI system that lets business users analyze and share data. The implementations of these two products in their respective environments are, of course, complex, but you now have an overview of how the primary pieces fit together. From there, you can dig into the components in-depth or test the products to better understand the full power and benefits of PowerPivot.

Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Check out his blog, Slipstream.

Dig Deeper on Microsoft SQL Server Analysis Services (SSAS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.