One of new features delivered in PerformancePoint Server 2010 is built-in support for PowerPivot reports as a data source.
After publishing PowerPivot report on Sharepoint 2010 you can use it to build interactive dashboards using PerformancePoint Services objects such Analytic Charts, Analytic Grids, KPI’s, Scorecards and Filters,
Here are basic steps how to prepare data source based on PowerPivot report:
1. Build your PowerPivot report in Excel 2010.
To create your PowerPivot report you need to have PowerPivot for Excel installed on your workstation. To make your report available as a data source, you need to upload it to SharePoint Server 2010 (site must have PowerPivot for SharePoint enabled).
Screens below show these activities.
2. After publishing your PowerPivot Report on SharePoint, you can connect to it from PerformancePoint Services Data Source.
The important note is that you must have ADOMD 10 installed on the server on which you are authoring the Dashboard in order to access a PowerPivot workbook as a data source.
3. In Dashboard Designer click Create tab, and then click Data Source.
- In the Category pane of the Select a Data Source Template dialog box, click Multidimensional and then click Analysis Services. Click OK.
- In the left navigation pane (workspace browser), type the name of your data source (in our example it is PowerPivotDataSource).
4. To configure your Data Source you have to set the following things:
- In the center pane, click the Editor tab. In the Connection Settings section, select Use the following connection.Type the connection string to the PowerPivot data source using the following format:
In our example (SalesResults.xlsx uploaded to PowerPivot Gallery) connection string looks like this:
- Select the cube from the drop-down menu. The cube name for a PowerPivot model will always be Sandbox.
- In the Data Source Settings section, select the method on which to authenticate to the data source. You can also set other attributes, like Formatting Dimension and Cache Lifetime.
- Now you can click Test Connection to check if your data source works correctly.
- If everything is OK you can click Save to store your object in MOSS list.
5. Since now you can create reports, KPIs and filters based on your new PowerPivot Data Source.