SQL Server 2008
Although the way of defining the KPI status and trend is very flexible it is also somehow circumstantial if you simply want to rate a KPI by its target value. In this case you would have to calculate the status of the KPI as being between –1 (worse) and +1 (best).
In many cases we find simple case statements here:
when KPIVALUE("TestKPI") >= 0.95 * KPIGOAL("TestKPI") then 1
when KPIVALUE("TestKPI") < 0.7 * KPIGOAL("TestKPI") then -1
This results in the KPI status being –1, 0 or +1. But if you want to blend smoothly between these values you have to use a formula that is a little bit more complex. Especially if you need to this computation for many KPIs it’s not really nice.
I had some examples for AS stored procedures (sprocs) in this blog before. So here is a very simple one to calculate a linear approximation between the boundaries:
public static double KPIStatusBanding(double bound1, double bound2, double actual)
if (bound1 < bound2) factor = 1;
else if (bound1 > bound2) factor = -1;
else return 0;
if (actual < bound1) return -factor;
else if (actual > bound2) return factor;
else return factor*2 * (actual – bound1) / (bound2 – bound1) – 1;
The parameters are as follows:
If bound1 < bound2 bigger values are better, if bound1 > bound2, lower values are better. With this function the calculation is quite easy. First let’s test the function itself with simple MDX queries:
member test as ASStatistics!KPIStatusBanding(100,200,175)
select test on 0
from [Adventure Works]
This results in a status value of 0.5.
In order to use the same boundaries as in my first example, I would use this KPI status expression:
0.7 * KPIGOAL("TestKPI"),
0.95 * KPIGOAL("TestKPI"),
As we can see from the KPI browser the values now gets approximated smoothly: