When to use Database Ranking in Web Intelligence Report

We will discuss what is Database Ranking and how and when we can use it in Web Intelligence Report. We will also compare its advantages over report level ranking.




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

0 comments:

Post a Comment