SAP HANA – Rank Function in Calculation View

Hello Everyone,

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.

RANK:

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

SQL Syntax:

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

 

Scenario:

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

                                                               CUST_NO NVARCHAR(10),

                                                               PROD_NO NVARCHAR(10),

                                                               QUANTITY INTEGER,

                                                               PRICE INTEGER,

                                                               ORDERED_TIME SECONDDATE)

 

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;

1

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.

2

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

Step1:

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

Step2:

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

3

Step3:

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

4

Proceed to the next page to continue reading…

Check Also

Capture

SAP HANA – Calculation View ( Creation Using GUI)

Hello Everyone, In this article, let’s look at Calculation View, which is one more modeling …

11 comments

  1. Exist a version problem with ranking node? because en build 85v not work.

  2. Hello,

    Im with SP09 rev97 and theres no Generate Rank Column option.
    Which rev is phis option available?

    Thx in advance.

  3. Hi,

    I create a SQL view with this query statement:
    “SELECT
    COUNT(colA) counts,
    MIN(created_at) created_at,
    MAX(order_no) max_no,
    MAX(last_updated_at) last_updated,
    colA,
    colB,
    (case when colC = 0 then ‘NO’ else ‘YES’ end) colC,
    FROM (
    SELECT
    colA,
    colB,
    colC,
    created_at,
    last_updated_at,
    ROW_NUMBER() OVER(PARTITION BY colA, colB, colC) AS order_no
    FROM table1
    )
    GROUP BY colA, colB, colC ”

    My problem is: how can I create a column view (Attribute, Analytic, Calculation View) based on this query?
    Please teach me how to do that.

    Thanks a lot

  4. Hi,

    I need to use distinct value for one column. Can I use Rank function to achieve. If yes, please let me know how.

    Thanks

    • Hi Bishant,

      To calculate distinct count you don’t need rank function, you can do this using ‘Counter’ in either Calculation View (Cube type) or Analytic View.

  5. Hi,

    My query is when we use select distinct statement for any more than three column. How can we achieve this graphically. By using counter we can get the distinct count but I need the distinct column value. I hope it makes somewhat clear my query.

    Thanks

    • Hi Bishant,

      Use ‘Aggregate Node’ and add the columns for which you need distinct values as Attributes to the output. Don’t add any measures into it. The output of the aggregate node should be same as SELECT DISTINCT COL1,COL2,COL3… FROM TABLE statement.

Leave a Reply

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

four + six =