Hello,

We always wanted to use the rank functionality in the graphical calculation views. There exists few extraordinary blogs and documents in SCN demonstrating the work arounds for rank function. Now, needless to worry a new node called ‘Rank’ has been introduced (HANA Version 1.00.094) in the graphical calculation view.


In this document I shall explain the use-case using the Rank node.


Use case: Finding top N revenues by year across quarters

     – Created a graphical calc view having aggregation as the top node.

     – Included a rank node and input to the rank node is a calc view having the following data. The use-case here is to select top revenues by year.

    

    
     – The following screenshot explains the construction of the calc view

    

   

     – When the Rank node is clicked the properties for the node are displayed on the left

          Sort Direction –  Order of sort/top values. It can be either Descending/Ascending. In this scenario the descending order is selected

          Threshold       –  This is the value for N, i.e., Rank Number/Top Number. It can be given directly as a fixed integer value or can be passed as a input parameter.

                                       In this case an input parameter is created (IP_RANK) and passed to the threshold value getting input from user in the run-time

         Order By         –  The column on which the sort has to work. Here the column is ‘REVENUE’

          Partition By    – The column(s) based on which the sort has to be done. Basically these columns go to the group by clause. Here YEAR is selected.


     – The three columns YEAR, QUARTER, REVENUE are added as outputs to the semantics node from the aggregation node.

     – The view is then activated.The following is the screenshot of the data preview.

    

    

     – From the output it is seen that the top 2 revenues across each year is fetched

Please feel free to give your comments & suggestions.

Thank you !

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Jon Humphrey

    Hi Monissha,

    Given your model as a starting point.  How would I show the top 2 Years.  In other words how do you rank the column that has the highest level of granularity.

    Thanks,

    Jon

    (0) 
  2. Anindita Bhowmik

    Hi Monissha,

    A simple and well written article on the rank node. 🙂

    But, in my experience of working with the Rank Node in SP09 Revision 97, it seems to deteriorate the performance of the view drastically to a point where in to just rank 2 crore records from one table it takes beyond 30 secs of processing time. 🙁

    Any comments on optimizing the same? 😕

    It is a  calculation view with just the rank logic with a simple aggregation view as the base on the calculation view. 😐

    Regards,

    Anindita Bhowmik

    (0) 
  3. Ram C

    Hi Monissha,

    I tried to implement same logic. But Unable to use Input parameter  at Threshold level.

    Can u help me.

    (0) 
  4. Rajshekar shekar

    Hi Monissha,

    Thanks a lot for your article, helps me to understand clearly….

    I tried to pass the Value (THRESOLD) through INPUT PARAMETER instead of Fixed value..But i am not able to assign IP, to Thresold…can you help me out on this….

    Thank’s in advance!!!

    Raaj

    (0) 

Leave a Reply