Resolving a KPI’s Range into its Status

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:

Value	Status	Value	Status	Value	Status
0	1	20	0.33	40	-0.33
1	0.97	21	0.3	41	-0.37
2	0.93	22	0.27	42	-0.4
3	0.9	23	0.23	43	-0.43
4	0.87	24	0.2	44	-0.47
5	0.83	25	0.17	45	-0.5
6	0.8	26	0.13	46	-0.53
7	0.77	27	0.1	47	-0.57
8	0.73	28	0.07	48	-0.6
9	0.7	29	0.03	49	-0.63
10	0.67	30	0	50	-0.67
11	0.63	31	-0.03	51	-0.7
12	0.6	32	-0.07	52	-0.73
13	0.57	33	-0.1	53	-0.77
14	0.53	34	-0.13	54	-0.8
15	0.5	35	-0.17	55	-0.83
16	0.47	36	-0.2	56	-0.87
17	0.43	37	-0.23	57	-0.9
18	0.4	38	-0.27	58	-0.93
19	0.37	39	-0.3	59	-0.97
				60	-1

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… 

Why you should base your Facts Data Sources on Views

In SQL Server Analysis Services 2005 you can use different methods to map your source data into your Analysis Services Database’s Data Source Views. You can map your data source model one-on-one onto your physical tables in your (dimensional) database or you can map it on database views. Needless to say, the latter provides an extra level of abstraction. Which is almost always a good thing, imo. You could also use named queries to map your data (the queries reside in your SSAS project) but then you’d lose the abstraction layer again: If the underlying data model changes you’d have to change the SSAS database too while if you base your data sources on views you’d just have to alter those instead.

Now, if you want to enable partition write-backs (which I think is a very interesting feature for forecasting and what-if analysis scenarios) you can only do that on measure groups containing measures of which the AggregateFunction is set to SUM (additive measures). So this can be a problem when your fact table contains both additive and semi-additive measures. As I said, you won’t be able to use write-backs on the semi-additive ones but if you’ve mapped your facts one-on-one you cannot enable them on the additive ones either since they will be in the same measure group (nope, you can’t create multiple measure groups on the same fact table in your data source views).  Using database views or named queries you can easily get around this restriction.

But the views still provide a greater level of abstraction than the named queries…