## Rank and Dense Rank Functions:OBIEE

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…

saibabusaid: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.

Aparnasaid: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 Balajisaid: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.

Gergosaid:Dense rank in obiee:

MAX(RANK(xx by yy)) – RANK(xx by yy) + 1

Ramasaid: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.

MURUGANsaid:Hi Rohit,

The given syntax for rank in obiee doesnt work in obiee 11g.RANK(“class”.”student” by “class”.”marks”).

Pls help me on this.

Rgds,

Murugan

Rohit (author)said:Hi Murugan,

The same works for me. Please let me know what are you trying to achieve.

Regards,

Rohit

Prafullasaid:Hi,

Thanks a lot for the excellent explanation.

My question is little functional dependant than the techonology.

Would you please provide me the use case of Rank and dense rank , I mean which business scenario we can use rank and which dense rank is good.

Rohit (author)said:Hi Prafulla,

A business scnario would be when you want to display the top 10 values say Sales etc. In that case, if we calculate rank on the Sales column and the Sales are same for some regions, then, it will assign then the same rank to those Sales and would skip one rank. Eg: 1,2,2,4. In this case, you might want to display 10 distinct top Sales values or just top 10 Sales values. In first case, you will have to use Dense Rank whereas in second scenario you will have to use Rank.

Hope it helps.

-Rohit