Skip to Content
Author's profile photo Krishna Tangudu

SAP HANA: Dynamic Ranking using Script based (SQL Script) Vs Graphical Calculation view Vs Script based (CE Functions)

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 🙂

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla

      Good one Krishna,

      For the graphical one, you should have pasted separate screenshot of each.

      Author's profile photo Krishna Tangudu
      Krishna Tangudu
      Blog 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

      Author's profile photo Former Member
      Former Member

      Hi Krishna,

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

      Regards,

      S

      Author's profile photo Former Member
      Former Member

      Nice one Krishna.

      Author's profile photo Krishna Tangudu
      Krishna Tangudu
      Blog Post Author

      Thank you so much Latha

      Author's profile photo Former Member
      Former Member

      Nice Blog krishna ,

      Thanks for the efforts..

      What happen to your old blogs , I can't see those.. if possible try to migrate those ..

      Regards,

      Mahesh

      Author's profile photo Krishna Tangudu
      Krishna Tangudu
      Blog 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

      Author's profile photo Henrique Pinto
      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?

      Author's profile photo Krishna Tangudu
      Krishna Tangudu
      Blog Post Author

      Yes Henrique.

      Will definitely check that out and see which one of the options gives the best time.

      Regards,

      Krishna Tangudu

      Author's profile photo Former Member
      Former Member

      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