Introduction to PowerPivot for SharePoint 2010

topic source MSDN

Overview

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:

Highlight workbooks and the information that they contain from within SharePoint.
Refresh external connections to resources to keep the data current.
Schedule times to update the data automatically.
Reuse PowerPivot data from one workbook in other workbooks.

Download PowerPivot for Excel 2010

Code It

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

Click Site Actions and then select More Options.
Click Library in the Filter By section and then select PowerPivot Gallery.
Name the library Employment Reports and then click Create.
Figure 1. Creating a PowerPivot Gallery

Add the three sample files, Employment.xlsx, Economic_Data.xlsx, and PowerPivotExample.xlsx to the Employment Reports.

Figure 2. Default PowerPivot Gallery view is called the Gallery View

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

Navigate to the Employment Reports gallery.
In the Manage Views section, click the drop-down list under Current View, and then select Theater.

Figure 3. Theater View
Click the directional areas at the bottom of the page. As you click the arrows, the primary image on the screen reflects an image of a worksheet from a workbook in the Employment Reports gallery. In the bottom-left corner of the screen, SharePoint displays the name of the workbook that is associated with the current worksheet.
Repeat steps 2 & 3 except select the Carousel view instead of the Theater view.

Figure 4. Carousel View
Repeat steps 2 & 3 except select the Gallery view instead of the Carousel view.
Hover over the second worksheet from the first workbook in the gallery. SharePoint displays the image of the worksheet as the larger, primary image that is associated with the current workbook.

Note:
Power Pivot Galleries also contain an All Documents view, the standard view that is associated with document libraries in SharePoint 2010.

Interacting with Power Pivot workbooks

Power Pivot for SharePoint works hand-in-hand with Excel Services to let users interact with Power Pivot workbooks in a browser with extremely high fidelity and performance.

To interact with a Power Pivot workbook in the browser

Open the Employment Reports gallery in the Gallery view.
Click the second worksheet of the Employment.xlsx workbook. SharePoint opens the workbook in the browser and displays the Top 20 Cities by Payroll worksheet. This report contains two slicers. One slicer lets you select the year that is associated with the report; the other one lets you filter the report by state or by combination of states.
From the State slicer, select the slice that is labeled MN. The report updates to reflect the employment data from Minnesota.
Press and hold down the Ctrl key as you select the slice that is labeled FL. By holding down the Ctrl key, you can select multiple non-contiguous slices to add to a filter. In this case, the report updates to reflect the employment data from Minnesota and Florida.
Click the small filter icon that is immediately to the right side of the Row Labels heading.
Select City, Value Filters, and then click Top 10.
Change the value 20 to 100 to see the top 100 cities in Minnesota and Florida by Sum of Annual Payroll.
Click OK to see the results.

Enabling and Configuring Data Refreshes

Power Pivot 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

Select the applicable workbook, click the drop-down list that is next to the workbook name, and then select Manage Data Refresh.

Figure 5. Data refresh icon
On the Next Scheduled Refresh line, click Configure Schedule.
Select the box that is next to Enable to open the remaining fields.
Complete the remaining fields, and then click OK to save the schedule.

Important:

Make sure that you fill out the credentials section correctly for each data source; access the section by using the arrow to the right side of the data source name.

Important:
Make sure that you fill out the credentials section correctly for each data source; access the section by using the arrow to the right side of the data source name.

Figure 6. Data refresh document properties

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

In a PowerPivolt Gallery, locate the PowerPivot workbook that contains the desired source data.
Click the New Report icon.

Figure 7. New Report icon
Click Open.

Figure 8. Open or Save dialog box
If you are prompted about a security issue but have confidence in the data source, click Enable.

Figure 9. Security notice

Excel displays a new workbook with a PivotTable that is connected to the same data that is in the original PowerPivot workbook.

Figure 10. Workbook reusing data

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 avoids the row and column limitations in Excel.
PowerPivot adds a data-relationship layer that you can use to integrate data from different sources.
PowerPivot provides workbooks with portable, reusable data; data stays inside the workbook and there is no requirement to manage external connections.

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:

A special document library called the PowerPivot Gallery that is designed specifically for PowerPivot workbooks.
Unique document library views that simplify browsing and locating PowerPivot workbooks.
Support for interaction with PowerPivot workbooks in the browser.
Capability to automate data-refreshes.
Management capabilities such as reporting PowerPivot workbook usage.
Flexibility to add or remove whole installations to adjust the processing capacity across your farm as required by circumstances. Auto-discovery features in SharePoint and load balancing by the PowerPivot System Service ensure that the software finds and uses new instances automatically.

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.

Emad Adel