Tuesday, July 8, 2014

OBIEE - Comparing performance to peers

I was asked about an analysis where I compare the list of top 10 products of a company ( or sales person or anything else) with the top 10 off all the others. The idea is to see the top products of peers in compare to me.
I will describe here the solution I was asked about and in the next post some of my ideas how this could be done (better?).
(Done on OBIEE

Option 1

I'll use Sample Sales and compare Rank of Revenue by all companies with Rank of Revenue of specific company.
The Analysis:
The basic columns: Company, Product, Revenue

Sum(Revenue by Product) = the total Revenue for each Product
Rank (Sum(Revenue by Product)) = The Ranking of each Product Revenue for the entire data
Rank(Revenue by Company) = The Ranking of each Product Revenue for each Company

Now I was requested to mark the cases where Rank(Revenue by Company) is not in top 10 while it is in top 10 in Rank (Sum(Revenue by Product)). This way I can see the products I can improve with.

So I created a column with the following cumbersome case statement:
when rank(sum(Revenue by Product))<11 and rank(Revenue by Company)<11 THEN 'OK'  when rank(sum(Revenue by Product))<11 and rank(Revenue by Company)>10   then 'XXX' Else '' end
And used Conditional format so this column will be red when it's value is XXX.

The result is (I left the Original Column Header on purpose):

 We can add filter to the Analysis, to see only top 10 rank(sum(Revenue by Product)).

The result will be OK:

Now, I was told, it's very nice but we want to filter the company with dashboard prompt, so each Company will get it's own results by default.
THIS is a problem.
If we try a regular filter, we will loose the data of the other companies, and see only the selected one. 

What can we do?
Use selection steps!
Why? Because Selection steps are executed only after the general Query. 

Select the Company in List:

Edit "Stat with all members" 

Change the Action to "Start with all members" and mark "Override with prompt".

Now when we call the analysis from a dashboard, it is all working fine:

Option 2

Noy Dekel from Vision.BI remarked that this could be achieved with the Filter Function an presentation variable, as well:

This time we don't have the Company column:
We have only:
  • Product 
  • Revenue (we don't need the sum(revenue by Product since there is no company column)
  • FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) 
  • RANK(Revenue)
  • Rank(FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) )
  • The relevant case statement
Since the filter formula is Presentation Variable based, we can change it using a Prompt.

There is one small difference between the results: the second option always forces us to select by default a specific company, while the first allows us to see all values by default.

You are welcome to continue to OBIEE - Comparing performance to peers - next step

No comments:

Post a Comment