Skip to Content

SAP HANA: Implementing RANK using Graphical calculation View

Hi Folks,

As mentioned in this blog:

SAP HANA: Dynamic Ranking using Script based (SQL Script) Vs Graphical Calculation view Vs Script based (CE Functions)

As requested in the above blog, collaborating with this document on the steps i have used to create the graphical view . Please do suggest your opinions to make it better.

Will explain the each node here on how we can build the graphical calculation view and get rank in it.

Screen Shot 2014-07-04 at 4.49.19 PM.png

Projection 1 & 2 : We select the required columns i.e Employee Name,Region and Salary and create a “dummy” column with constant value 1 as shown below:

Screen Shot 2014-07-04 at 4.50.50 PM.png

Join 1: ( Projection 1 & 2 joined on dummy column )

Screen Shot 2014-07-04 at 4.51.57 PM.png

Aggregation 2 : ( Aggregate salary as “Minumum” and add “dummy” column to apply the group by )

Screen Shot 2014-07-04 at 4.53.22 PM.png

Join 2 : ( Joining Aggregation 2 and Projection 1 on dummy column )

Screen Shot 2014-07-04 at 4.53.47 PM.png

Projection 3 : ( Apply filter SALARY_1 < SALARY )

Screen Shot 2014-07-04 at 4.54.54 PM.png

Aggregation 1: ( Add EMPLOYEE_NAME_1 as “Aggregated column” and apply Count on it  at EMPLOYEE NAME,REGION,SALARY level)

Screen Shot 2014-07-04 at 4.57.22 PM.png

Projection 4: ( Add a calculated column Rank with formula RANK1 + 1  — To get the ranks from 2)

Screen Shot 2014-07-04 at 4.58.54 PM.png

Projection 6: ( Apply filter SALARY = MINUMUM and add it to Rank = 1 as calculated column )

Screen Shot 2014-07-04 at 5.00.42 PM.png

Projection 7 & 5 : Choose the required columns i.e EMPLOYEE_NAME,REGION,SALARY and RANK

Screen Shot 2014-07-04 at 5.02.26 PM.png

UNION_1 ( Map both Projection 7 & 5 in the union )

Screen Shot 2014-07-04 at 5.03.12 PM.png

Now you can see in the data preview we can see the RANK column showing us the desired output.

Screen Shot 2014-07-04 at 5.04.58 PM.png

Hope this navigations is clear.


Krishna Tangudu

You must be Logged on to comment or reply to a post.
    • Hi Sreehari,

      Yep will have to check the performance here. Once I load considerable amount of data then will check and post the results here


      Krishna Tangudu

  • This looks like a complicated solution. If the goal is just to get the rank as output, I would suggest using window functions in SQL. You could embed this in a graphical calc view just the same and achieve the same result with 2 lines of code.



    Just satisfied a much more complex ranking requirement and landed on using this technique, much simpler and clear.

    If you need to pre-aggregate and then rank, you just perform a subquery and wrap this partition function around that. This would be the case if you had an employee across two regions and you wanted to rank wholistically.



  • Hi Krishna,

    Any update on the performance for this approach?

    We have the same scenario and thinking if the graphical version will be faster over the SQL one.


    • In this case I would expect SQL to be faster because it creates a single SQL execution plan in the column store.

      Some empirical evidence either way would be interesting.

  • Hi John,

    Even I had the same thought, but I got this note from SAP:

    Window functions (RANK()) in HANA at the moment are not nice, since they have to be calculated in the row engine and are not natively supported in the column store. Try to rebuild the ranking with features which are running in column store.


    Please share some test results on these scenarios if you have.



  • Hi,

    Nice solution. Just small rectification in Aggregation 2 section,  Aggregation Type should be MAX for MINIMUM column. Otherwise, you will not get data for RANK 1 and you will skip it.