Introduction to PowerPivot for SharePoint 2010
Summary: Learn how to use Microsoft PowerPivot for SharePoint to extend the reach of Excel workbooks that contain PowerPivot data.
Last modified: September 12, 2012
Applies to: Excel 2010 | Office 2010 | VBA
Published: March 2011
Provided by: Steve Hansen, Microsoft Visual Studio MVP and founder of Grid Logic.
PowerPivot for SharePoint supports Excel 2010 workbooks that contain PowerPivot data and that are published to a SharePoint site. As a result, other users who do not have the PowerPivot add-in installed can view and interact with the workbook. In addition, PowerPivot for SharePoint has unique features to extend the capabilities of Excel workbooks that contain PowerPivot data. For example, you can do the following:
PowerPivot workbooks are stored in a special type of SharePoint document library called the PowerPivot Gallery. It is designed specifically for working with PowerPivot workbooks and provides special navigational and preview capabilities for PowerPivot workbooks. The following steps create a new PowerPivot Gallery named Employment Reports.
To create a PowerPivot Gallery
After you create the PowerPivot Gallery, you can modify the settings to your specifications. For example, if you change the library view to anything other than All Documents, you can navigate through workbooks and their respective sheets without having to open the workbook. To experiment with the various PowerPivot Gallery views, follow these steps:
To change the gallery view
Interacting with PowerPivot workbooks
PowerPivot for SharePoint works hand-in-hand with Excel Services to let users interact with PowerPivot workbooks in a browser with extremely high fidelity and performance.
To interact with a PowerPivot workbook in the browser
Enabling and Configuring Data Refreshes
PowerPivot for SharePoint can automatically refresh data in a PowerPivot workbook so that you do not have to open the workbook in Excel, refresh the data, and then save the workbook back to SharePoint.
To schedule a data refresh
Reusing PowerPivot Data in a new Workbook
One great feature in PowerPivot for SharePoint is the capability to reuse PowerPivot data across multiple workbooks. This feature enables you to create a new workbook that uses PowerPivot data associated with a separate workbook. For example, suppose that you must perform an analysis of employment data by state, and that in a previous analysis, you created a PowerPivot workbook that contains the data you need. You can reuse the PowerPivot data from your earlier analysis very easily by using PowerPivot for SharePoint.
To reuse data in a new workbook
Excel displays a new workbook with a PivotTable that is connected to the same data that is in the original PowerPivot workbook.
PowerPivot for Microsoft Excel 2010 is an add-in for Excel that enables you to create PowerPivot workbooks. A PowerPivot workbook is a special Excel workbook. It contains embedded data that is processed by an Analysis Services data engine that runs in-process with Excel. Some of the benefits that PowerPivot workbooks provide over standard Excel PivotTables include the following:
PowerPivot for SharePoint adds the necessary services and infrastructure to SharePoint to enable users to work with PowerPivot workbooks. Some of the features include the following:
Although this feature set is impressive, it is even more impressive when you consider the complexity of the code that runs to achieve this functionality. Just as Excel works hand-in-hand with an in-process instance of Analysis Services to provide PowerPivot functionality in Excel 2010, when you access a PowerPivot workbook in the browser via Excel Services, Excel Services uses a server instance of Analysis Services to aggregate the data while Excel Services handles the rendering.
Ultimately, the beauty of this functionality is that it enables information workers to access powerful Business Intelligence capabilities and to easily share knowledge with little or no involvement from IT professionals. For IT professionals, the SharePoint management and reporting capabilities provide what they need to keep systems running smoothly.
About the Author
Steve Hansen is the founder of Grid Logic, a Minnesota-based consulting firm specializing in business intelligence and information worker solutions. A developer, frequent author and speaker at technical conferences, Steve is a Microsoft MVP for his work with Visual Studio. One part code jockey, one part finance geek; Steve also has an MBA from the University of Minnesota with a concentration in finance.
Steve would like to thank Grid Logic's Sean Van Dyk for his assistance with this article.