By: Christian Holslin

Introduction

This blog post explores a valuable, core feature of Microsoft PerformancePoint Services for SharePoint 2010.  Key Performance Indicators, or KPIs as they are commonly known, frequently represent a formula, like a Profitability Index, and not a single metric, like Gross Revenue.  PerformancePoint provides the perfect solution for business users seeking to leverage existing data warehouses: Calculated Metrics.

I will take you through the build process, step-by-step, starting from nothing and ending with a working scorecard with a custom KPI (as opposed to a pre-built Analysis Services KPI) leveraging a calculated metric.

Pre-requisites

To repeat this process in your own environment, you will need a working installation of SQL Server Analysis Services, SharePoint 2010, a Business Intelligence Center SharePoint site, and Microsoft’s AdventureWorks 2008 Family of Sample Databases.  You can download the sample databases from CodePlex here: http://msftdbprodsamples.codeplex.com/

Business Case

Our business case is simple: my regional sales manager wants to create a new sales metric without altering the existing, underlying data warehouse.  The trick is we need to see computed metrics that are not already available.  Without involving IT, we are going to use PerformancePoint Services to easily accomplish our goal.

Reporting Objective

KPIs generally only exist when we have a reporting objective.  Thus, for this walk-through we are going to find out where best to allocate sales promotions to help us drive more business.  We are going to tap into our sales data warehouse to find the most profitable product lines.  This calculated metric we are calling Profitability Index.

If get through the entire How-To, try adding more or different Dimensions to your new scorecard to see how the custom KPI can be applied to any entity in the data warehouse.

How-To

First, let’s see what we’re trying to achieve.  The following screen shot shows the final end product.  We won’t go into the details of formatting (to be saved for another article).  What I will focus on here is the Profitability Index metric and how it came to be.

We’re going to start from scratch so all the steps are clear.  First, open the PerformancePoint Dashboard Designer from your Business Intelligence Center.  Locate the PerformancePoint Content bucket in the Workspace Browser.

Next, right-click on the PerformancePoint Content bucket and select New KPI.  Pick the Blank KPI template when prompted.

Give your KPI a new name after it gets created.

We’re calling ours “Custom KPI.”  Now, make a note of the Custom KPI (or whatever you named yours) Editor tab in the middle of the Dashboard Designer to the right of the Workspace Browser.  We are going to keep the default Actual and Target values in place since this is what we want.  Obviously, you can change them as needed, but we’re not concerned with their names.  We are concerned with the Data Mappings for our Actual.  Next, we need to edit the Data Mappings so we can link the KPI to our Analysis Services cube.

Click on the highlighted “1 (Fixed values)” link in the Data Mappings column for the Actual row.  After the data source mapping dialog box appears, click on the Change Source button at the bottom.

You will be presented with a data source selection box.  Here is the key choice to make: switch to the Calculated Metric tab, scroll down to the Templates folder, and pick Blank Calculation.  This will allow us to specify a computed value for our Actual in our new KPI using any existing Measures in our Analysis Services Cube.

After you pick the Blank Calculation template, you need to decide which Measures you want to leverage in the Actual.  The Blank template provides you with two values by default.  Click on the Name column to change their names.  Next, click on the familiar “1 (Fixed values)” link in the Source column.  What we will do now is bind the SalesAmount field to the Sales Amount measure in our Cube.

After you click the link, another Data Source Mapping dialog will appear.  Click Change Source again to select the Analysis Services Cube data source.

NOTE: I am assuming that you have already created a data source connection to the AdventureWorks Data Warehouse Analysis Services Cube and saved it to SharePoint.  Click on the SharePoint Site tab to choose the data connection to Analysis Services.

After you click OK, you will need to select the Measure.  If you’re using the AdventureWorks data warehouse, pick Sales Amount.

Click OK, then repeat that same process for each Measure you need in your formula.  Finally, in the Formula box on the bottom, type out the formula for the Calculated Metric using the names of the mapped fields.

Click OK to save it and that’s it!  You’ve created a Calculated Metric for the Actual value in your new KPI.  Now, to get this into a Scorecard so we can check it out, go back to the PerformancePoint Content bucket in the Workspace Browser, right-click, and select New Scorecard.

When prompted to pick a Scorecard Template, be sure to pick the Analysis Services template and make sure that the Wizard option is checked.  This gives the Scorecard the ability to interpret the Dimensions in the Cube.

IMPORTANT: When creating the new Scorecard, select the same Analysis Services data source that you used when created the Calculated Metrics.

Next, select “Create KPIs from SQL Server Analysis Services measures.”  This will give you the option to pick your new Custom KPI that we just added.

In the next step, click on the “Select KPI” button to choose your new KPI.

Pick the KPI you just created.

You will see the KPI appear on the list of KPIs to import.  Continue through the wizard and leave the defaults selected for the rest of the menus.  You do not need to alter them to create the Scorecard.

If you’ve wired it all up correctly to Analysis Services, and you’re using the AdventureWorks data warehouse, you should see something like this when you’re done.

Granted, the Profitability Index of every single sales figure of all time (which is what you get by default), isn’t terribly useful, but that’s what you’re seeing in the above screen shot.  To make this more useful, and specifically to apply this new KPI to a set of Product Categories, look to the right of the Scorecard in the middle of the screen.  Use the navigation tool to browse the Dimensions.

Obviously, pick whatever you want, and then drag it onto the Scorecard as the Last Child of the KPI in the Scorecard.

After that you should see the Dimension members you selected appear in the Scorecard.

Click over to the Edit tab and click on Update to update the Scorecard.

If all is well, your Scorecard will update with the Calculated Metric for the Actual column.

By: Christian Holslin