Skip to Content

Hi Folks,

In this blog, i will be explaining on how to achieve “Custom” Ranking using Graphical Calculation View.

Problem Description:

We have to show Top ‘2’ ( Depends on the input from customer) Employees by “Salary” wise for each “Region” .i.e Top 2 employees in each region who are earning more salaries 🙂

**Note: if any body has the same salary they will share the same rank and they get ranked on the order of highest salary.

Example: Let us take the same table (Employee table) i have used in my previous blogs

SAP HANA: Using “Dynamic Join” in Calculation View (Graphical)

Using Script basec Calculation View using SQL Script:

We can achieve this very easily using window functions using Script based Calculation View as shown below:

SELECT “REGION”,”EMPLOYEE NAME”,”SALARY” FROM

(SELECT “REGION”,”EMPLOYEE NAME”,”SALARY”,RANK() OVER (PARTITION BY “REGION” ORDER BY SALARY DESC) AS RANK

FROM EMPLOYEE)

WHERE RANK <= 2 ( we can use input parameter to make this dynamic)

Have achieved the same intended result using Graphical Calculation View as well.Please note that we cannot use “Rank” function in the graphical calculation view ( atleast in the current version it is not supported 🙂 )

Using Graphical Calculation View:

Please find the snapshot of the model created below:

Screen Shot 2014-01-09 at 6.46.30 PM.png

Have a look on this document for further details on creating it.

SAP HANA: Implementing RANK using Graphical calculation View

Output:

Capture.PNG

As seen above, based on the input you give ex: 2, you will get Top 2 Employees ranked based on their salaries.

Using Script based Calculation Views using CE Functions:

As it would be difficult to show all the nodes and their screenshots , i have recreated the same logic using “CE” functions as shown below:

/********* Begin Procedure Script ************/

BEGIN

t = CE_COLUMN_TABLE(KRISHNA.EMPLOYEE);

t1 = CE_PROJECTION(:t,[CE_CALC(‘1’,int) as dummy, “REGION”,”EMPLOYEE NAME”, “SALARY”]);

t2 = CE_PROJECTION(:t,[CE_CALC(‘1’,int) as dummy, “REGION”,”EMPLOYEE NAME” AS “EMPLOYEE NAME1”, “SALARY” as SALARY2]);

t3 = CE_JOIN(:t1, :t2, [dummy,REGION]);

t4 = CE_AGGREGATION(:t1,[sum(dummy) as “count”],[dummy,”REGION”]);

t5 = CE_PROJECTION(:t4,[“REGION”,”count”,dummy]);

t6 = CE_JOIN(:t5, :t3, [dummy,REGION],[“count”,dummy,”REGION”,”EMPLOYEE NAME”,”EMPLOYEE NAME1″,”SALARY”,”SALARY2″]);

t7 = CE_PROJECTION(:t6, [“REGION”,”EMPLOYEE NAME”,”EMPLOYEE NAME1″, “SALARY”, SALARY2,dummy,”count”],'”SALARY2″ <= “SALARY”‘);

t8 = CE_AGGREGATION(:t7,[count(“EMPLOYEE NAME”) as rank1],[“REGION”,”EMPLOYEE NAME”, “SALARY”,”count”]);

t9 = CE_PROJECTION(:t8, [“REGION”,”EMPLOYEE NAME”, “SALARY”, rank1,”count”,CE_CALC(‘”count”-“RANK1″+1’,int) as rank]);

var_out =  SELECT REGION,”EMPLOYEE NAME” AS “EMPLOYEE_NAME”,SALARY,rank as “rank” FROM :t9;

END /********* End Procedure Script ************/

Hence we saw the 3 ways in which we can get the ranking i.e Calc Script using SQL Vs Calc Graphical Vs Calc Script using CE functions.

Please do let me know your feedback on this 🙂 .

Your’s

Krishna Tangudu 🙂

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

    1. Krishna Tangudu Post author

      Thank you Raj 🙂 for your feedback.

      I left it because i felt it will make the blog very lengthy. Will definately add those details in the seperate blog and share it.

      Regards,

      Krishna Tangudu

      (0) 
      1. srinivas L

        Hi Krishna,

        Very good blog, Can you paste screenshots on how you have achieved using the graphical calculation view

        Regards,

        S

        (0) 
    1. Krishna Tangudu Post author

      Hi Mahesh,

      Thank you for your feedback.

      My other blogs are in my old account you can find them in the below link:

      Lalitha Swaroop Krishna Tangudu

      As i changed my employer i created a new account and It seems it is not possible to get my old account linked to the new one 🙁

      Regards,

      Krishna Tangudu

      (0) 
  1. Henrique Pinto

    It’d be good to have a performance comparison on the 3 modeling options.

    Could you populate the test table with some test data (100k+ records at least, 1M+ would be better) and run each model 3 times and take the average time?

    (0) 
  2. murugesan thirumal

    Hi Krishna,

            

    I am new to hana and by your blog i got more information in modelling.

    I have one doubt, how to perform group by operation in graphical calculation view.

    Regards

    Murugesan Thirumal

    (0) 

Leave a Reply