Rank Node : Certainly a nice-to-have feature

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 !