02.11.2009

Simple Banding function for KPI Status or KPI Trend

Technical Value

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:

  1. case      
  2. when KPIVALUE("TestKPI") >= 0.95 * KPIGOAL("TestKPI") then 1      
  3. when KPIVALUE("TestKPI") < 0.7 * KPIGOAL("TestKPI") then -1      
  4. else 0      
  5. 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:

  1. public static double KPIStatusBanding(double bound1, double bound2, double actual)      
  2. {      
  3.     double factor=0;
  4.     if (bound1 < bound2) factor = 1;      
  5.     else if (bound1 > bound2) factor = -1;      
  6.     else return 0;
  7.     if (actual < bound1) return -factor;      
  8.     else if (actual > bound2) return factor;      
  9.     else return factor*2 * (actual - bound1) / (bound2 - bound1) - 1;      
  10. }

The parameters are as follows:

bound1 worst value
bound2 best value
actual actual value

 

image

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:

  1. with      
  2. member test as ASStatistics!KPIStatusBanding(100,200,175)      
  3. select test on 0      
  4. 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:

  1. ASStatistics!KPIStatusBanding(      
  2. 0.7 * KPIGOAL("TestKPI"),      
  3. 0.95 * KPIGOAL("TestKPI"),      
  4. KPIVALUE("TestKPI"))

image

As we can see from the KPI browser the values now gets approximated smoothly:

image

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden