The Analysis Services KPI Framework is quite powerful. Each KPI consists of 4 major attributes that can be solved through a corresponding MDX expression:
Value – the value of the KPI
Goal – the goal of the KPI, what you like the value to be/become
Status – indicates if the KPI is bad (-1), neutral (0) or good (1)
Trend – indicates how the KPI is doing over time
The fact that Status resolves to a value between -1 and 1 serves a pretty nice purpose. The system and possible client applications can interpret the meaning of the KPI: i.e. is it good or is it bad.
However, it can also complicate things. Especially when you’re not that good at math, finding a correct formula for the Status expression can proof to be quite challenging. Let’s say you have a measure [Invoice Aging] that contains the amount of days between an Invoice Date and its Closed Date (a late arriving fact when the invoice is fully paid for). Your CFO wants to know when there are invoices that age longer than 30 days. He states that if an invoice is paid before the 30th day, than that is a good thing. On the other hand, if an invoice ages 60 days or longer, than that is bad. Ideally an invoice is closed the same day that it was created (zero days).
If you would translate this into a mapping for 0=1 (good), 30=0 (neutral) and 60=-1 (bad) you’d get this:
Now, needless to say wrapping this table hard-coded into one giant MDX expression is not what you’ll want to do ;-) Good old math to the rescue!
As you can see in the table above, the status is pretty linear in accordance to its value. It just needs to be normalized from its value range (0…60) into the KPI’s status range (-1…1). Following expression does just that:
(60 - [Invoice Aging]) * 2 / (60 - 0) - 1
And that’s about it. But lets get into the semantics of this formula before ending this post. The number 60 occurs in it twice. That number defines the top end of the value range in the above example. The zero is there for a reason also… Yes, in this example it is obsolete but it also defines the bottom end of the value range. And that knowledge comes in handy because our CFO just changed his mind about the good and the bad of our nifty little KPI (off the record: CFO’s have this nasty tendency to do that allot, as do any CXO’s). He now states that indeed 60 or more days is a bad thing. But if an invoice is paid on the 30th day then that is still a good thing. So our value range changes from (0…60) to (30…60). Changing the formula to incorporate this change is pretty easy:
(60 - [Invoice Aging]) * 2 / (60 - 30) - 1
You might also have noticed that this formula resolves the lowest value into the highest status (because low invoice aging is a good thing). In most cases however, like a Sales figure for example, the highest value has to resolve into the highest status. Say, that when you sell x cars or less, then that is to be considered as a bad thing. And if you sell y cars or more, then that is good. Then this would be the formula:
(y - [Cars Sold]) * -2 / (y - x) + 1
As you can see, the 2 has changed into -2. This is because in our previous example it actually defined the target range (of the status value). The invoice aging had to resolve to ((1) – (-1)) which equals 2. But now the amount of cars sold has to resolve to ((-1) – (1)) which equals -2.
Now, the thing about the substraction (reversed status) and the addition of 1 (normal status) is something I cannot really explain. I know they need to be there in order to get the correct results but I’m a bit puzzled about their actual meaning. As I am not a mathematician I can only guess and so far I haven’t guessed anything worth mentioning yet. Feel free to comment if you know a decent explanation though ;-)
In conclusion of this post I’d like to state that things like this can always be calculated by SSAS and in the end you will be beter of following a mathematical approach instead of going the oh-so-messy Iif() way…