Learn About PowerPivot Capabilities
PowerPivot for Excel is an add-in that you can use to perform powerful data analysis in Excel 2010, bringing self-service business intelligence to your desktop. PowerPivot for Excel includes a window for adding and preparing data, and a PowerPivot tab on the Excel ribbon that you can use to manipulate the data in an Excel worksheet.
PowerPivot for Excel also includes a wizard that you can use to import data from different sources, ranging from large corporate databases on your intranet, to public data feeds, to spreadsheets and text files on your computer. Data is imported into PowerPivot for Excel as tables. These tables are shown as separate sheets in the PowerPivot window, similar to worksheets in an Excel workbook. But PowerPivot for Excel provides significantly different functionality from what is available in an Excel worksheet. For more information about the PowerPivot user interface, see Take a Tour of the PowerPivot UI.
The data that you work on in the PowerPivot window is stored in an analytical database inside the Excel workbook, and a powerful local engine loads, queries, and updates the data in that database. The PowerPivot data can be further enriched by creating relationships between the tables in the PowerPivot window. And because PowerPivot data is in Excel, it is immediately available to PivotTables, PivotCharts, and other features in Excel that you use to aggregate and interact with data. All data presentation and interactivity are provided by Excel 2010; and the PowerPivot data and Excel presentation objects are contained within the same workbook file. PowerPivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.
In addition to the graphical tools that help you to analyze your data, PowerPivot includes Data Analysis Expressions (DAX). DAX is a new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex grouping, calculation, and analysis. The syntax of DAX formulas is very similar to that of Excel formulas, using a combination of functions, operators, and values.
Differences between Excel and PowerPivot Workbooks
As mentioned above, there are differences between the PowerPivot window and the Excel window in terms of how you work with data in each. There are some also other important differences that we want to call out specifically:
PowerPivot data can be saved in workbooks that have the following file types: Excel Workbook (*.xlsx), Excel Macro-Enabled Workbook (*.xlsm), and Excel Binary Workbook (*.xlsb). PowerPivot data is not supported in workbooks with other formats.
The PowerPivot window does not support Visual Basic for Applications (VBA). You can use VBA in the Excel window of a PowerPivot workbook.
In Excel PivotTables, you can group data by right-clicking a column heading and selecting Group. This feature is often used to group data by date. In PivotTables that are based on PowerPivot data, you use calculated columns to achieve similar functionality. For more information, see Grouping Dates.
Publishing Data to SharePoint
After you create a PowerPivot workbook, you can share it with others in all of the normal ways that you share files. However, by publishing the file to a SharePoint server or farm that has PowerPivot for SharePoint and Excel Services installed, you can gain many benefits. On the SharePoint server, the services work together to process the workbook: the PowerPivot for SharePoint components process the data, and Excel Services renders it in a browser window where other users can continue to analyze the data. For more information, see Share Data from PowerPivot.
PowerPivot services on SharePoint add collaboration and document management support for the PowerPivot workbooks that you publish to SharePoint. PowerPivot for SharePoint provides a new type of document library that allows rich previews of the reports in your PowerPivot workbooks, the ability to create Reporting Services reports from data in your published PowerPivot workbooks, and the capability of scheduling automatic refresh of your PowerPivot data from external data sources. For more information about integration with SharePoint, see PowerPivot for SharePoint in SQL Server 2008 R2 Books Online.