PowerPivot for Excel
PowerPivot for Excel is an add-in to Excel 2010 that is used for adding and integrating large amounts of data in Excel workbooks. The data that you add to the workbook is stored internally, as an embedded PowerPivot database inside the .xlsx file.
You can use this database in Excel or in Power View reports. In Excel, the workbook provides all of the data visualization and interaction features. Using the PivotTable reporting skills you already have, you can build sophisticated workbooks that are based on rich analytical data that you build easily in Excel.
Power View reports can use the workbook as an external data source for live, ad hoc reporting in SharePoint. Power View is a premium Reporting Services feature that is part of the SQL Server 2012 release. It is a server-side report designer that is built into SharePoint so that you can create your reports online.
This topic contains the following sections:
Benefits of Using PowerPivot for Excel
Excel Version Requirements
Loading and Calculating PowerPivot Data on the Client
Upgrading from a Previous Version
Related Content
Benefits of Using PowerPivot for Excel
When you install the add-in, you get the following benefits:
A separate application workspace used for importing, relating, enriching, and cleansing data. The workspace opens over Excel, but stores the data in the same .xlsx file that Excel uses. Within the workspace, you can build an analytical database inside Excel when you import and relate data.
DAX expression language supports data manipulation and business logic. DAX lets you create formulas in the data layer. Expressions can reference any related table or column. You can use expressions to create calculated columns or measures, reformat data, and group data.
Data processing is through a local Analysis Services xVelocity in-memory analytics engine (VertiPaq) that compresses and loads data and makes it available to data visualization objects, such as PivotTables, in a worksheet. The engine runs in the Excel process. There are no administration or configuration tasks to perform. The engine is an internal component of the PowerPivot add-in to Excel.
A PowerPivot menu is added to the Excel ribbon so that you can work with data in the Excel workspace.
Data access is enabled through updated versions of AMO, ADOMD.NET, and the Analysis Services OLE DB provider. These providers are installed with the add-in and support connections to PowerPivot data.
A data feed provider for importing and refreshing data in the OData format.
To use the data interactively, you must either open it in Excel on a local computer that also has PowerPivot for Excel add-in, or you can access it remotely on a SharePoint server that has Excel Services and PowerPivot for SharePoint, or Reporting Services.
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.
.gif "GMNI_clientApp")
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 and Reporting Services in SharePoint Mode).
Loading and Calculating PowerPivot Data on the Client
PowerPivot for Excel includes the Analysis Services xVelocity in-memory analytics engine (VertiPaq) 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 xVelocity 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 xVelocity 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.
.gif "GMNI_InprocVertipaq")
Upgrading from a Previous Version
This version is the second release of PowerPivot for Excel. It supports several new features over the previous version, including new DAX functions, hierarchical data, and KPIs. For more information about upgrading, see Upgrade PowerPivot for Excel.