Rank and Dense Rank Functions:OBIEE

Yesterday, I was working on some issue and had to make use of Rank and Dense Rank in some reports in OBIEE. Here I am sharing the same with you as I think this might help some of you.

First of all lets understand what is Rank and Dense Rank. Let me explain the same with an example.

If in a class there are 5 students and in an exam 2 got 95 and other 2 got 93 and the last one got 91. So, using the Rank function will give you the below output:

Students     Marks     Rank

student 1      95             1

student 2      95             1

student 3      93             3

 student 4     93             3

student 5      91             5

And if we make use of the Dense Rank Function it will give the below output:

Students     Marks     Rank

student 1      95             1

student 2      95             1

student 3      93             2

 student 4     93             2

student 5      91             3

Hence, Dense Rank doesn’t skip the values and provides a continuous series of numbers as compared to Rank.

Now, lets see how can you write a query for the same on the DB.

Rank in DB

SELECT Student, Marks, Rank() over (PARTITION BY Marks order by student) as Rank  from Class

 

Dense Rank in DB

SELECT Student, Marks, Dense_Rank() over (PARTITION BY Marks order by student) as Dense_Rank  from Class

Now, lets understand how to implement the same in OBIEE.

Rank in OBIEE

OBIEE has an internal function called Rank and can be used directly for this. We can use the below syntax in the fx of the column:

RANK(“class”.”student” by “class”.”marks”)

  

Dense Rank in OBIEE

Since, Dense Rank is not an internal function of OBIEE we need to make use of the Evaluate function to use this. As explained in the previous posts also that Evaluate function is used when we have to make use of the DB functions in OBIEE. We can use the below syntax in the fx of the column:

EVALUATE(‘DENSE_RANK() OVER (PARTITION BY %2 ORDER BY %1)’AS INTEGER,”class”.”student”,”class”.”marks”)

The above two syntax show that how we can make use of Rank and Dense Rank functions in OBIEE.

Now, one important thing to note here is that we can make use of Rank as the filter in a report but we can’t use Dense Rank as the filter in a report. This is because Rank is an internal function to OBIEE and hence, when we make use of Rank as the filter in a report, OBIEE forms a subquery to calculate the Rank and filters the data in the outer query.

eg: RANK(class.student by class.marks) <=3

But, the same doesnt happen with Dense Rank. For Dense Rank OBIEE doesnt perpare and subquery and tries to use the entire Dense Rank expression in the where clause in the query which Oracle doesnt permits. It gives an ORA error stating that ‘Window Functions are not permitted’

I hope this will help you to understand the Rank and Dense Rank functions and their usage in OBIEE.