Rank node in SAP HANA Calculation view

Welcome to the next tutorial of this SAP HANA Tutorial series. In this one, we learn how to rank rows and pick up the highest or lowest ranks according to a condition using the Rank function in calculation views.

Let’s get into the requirement.

In a company XYZ, employees in the finance department with ID numbers 1001,1002 and 1003 have to undergo an evaluation to check which one of them is better at their job. They undergo tests from January to May on the first day of every month. Thus, each employee goes through five such evaluations. The top score they get during any of these months would be considered as their final score at the end of these evaluations.

Our requirement is to create a view of this data that displays only the top score of each employee along with the date on which the employee took the evaluation.

A new table was created below containing the overall evaluation data – EMP_SCORE_FINANCE under our 0TEACHMEHANA schema. The data has been fed into this table for each employee and their respective scores each month.

Rank function in sap hana calculation view

1

To achieve this, we would need to build a calculation view which ranks these rows of data and picks up the highest score for each employee. Fortunately, in SAP HANA graphical views, we have a rank operator available.

Let’s start by creating a new calculation view.

Rank function in sap hana calculation view

Give it a name and description and press “Finish”.

Rank function in sap hana calculation view

A blank canvas …

Let’s first build a simple view without the rank. Just a small view which displays all data from the table.

Rank function in sap hana calculation view

We add a projection with the table and connect it to the aggregation. Select all fields in the projection.

Rank function in sap hana calculation view

Now select all fields in the aggregation. When done, save and activate this view.

Rank function in sap hana calculation view

Run a data preview and you would get the below data which is of course the same as the table since we haven’t applied any additional logic anywhere.

Rank function in sap hana calculation view

SAP HANA Calculation View with Rank Node

Now, let’s get to business and rank this data as per our requirement. Drag the rank button on to the blue arrow joining the Projection_1 and the Aggregation block as shown below.

Rank function in sap hana calculation view

Press “Yes” on the message asking for confirmation.

Rank function in sap hana calculation view

You would now see a rank block inserted in between the projection and aggregation. The placement of these blocks looks messed up though. Fortunately, your HANA view doesn’t need to look like your room. There’s an auto arrange button marked below which cleans up the layout and arranges it in an optimal way.

Rank function in sap hana calculation view

1

After pressing this button, you would see that the blocks have been properly arranged as shown below. Once done appreciating this feature, click on the Rank node. On the right pane, first select the fields you need to take to the next level and then come to the bottom section marked in the red area below. Here the rank configurations would be maintained.

Rank function in sap hana calculation view

The first setting is the sort direction. Here you specify whether you wish to sort it with the highest value first or with the lowest value by choosing Descending or Ascending respectively. Since we need to pick up the maximum score, we keep this at Descending.

Rank function in sap hana calculation view

Next, we set the “Order By” field. This is the field we need to sort Descending (as per our previous setting). In our case, this field is SCORE. We need to sort SCORE descending to find out the top score.

Rank function in sap hana calculation view

Next, we need to set the “Partition By” column. This is the field we wish to sort by. Thus, we need to sort SCORE descending by each EMP_ID and then the first row for each such employee ID would be his/her top score.

Rank function in sap hana calculation view

Once done. Save and activate the view. Now, check the data preview to confirm the results. As seen below, the view has ranked and picked up only the top score of each employee and also the date on which they achieved this feat. Congratulations employee 1003!

Rank function in sap hana calculation view

Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.

If you feel the website helped you, please also contribute any small amount by using the “Donate button” on the right side of this page to help with the operational costs of this website and other systems involved.

<<Previous Tutorial                                                                                                              Next Tutorial>>

 

Tagged , , .

19 Comments

    • Sorry for the late reply. It’s been a busy few weeks.
      Threshold value is not mandatory and you shouldn’t be getting an error due to that.
      I would need more information to solve this problem. Please mail me all your steps and screenshots to shyam.uthaman@teachmehana.com.
      Probably, you would have solved the issue by now. If that is the case, please leave the solution in the comments below. It might help someone someday.
      Thanks.

  1. HI Shyam,

    How do we execute a scenario top 3 emp id based on scores for each date available .
    SO if we have 3 dates and 10 emp total we have 30 scores. i want top 3 for each date. Is it possible ?

  2. Hi Shyam,

    Thanks for the nice article. I followed the same and i got all 0(zero) in score column of Data Preview. Then i changed in semantics of score Aggregation to “Max” and it works. Is this missing in the above tutorial or i did go in the wrong direction? Please confirm.

    Thanks
    Anand

  3. Great Site!! Just a question regarding the Rank node. Once I try to activate this calculation view it fails due to no measure defined in reporting enabled view. If i do define a measure that will aggregate the value and we don’t get the desired result. Can you kindly guide me where I’m missing a point.
    Thanks in advance.

  4. Hi Shyam ,

    I am unable to see any data in my projection,rank or semantics even after activating it . However , data is present in my table .
    Can you please help ?

    Thanks,
    Avinash Singh

  5. Hi shyam ,

    If i don’t give the Threshold then the application is throwing an error “incomplete window function in rank node”

  6. Hi Shyam,

    I have seen your tutorials on http://teachmehana.com/. They are awesome. Thank you for sharing with everyone.

    I need your help Shyam. I want one scenario for calculation view and one scenario for stored procedures from an interview perspective. I know the theory of creating calculation view and stored procedure. But coming to project implementation, i would like to know the real time scenario in order to crack the interview process. Could you please guide and help me. I will be waiting for your reply. Thank you.

    Regards
    S

Leave a Reply

Your email address will not be published. Required fields are marked *