PowerPivot reports as a data source for PerformancePoint Services dashboards

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:
    PROVIDER=MSOLAP;DATA SOURCE=http://sitename/Library/PowerPivotFileName.xlsx.
    In our example (SalesResults.xlsx uploaded to PowerPivot Gallery) connection string looks like this:
    PROVIDER=MSOLAP;DATA SOURCE=http://contosodemo/PowerPivot%20Gallery/SalesResults.xlsx.



  • 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.

This entry was posted in PerformancePoint Services, PowerPivot, SharePoint Server and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s