In this article, we are going to discuss about how to build rank function using graphical Calculation View in SAP HANA.
Before we go ahead and see how to build this, let’s look at details about RANK function.
- This function calculates the rank for a data set based on the partition and ordering clause.
- This will be very useful when we have to pick latest records from multiple records or top N or Bottom N records from the source set.
- RANK() OVER (PARTITION BY <Colum Name> ORDER BY <Colum Name> ASC/DESC)
SAP HANA Supports RANK function from initial version – if we are going to use this as part of SQL Script, however from SAP HANA SP9 revision we have this functionality as an additional node while creating graphical calculation view.
- The scenario here is, we have sales orders data, where in, for every change to existing sales order there will be a new record in the table.
- When we build report on this table, we have to pick the latest sales order i.e. which has changed recently based on the ordered time.
Data Set: Please execute the below SQL statements, if you would like to practice the same exercise in your system.
Create Column Table Statement:
CREATE COLUMN TABLE SAP_STUDENT.ORDERS_DATA_RANK (ORDER_NO INTEGER,
Insert Data Into Table:
INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1234,’C01′,’P012′,10,5,’2015-09-10 08:03:12′);
INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1235,’C02′,’P023′,100,10,’2015-09-10 14:45:36′);
INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1236,’C03′,’P067′,80,20,’2015-09-15 21:23:56′);
INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1234,’C01′,’P012′,30,5,’2015-09-10 11:03:12′);
INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1234,’C01′,’P012′,50,5,’2015-09-12 08:03:12′);
INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1236,’C03′,’P067′,120,20,’2015-09-17 11:23:12′);
- Once you run the above insert statements, please run the below select statement to make sure data is inserted in a proper way as shown below.
SELECT * FROM SAP_STUDENT.ORDERS_DATA_RANK;
RANK() using SQL Script:
- Based on our scenario, we have to partition our data set by ORDER_NO and then arrange it in descending order based on ORDERED_TIME column to get top (first) rank to the recently changed sales orders.
- We can achieve this by writing the below SQL statement, which calculates the RANK based on PARTITION and ORDER BY clause and assigns the ranks as 1,2,3 and so on.
- Once we arrange data set as shown above with the help of RANK function, we can just filter the RANK value to ‘1’ to get recent records to the output.
RANK() using Graphical Calculation View:
- Now let’s see how we can achieve same thing using rank node available while creating graphical calculation view.
- Note: This node is available in graphical calculation view only from SAP HANA SP9 revision.
Please follow the below steps to build rank functionality using calculation view.
- Make sure we have the above table created and data present in SAP HANA system.
- Note: Individuals can also use their own data set to check the functionality.
- Let’s go ahead and create a new calculation view of type ‘graphical’ and specify details like technical name and label as shown below.
Please go though ‘Graphical Calculation View in SAP HANA’ for complete information on calculation view.
- We can see the ‘Rank’ node available on the left hand side of the screen. Click on that node and click again on design area to add rank node to our design area as shown below.
Proceed to the next page to continue reading…