Rank function syntax in OBIEE
A question came up recently during an OBIEE 10g training course we were running which merits a note on the blog. As part of the training, a request was built that displayed the TopN most expensive projects within an organisation for a specified year. The question posed by a delegate was “how do I display the TopN most expensive projects across a range of years? For example, the top 3 most expensive projects on a year-by-year basis.
The output of the request should look something like this:
Year Project Cost Rank 2012 A 10,000 1 2012 B 9,000 2 2012 C 8,000 3 2011 B 7,500 1 2011 C 6,000 2 2011 D 5,000 3 2010 A 9,500 1 2010 B 8,500 2 2010 E 3,000 3
This type of request is simple to construct using SQL in the Oracle database, utilising the Rank funtion with a partition by clause for the year.
When we look at the Rank function in OBIEE, the tooltip describes the function as follows:
Syntax: RANK(expr)
Description: Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,…). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7…).
What the description does not tell us is the syntax for the partition by clause. In order to add the clause, the syntax is as follows:
RANK(measure by attribute) e.g. RANK(“Fact – Project Cost”.Cost by “GL Calendar”.”Fiscal Year”)
The results of the query then match the requirement with minimul fuss:
If we interrogate the request log and look at the physical SQL query, we can identify the database Rank function in use, with the partition by clause (excuse the aliases):
... Case when SAWITH0.c1 is not null then Rank() OVER ( PARTITION BY SAWITH0.c2 ORDER BY SAWITH0.c1 DESC NULLS LAST ) ...
Hope this is useful…
It was informative.