KPI Threshold Values From Table

Today i had a requirement to make Thresholds configurable in PerformancePoint's KPIs. In PerformancePoint we can make Target value to be stored in a separate table and use it. But in my case I was mostly working with percentages where target values is always 100% but the performance indicators are variable. As some >80% is green sometimes >70% is green.
Client needed to store these thresholds values in an independent table perhaps accomplishing 2 things:

1. Access cube via excel and use these values
2. Change in thresholds did not effect PPS KPIs.

As of yet I have not found a way to configure thresholds to be pulled from other source. There is an option in "Band by Stated score (advanced)" but it still uses a single value.

So following is the methodology to overcome this limitation:

1. First I created 2 simple tables in my data mart. One table for source systems. Other for target values for Green and Yellow along with reference to source systems table.
2. In my cube I used these 2 tables in an unrelated measure group. Source systems table being used as a dimension. Target value table used a fact table with a measure group.

These were all the backend changes required.
Now we move to PerformancePoint server's Dashboard Designer.

For each KPI in the Actual row use the following CASE expression:

CASE

when

[Measures].[My Value] >= ([Measure].[Green - Fact Target Values], [Dim Source].&[System XYZ])

Then 1

when

[Measures].[My Value] >= ([Measure].[Yellow - Fact Target Values], [Dim Source].&[System XYZ])

then 0

when

[Measures].[My Value] < ([Measure].[Yellow - Fact Target Values], [Dim Source].&[System XYZ])

then -1 END

WHERE
[Measure].[My Value] = Factual value upon which all measure are based.
[Measure].[Green - Fact Target Value] = Threshold value for Green indicator to be shown like 90 etc.
[Measure].[Yellow - Fact Target Value] = Threshold value for Green indicator to be shown like 80 etc.
([Measure].[Yellow - Fact Target Value], [Dim Source].&[System XYZ]) = Returns values for a particular system since I had multiple source systems.

In the Target value use [Measure].[My Value] in 'Data Mappings' and 'Source Data' in 'Calculations' to show values on dashboard.

For Thresholds use 'Increasing is Better' scoring pattern and 'Band by numeric value of Actual' banding method. Lastly for indicator values set 1 for Green, 0 for Yellow and -1 for Red.

That is all you need and no more adjusting Thresholds in Dashboard designer. Just modify values in the database table and you would be fine. Oh and yes you would be required to process your cube every time threshold values change.

Environment: SQL Server 2005, Performance Point Server 2007 SP2

Alternate Approach
Use SQL Server Analysis Services KPI do accomplish the same since it provides much simpler and easier way to use external goal values.

External Links:

1. http://blogs.msdn.com/performancepoint/archive/2008/01/09/band-by-stated-score.aspx
2. MS Press Microsoft SQL Server 2005 Analysis Services Step By Step Chapter 8 page 213