Working with Tabular Data Sources in PerformancePoint Monitoring

With PerformancePoint Monitoring Server you can source data in scorecards from a variety of data sources, including Excel 2007, Excel Services and Relational Tables. When using Excel or Relational data sources, it is best to format your data in a specific manner in order to use this data most effectively in the Dashboard Designer. The Dashboard Designer allows you to import this data and then essentially, mark it up to look similar to a cube, flagging columns as either dimensions or fact data and specifying aggregation types for the columns. In order to use the data most effectively in a scorecard, formatting your data such that you have all dimensionality (like Time, Products, Customers, etc) of the data in a single column rather than across the top as rows. Your KPIs (metrics or numerical values) should appear across the top as rows. Let's look at an example of data in Excel:

Here we have some actual and forecast data for 2007. We want to utilize the Forecast data as a data source in Excel. What we will want to do to most effectively use this data in the Dashboard Designer is move the time dimension to a single column so that it can be marked as a dimension and used for rows/cols on the scorecard. Additionally, we need to fill in the bike classifications for column A. We will reformat our spreadsheet to look as follows:

Now, use the following steps to create your data source and scorecard:

1. From Dashboard Designer, right click on Data Sources and choose New Data Source.

2. Select Tabular List from the Category selection and select the Import from Excel 2007 Workbook template. Click OK.

3. Assign a name and optionally assign a display folder and permissions. Click Finish.

4. Select the View tab and click Edit in Excel.

5. Copy the data from your spreadsheet and paste it into the spreadsheet opened by the Dashboard Designer. Click Accept Changes. It should look as follows:

6. Note that all non-numeric columns will automatically be flagged as Dimension types, while all numeric columns will be flagged as Actual types with an Aggregation type of Sum. Make any adjustments needed by clicking on the column header and making the appropriate selections in the Details pane.

7. Publish the data source.

8. Right click on Scorecards and choose New Scorecard.

9. Select the Tabular category and the Excel 2007 Template. Click OK.

10. Assign a name, optionally assign a display folder and permissions. Click Next.

11. Select your data source. Click Next.

12. Click Add KPI. Set your Actual and Targets columns to the appropriate metrics and select the correct banding type. Click Next.

13. Repeat step 11 for each KPI you want to create.

14. Click Next. Click Finish.

15. Drag the member names that you see in the details pane to the workspace and place them on either the rows or columns. In this example, Time periods are added to the columns and products to the rows. Note, that because of the way we formatted our date, the product, region and time selections are all available as dimensions to be added to the scorecard.

16. Publish your scorecard.

17. You can now add your scorecard to a dashboard and create a filter on either the Product, Region or Time data.

Alyson Powell Erwin (alysonp@microsoft.com)