Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

How to dynamically calculate TopN

Posted by sranka on November 25, 2009

Hi All

Finally I am sitting down to post an interesting thing I came across during my recent release. I would like to than Gopal Kethineni for this.

The requirement was very interesting and important :

Business wanted to calculate Top 10 opportunities/$ amounts by different slices/dices.

In past we had accomplished the above requirement by pre calculating the values. But the permutations and combinations were many. E.g Top 10 Opportunities By Geographical Hierarchy or By Product Hierarchy or by Time Hierarchy. If we had to pre calculate all of the above permutations and combinations than we had to write too many ETL and eventual ETL load would have delayed.

To make solution more scalable and dynamic we used POWER of OBIEE by twisting giving TOPN function. Following is the example .

For Every Level 4 Of Geographical Hierarchy, show all the Top 10 Customers based on the opportunity amount. Along with this we need to show all the Level 5 of the Geographical Hierarchy.

In the above example the challenge was to select Level 4,Level 5 , Customer Name, and Opportunity amount as part of the criteria but to calculate Top 10 customer only Level 4 Of Geographical Hierarchy needed to be considered as Level 5 was needed to be considered as only an attribute. To solve the above complex problem.Following expression was used as part of formula column.

TOPN(SUM("Fact - Customer"."Opportunity Amount" by "Dim - Geo Hrchy"."Level 4", "Dim - Customer"."Customer Name"),10 by "Dim - Geo Hrchy"."Level 4") 

We have saved more than several hours of ETL development/QA and system resources time. If I can count correctly, I would say we must have saved more than $100K , by implementing this solution.

Hope This helps,
Sunil S Ranka


2 Responses to “How to dynamically calculate TopN”

  1. Christian said

    Nice. Ask for 10% of the savings 😉


  2. Neela said

    Hi ,

    I had the same scenario to calculate Top 3.

    The columns in the report are org name level 1 ,org name level 2,project number ,count of a transaction

    The org names are drill enabled and have a tree structure in them.hence dynamice drill is possible through repository configuration(hierarchy is present for org names).

    if i want rank to be based on project number say count of transaction no for a particular project
    I give

    TOPN(SUM(“- CM”.”# of Trx Number” by “Org”.”Org Level 1″),3 by “Org Hierarchy”.”Org Level 1″ )

    this throws an error

    Odbc driver returned an error (SQLExecDirectW).
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near : Syntax error [nQSError: 26012] . (HY000)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: