Creating PPS KPIs based on SSAS KPIs
There are two ways to create KPIs in PerformancePoint Server based on SSAS KPIs. The first is to use the Scorecard wizard to import those KPIs into PPS. This will create a new scorecard containing only those imported KPIs. I wrote a post on this some time ago.
If you want to add SSAS KPIs to your existing scorecards however, you can also create individual PPS KPIs based off of SSAS KPIs through the dashboard designer as well. It does require using some MDX expressions to create those KPIs. First, you will want to create a new blank KPI. Select the SSAS cube containing your KPI as your data source.
In the Data Mapping column rather than selecting a measure, you will enter an MDX expression as follows for the Actual value:
Expression:
KPIValue("Gross Margin KPI")
Screenshot:
For the Target Data Mapping, enter the following:
Expression:
KPIGoal("Gross Margin KPI")
Screenshot:
Add a new Target for Trend and map as follows:
Expression:
KPITrend("Gross Margin KPI")
NOTE: When importing your KPIs through the scorecard wizard you automatically get the correct threshold settings imported and the correct images imported for your Target and Trend. When you create these manually, you will have to select the indicator and enter your thresholds for both the Target and Trend columns.
To set the threshold on the Target column, select "Increasing is Better" for the scoring pattern. Select "Band by stated score (advanced) " for the banding method.
Select the indicator you want to show. Then, when specifying the data mapping for the threshold enter the following expression and select to "Use MDX tuple formula":
Expression:
KPIStatus("Gross Margin KPI")
Next, don't forget to set your threshold bands, for a typical 3 state indicator it would look as follows:
Recall, that the status in an SSAS KPI will always be between 1 and -1.
To set the threshold on the new Trend Target column, select "Increasing is Better" for the scoring pattern. Select "Band by stated score (advanced) " for the banding method.
Select the indicator you want to show. Then, when specifying the data mapping for the threshold enter the following expression and select to "Use MDX tuple formula":
Expression:
KPITrend("Gross Margin KPI")
Next, don't forget to set your threshold bands, for a typical 5 state arrow indicator would be as follows:
Also, don't forget to set the calculation column on your new Trend target to be "Default".
Alyson Powell Erwin