Home » BI Answers, Database

Rank and Dense Rank Functions:OBIEE

28 October 2010 20,215 views 8 Comments
1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 5.00 out of 5)
Loading ... Loading ...

Hi Friends,

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.

Keep Posting…


8 Comments »

  • saibabu said:

    hi please send the Obiee Real time interview Q?

  • Rohit (author) said:

    Hi

    I am planning to start writing that also. Need some time to build a bank of questions.

  • Aparna said:

    Pleasse send the real time scenarios of Alias table

  • Rohit (author) said:

    Hi Aparna,

    The real time scenerios for Alias table is…
    Eg: Say you have a fact table Sales_fact and it has 2 date keys Start_dt and End_dt. Now, both need to join with the Time Dim. So, you can not create 2 joins between 2 tables, so in that case you need to make use of Alias tables.
    Create 2 alias tables of Time_Dim i.e. Start_date_dim and End_date_dim and join the Sales fact keys Start_Dt and End_Dt with the key column present in the alias tables like… start_dt with dt_id of Start_dt_dim and end_dt with Dt_id of End_dt_dim.

    Kindly let me know if you still have some questions….

    Keep posting :)

  • Anand Balaji said:

    Hi Rohit,

    Can you please give me your mail id..? i found your website very clear and understandable for freshers.thanks for posting your views.

    Thanks & Regards,
    Anand

  • Rohit (author) said:

    Hi Anand,

    Thanks alot… You can post any queries here and I will try to resolve them.. Also, I would like to get feedback from my readers on the topics which they want me to post here.

  • Gergo said:

    Dense rank in obiee:
    MAX(RANK(xx by yy)) – RANK(xx by yy) + 1

  • Rama said:

    Hi,

    It is very simple and nice way of presentation on each of the topic.. Thanks lot for your effort. I would like to start OBIEE . After looking your blog, i felt that it is very useful.

    Thanks,
    Rama.

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.