When to use Database Ranking in Web Intelligence Report
While working with one user requirement on applying Ranking at report level, I was not very happy with the performance and complex approach of placing Ranking. Performance was slow due to the huge volume of data and processing of ranking on this huge data by web intelligence report server. For ranking on report, WebI server gets the unranked data from database and does sorting and filtering on it to return ranked data.
I tried to use Database Ranking and was amazed with the performance
as it ranked data at the database level and the data returned to Web
Intelligence by the query was already ranked.
Following this, I can point the advantages as:
- By ranking at the database level we allow the DB server, which is typically far more powerful than the client machine, to perform this processing.
- Pre-ranking data reduces the amount of data retrieved in Web Intelligence.
- Since we get processed data, using this data minimizes the complexity when used in Xclecius or similar tools.
How does it work?
A database ranking works by modifying the SQL that Web
Intelligence generates to retrieve the query data. If your database supports
ranking, Web Intelligence generates SQL to rank the data. Web Intelligence uses
the SQL-99 Rank function in ranking SQL.
You can perform a database ranking only if your database
supports it. If this is not the case, the Add a database ranking button is
disabled on the Query Panel toolbar. Databases that support ranking are Oracle, DB2, Terradata and Redbrick.
Example: I am using a simplified case to compare difference between
using ranking at report level and using database ranking.
Requirement: Return Top 10 users based on number of actions.
To achieve it using report level
ranking, I pulled the objects in web intelligence and executed the query. With
output, I added the ranking functionality provided at report level and ranked
the data for top 10 users.
SQL:
SELECT
AUDIT_EVENT.User_Name,
count(AUDIT_EVENT.Event_ID)
FROM
AUDIT_EVENT
GROUP BY
AUDIT_EVENT.User_Name
|
Query output: 4369 rows
Query Time: 225 sec
Adding Report level ranking- 60 sec (manual)
|
For database ranking, I
added the ranking from the toolbar at the top of the Query Filters pane. I selected
“Top” as direction. In the dimension, I dropped User Name and “Number of
Actions” as based on. After
executing the query, I had the output ready ranked.
SQL:
SELECT
AUDIT_EVENT.User_Name,
count(AUDIT_EVENT.Event_ID)
FROM
AUDIT_EVENT
WHERE
AUDIT_EVENT.User_Name IN
(
SELECT
View__1.Column__1
FROM
(
SELECT
AUDIT_EVENT.User_Name AS Column__1,
RANK() OVER( ORDER BY
count(AUDIT_EVENT.Event_ID) DESC ) AS
Rk__1
FROM
AUDIT_EVENT
GROUP BY
AUDIT_EVENT.User_Name
)
View__1
WHERE
View__1.Rk__1 <= 10
)
GROUP BY
AUDIT_EVENT.User_Name
|
Query output: 10 rows
Query Time: 195 sec
No manual input.
|
Clearly there is a difference in overall process even if we
worked on a small set of data. Database ranking leverages database server
processing and a much easier method to achieve ranking.
I would suggest to use this feature on case to case basis.
If your database supports ranking and your reporting requirement is to get
ranked data, you can utilize Database Ranking. It would certainly be a performance improvement measure. But, if ranked data is a sub-set
of your reporting output, it would be good to use report level ranking
functionality.
Looking for your use-case...
Thanks for such a great article here. I was searching for something like this for quite a long time and at last I’ve found it on your blog. It was definitely interesting for me to read about their market situation nowadays.Well written article Thank You for Sharing with Us pmp training centers in chennai| pmp training in velachery | project management courses in chennai |pmp training in chennai | pmp training institute in chennai |
ReplyDeleteThanks so much for all your videos shared in youtube. very much helpful. I was looking for videos on "what and how to resolve traps in Universe Designer". It would be of great help if you could post the link for the topic mentioned.
ReplyDeleteAmazing post very useful info thanks for the author to share a valuable post .
ReplyDeleteAws training chennai | AWS course in chennai