Rank and Dense Rank Functions:OBIEE
(7 votes, average: 5.00 out of 5)
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…
June 21, 2010 -- Merge and Hash Joins in Oracle (1)
July 12, 2012 -- OBIEE: Using SORT_ORDER_LOCALE with Union (2)
May 6, 2010 -- Calling Database function in OBIEE (11)
September 6, 2011 -- Refresh Prompts while navigating Dashboard Pages-1 (1)
February 7, 2012 -- OBIEE Changing Source DB dynamically (2)
hi please send the Obiee Real time interview Q?
Hi
I am planning to start writing that also. Need some time to build a bank of questions.
Pleasse send the real time scenarios of Alias table
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
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
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.
Dense rank in obiee:
MAX(RANK(xx by yy)) – RANK(xx by yy) + 1
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!
Recent Posts
Categories
Archives
Tags
Most Commented
Most Viewed