How to use Excel Services as a Data source in PerformancePoint Monitoring

PerformancePoint Monitoring and Analytics (PPS M&A) supports the use of multiple data sources to use inside a Scorecard. One of the supported data sources are Excel Services. Excel Services are part of Microsoft Office SharePoint Services (MOSS) 2007.

The user can publish an Excel Sheet to Excel Services in multiple ways, PPS M&A however supports only Tables and Named Ranges to use as the data source. Excel Services is classified as a Tabular data source in PPS M&A. Other tabular data sources in the application includes: Excel 2007 Work book, SharePoint List, SQL Server Table.

The steps below outline how to publish an Excel Document to Excel Services and use the data from a table in Excel in a PPS Scorecard.

1. In Excel create a new Excel Sheet (or use an existing one). In this example we will create a simple table with four columns: Product, Market, Sales Amount and Target

2. The next step you need to is Publish this Excel Report to Excel Services.

3. You need to publish the Excel Document to a MOSS site where Excel Services is enabled. It is important that you choose Excel Services Options when publishing to Excel, since you need to specify which Items from the Excel Sheet you want to use as a data source

In this case we are only publishing the Items in the Workbook, and selecting the only table we had Table1

4. Once you have verified that the Excel Document is published, Excel will automatically display the table in your browser, after you have published the item. You can use the Excel Document as a data source.

5. In the Dashboard Designer, choose Create>>New Data source. From the list, select Tabular Data source and Excel Services.

6. In the Wizard, give the Data source a name and click Finish to select the Excel Services report

7. The Excel Services Datasource need information on which SharePoint Site, the Excel File is located on. Once you have selected the SharePoint site, the system will list all document libraries on the SharePoint Site, and the Excel Document within those document libraries

8. The next important thing is to type in the Item name you want to base the data source of, without the item name, you will not be able to get data from Excel, In our case the Table in Excel, used the default name of Table1

PPS Item Name

Same name used in Excel

9. You can now either click on the "Test Connection" to verify if the settings are correct, or go directly to the View Tab, where you set the column name, and column type. In our case, the columns Product, Market are dimensions, and Sales Amount and Target is our measures.

10. You are now ready to use the Excel Data as any other data source, and any changes in the Excel File on the server, will be updated in the Scorecard as well.

Leif Brenne (lbrenne@microsoft.com)