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.

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.

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

A blank canvas …

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

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

Now select all fields in the aggregation. When done, save and activate this 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.

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.

Press “Yes” on the message asking for confirmation.

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.

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.

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.

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.

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.

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!

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>>