• Article

PowerPivot for Excel

PowerPivot for Excel is an add-in to Excel 2010 that provides tools for adding and integrating large amounts of data in Excel workbooks. The add-in includes the following components:

  • A PowerPivot menu in the Excel ribbon.

  • A PowerPivot window that opens over Excel. It provides a design surface and tools for adding, relating, and enriching data.

  • A local Analysis Services VertiPaq engine that compresses and loads data and makes it available to data visualization objects, such as PivotTables, in a worksheet.

  • Updated versions of AMO, ADOMD.NET, and the Analysis Services OLE DB provider to support connections to PowerPivot data.

  • An ATOM data feed provider for importing and refreshing data in the ATOM format.

Excel Version Requirements

Creating an Excel workbook that contains PowerPivot data requires both Excel 2010 and the PowerPivot for Excel add-in. After you create the workbook, you can publish it to a SharePoint Server 2010 farm that has Excel Services and a deployment of SQL Server PowerPivot for SharePoint. PowerPivot workbooks can be opened in Excel 2007. However, Excel 2007 cannot be used to create or modify PowerPivot data or to interact with PivotTables or PivotCharts that use PowerPivot data. You must use Excel 2010 to get full access to all PowerPivot features.

To install the client application, you can download and run a setup program (PowerPivot_for_Excel.msi) that copies the program files to your computer.

To open the PowerPivot window, click the PowerPivot tab on the Excel ribbon. On the PowerPivot tab, click PowerPivot Window.

Client application screenshot

Utilizing the full capabilities of the PowerPivot for Excel requires that you have the 64-bit edition of Excel 2010. On the Windows platform, a 64-bit operating system and client application provides more addressable memory so that you can build workbooks that contain millions of rows of data. For more information about workstation requirements, see Hardware and Software Requirements (PowerPivot for SharePoint).

Loading and Calculating PowerPivot Data on the Client

PowerPivot for Excel includes the Analysis Services VertiPaq engine that runs in-process in Excel. The client application uses AMO and ADOMD.NET, which in turn use the Analysis Services OLE DB provider, to connect to and query PowerPivot data within the workbook.

On the workstation, the Analysis Services VertiPaq engine issues queries and receives data from a variety of data sources, including relational or multidimensional databases, documents, public data stores, or Web services. During data import and client-side data refresh, all data access to external sources is through the Analysis Services VertiPaq engine that runs inside the Excel process.

The following diagram illustrates PowerPivot data access from within Excel. It shows the components that support data processing. For more information about supported data sources and creating PowerPivot data, see the help that is installed with the PowerPivot for Excel add-in.

Client protocol stack for data import or refresh