Share via

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

  • Article

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

image

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

image

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

image

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

image

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

image

PPS Item Name

image

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.

image

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)

Comments

  • Anonymous
    August 22, 2007
    NOTE: This article was written with PerformancePoint Server 2007 (CTP3). UI and features subject to change.

  • Anonymous
    July 02, 2008
    Finally found a great step-by-step for excel and pps! Nicely done! Simple steps, and easy to follow...

  • Anonymous
    March 10, 2009
    I have followed these instructions and am able to click the "View" button beside "Excel Workbook" on the "Editor" tab and it opens the workbook. I can also click "Test Connection" and get the "Connection successful!" message. However, when I go to the "View" tab, I get the following error message: "Error in Connecting to Data Source. Please verify the connection details." I have tried recreating the workbook and recreating the connection, but still no luck. Any suggestions? Thanks! :)

  • Anonymous
    March 10, 2009
    Never mind. I found that all values in the table must be filled in - nothing can be left blank.