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 11.1.1.7.140527).

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

 Calculations:

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:

case 

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