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:

clip_image001

For the Target Data Mapping, enter the following:

Expression:

KPIGoal(“Gross Margin KPI”)

Screenshot:

clip_image002

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:

clip_image004

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:

clip_image006

Also, don’t forget to set the calculation column on your new Trend target to be “Default“.

Alyson Powell Erwin

alysonp@microsoft.com


Comments (7)

  1. csherrill says:

    I’m not able to import KPIs form SSAS. From one of your earlier posts, I read that we need the SSAS .dll in the PPS server for this to work. My tech support wants specific instructions to do this. Is there a KB article I can direct him to?

  2. MSDNArchive says:

    If you can’t import, can you try the methods listed in the post to pull them in?  

  3. Pei Ling says:

    I've imported KPIs successfully but the thresholds for the status & trend do not match the value i've defined in SSAS. It shows the default (1,0.5,-0.5,-1), is there a setting to set the value of thresholds to follow definition in SSAS? Thanks!

  4. AjieLeksono says:

    I need a basic about generate KPI using MDX Please.

  5. AjieLeksono says:

    I need a basic instruction or blog or site about generating KPI using MDX Please.  Thanks

  6. Tyler Hall says:

    Thank you for this post. It helped me get a workaround while my Sharepoint person was fixing the wizard, and helped me understand how to modify the ones the wizard products so I can make it display the way I want. Thanks a ton!