ABC-Analysis in OBIEE
When I am at customer site the most interesting questions come up. Sometimes really challenging ones. Lucky this time I just had to ask around in my highly skilled Trivadis-network for the one after "How to implement ABC-Analysis in OBIEE". This time it was Hendrik Becker, who came up with the answer and provided me the solution. Thanks Hendrik !
Generally ABC-Analysis is mostly used to categorize inventory into classes A,B and C, where class A marks the items having the most influence on the inventory costs. Class A typically contains the items which make up to typically 80% (or 20% of total items) of the total value, class B will have around 15% of total value and so on. For more detailed general information you can refer here. In the end you can use this method for mostly all quantitative measures, e.g. sales or bookings can be displayed this way. Traditionally I would say that ABC analysis is used most frequently in combination with products or customers. For the first to get an impression on which products are your most important ones, for the second to find out more about your key accounts, perhaps also giving insight in how dependent you are from your Top customers in terms of sales, bookings or whatever measure is analyzed.
But of course ABC analysis (as all analysis) is not limited, so feel free to use ABC analysis whereever you like to. In my opinion this analysis really has a vey good informative value and by the way explains itself visually for every user.
Enough general explanations, let’s get our hands on it.
So, what we need for this are 2 calculated measures beside the normal figures. One shows be the cumulated value of the measure we want to display, the other holds the percentage of this value from the total.
Let's take the sales demo and use sales as our measure. What I do is I create a rolling sum over sales rsum("- Sales".Sales). Additionally I calculate the percentage of my detailed sales cell from the total sales like this ("- Sales".Sales/sum("- Sales".Sales)) * 100. And of course I want to know how many items are in my class, so I need to count them. And also I create the category (you remember A, B, or C) which I will need later, perhaps like this:
CASE WHEN
round(rsum("- Sales".Sales/sum("- Sales".Sales)*100),1) <= 80
THEN
'A'
WHEN
round(rsum("- Sales".Sales/sum("- Sales".Sales)*100),1) between 80 and 95
THEN
'B'
ELSE
'C'
END.
After all the report looks like this :
or graphically like this :
Fine, I like that so far. But there is something important missing. You might know what I mean, don't you ? Exactly, add a dashboard prompt to make the limits flexible and change your column formulas accordingly.
I dare to claim that this dashboard really is a benefit for your user. Or not ? What do you say ?
So long,
Andreas