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:
-
case
-
when KPIVALUE(„TestKPI“) >= 0.95 * KPIGOAL(„TestKPI“) then 1
-
when KPIVALUE(„TestKPI“) < 0.7 * KPIGOAL(„TestKPI“) then –1
-
else 0
-
end
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)
-
{
-
double factor=0;
-
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:
bound1 | worst value |
bound2 | best value |
actual | actual value |
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:
-
with
-
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:
-
ASStatistics!KPIStatusBanding(
-
0.7 * KPIGOAL(„TestKPI“),
-
0.95 * KPIGOAL(„TestKPI“),
-
KPIVALUE(„TestKPI“))
As we can see from the KPI browser the values now gets approximated smoothly:
Kommentare (0)