Ranked comparison reporting in OBIEE

Andrew Desalme-Oracle

Hi all - I'm facing a challenge building a particular style of report in OBIEE 12c, and hoping to get some insight into whether this can be done.

The customer requirement is to compare two lists of ranked items side-by-side, and calculate variances across them. The items in each list are not the same, so the rank is what is being pivoted on.  For example:

We effectively need to take the dataset on the left, and display it as the table on the right, with the Variance being a calculated value (such as a Pivot table calculated column). Can it be done in Answers?

Gianni Ceresa

If you remove the "Period" columns which are useless you can with a table view.

Sales TY, Rank TY, Sales LY, Rank LY and Variance will be measure columns. You don't need a pivot table but a simple table.

You use AGO or FILTER to get the last year values and use those values in the calculation of both the RANK and Variance.

PS: if you really want the useless Period columns you just hardcode the values as the logic to get the current and last year values will be in filters and formulas.

Andrew Desalme-Oracle

I don't believe AGO will help this case though?  There is no LY data for the TY items (and vice versa), I need to compare the sales of whichever items were in each ranked position during their selling period.  If TY and LY are simple measures, they would both operate on the same item on each row, unless there's a trick to get the functions to be on Item 1's row but independently return data for Item 11?

Jerry Casey

Another approach is to do a simple union query that joins on the rank column.

Query 1 filters in Item# 1-10, with columns for Rank, Sales, and a NULL column.
Query 2 filters on Items 11-20, with columns for Rank, a Null column, and Sales.

Variance is calculated at the header level using saw_x calculations, like (max(saw_1 by saw_0)-max(saw_2 by saw_0))/max(saw_2 by saw_0).
A pivot table pivoted on Rank puts them into single records for each rank.

Andrew Desalme-Oracle

Unfortunately, pivot tables don't appear to work if a RANK() is the defining attribute, it recalculates the rank on the fly using whatever else is in the pivot table with it (in this case, there's no other attributes shown if i want it to group by rank).

Even if a simple table displays the two result sets from the UNION operation, the pivot table doesn't adhere to that separation of data.

Pivot collapsing the rank to a single row:

Robert Angel

I the business will not agree but what the table shows on the right is spurious.

Anyone with no prior knowledge would look at it and read that item 10 was ranked 10th this year (true) and ranked tenth last year (false - from your table on the left)

I am guessing your client does this in excel yes?

If you dropped the spurious item detail in the table on the right and just made it rankings then the suggested union based method would work as suggested by @Jerry Casey, and the message that the table gives would be entirely consistent.

Alternatively, keep the item, drop the spurious second rank and have instead 'rank LY' and you can then use the standard AGO functions to achieve this as suggested by @Gianni Ceresa, and again the message of the table is entirely consistent.

The only other way you could achieve this would be pre-processing in the ETL, or by creation of a opaque view or similar, as the logic as you say is "join by rank".

Let's not try to replicate excel, you can staple a hamster to a pigeon in O level biology, that does not mean such monstrosities should live...

(Mr Angel does not condone violence to dumb animals with the possible exception of end users who persist in foolish behavior in excel)

Jerry Casey

I may give the data gurus ulcers , but this:

The two external columns are both marked as attributes.  I used EVALUATE so the results would sort correctly.

Produces this (Using my numbers, not yours): 

"var" is a calculated item on the Period column.  Used conditional data format to get the decimals. (when Period is null...)

Pivot Table looks like this:

Andrew Desalme-Oracle

Thank you Jerry (and everyone), that is a nice trick with EVALUATE.  I think that would solve the problem I had with RANK.  As Robert said though, this is also a case of replicating an Excel sheet in OBIEE, which can be a painful/pointless exercise, so I will have to expand on these points and see how it goes.