Hi Folks,

As mentioned in this blog:

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

As requested in the above blog, collaborating with this document on the steps i have used to create the graphical view . Please do suggest your opinions to make it better.

Will explain the each node here on how we can build the graphical calculation view and get rank in it.

Screen Shot 2014-07-04 at 4.49.19 PM.png

Projection 1 & 2 : We select the required columns i.e Employee Name,Region and Salary and create a “dummy” column with constant value 1 as shown below:

Screen Shot 2014-07-04 at 4.50.50 PM.png

Join 1: ( Projection 1 & 2 joined on dummy column )

Screen Shot 2014-07-04 at 4.51.57 PM.png

Aggregation 2 : ( Aggregate salary as “Minumum” and add “dummy” column to apply the group by )

Screen Shot 2014-07-04 at 4.53.22 PM.png

Join 2 : ( Joining Aggregation 2 and Projection 1 on dummy column )

Screen Shot 2014-07-04 at 4.53.47 PM.png

Projection 3 : ( Apply filter SALARY_1 < SALARY )

Screen Shot 2014-07-04 at 4.54.54 PM.png

Aggregation 1: ( Add EMPLOYEE_NAME_1 as “Aggregated column” and apply Count on it  at EMPLOYEE NAME,REGION,SALARY level)

Screen Shot 2014-07-04 at 4.57.22 PM.png

Projection 4: ( Add a calculated column Rank with formula RANK1 + 1  — To get the ranks from 2)

Screen Shot 2014-07-04 at 4.58.54 PM.png

Projection 6: ( Apply filter SALARY = MINUMUM and add it to Rank = 1 as calculated column )

Screen Shot 2014-07-04 at 5.00.42 PM.png

Projection 7 & 5 : Choose the required columns i.e EMPLOYEE_NAME,REGION,SALARY and RANK

Screen Shot 2014-07-04 at 5.02.26 PM.png

UNION_1 ( Map both Projection 7 & 5 in the union )

Screen Shot 2014-07-04 at 5.03.12 PM.png

Now you can see in the data preview we can see the RANK column showing us the desired output.

Screen Shot 2014-07-04 at 5.04.58 PM.png

Hope this navigations is clear.

Your’s

Krishna Tangudu

To report this post you need to login first.

13 Comments

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

    1. Krishna Tangudu Post author

      Hi Sreehari,

      Yep will have to check the performance here. Once I load considerable amount of data then will check and post the results here

      Regards,

      Krishna Tangudu

      (0) 
  1. Justin Molenaur

    This looks like a complicated solution. If the goal is just to get the rank as output, I would suggest using window functions in SQL. You could embed this in a graphical calc view just the same and achieve the same result with 2 lines of code.

    SELECT “EMPLOYEE_NAME”, “REGION”, ROW_NUMBER() OVER (PARTITION BY “EMPLOYEE_NAME” ORDER BY “SALARY”) AS “RANK”, “SALARY”

    FROM EMPLOYEE

    Just satisfied a much more complex ranking requirement and landed on using this technique, much simpler and clear.

    If you need to pre-aggregate and then rank, you just perform a subquery and wrap this partition function around that. This would be the case if you had an employee across two regions and you wanted to rank wholistically.

    Regards,

    Justin

    (0) 
    1. Krishna Tangudu Post author

      Yes Justin, Completely agree with you. I mentioned the same in the previous blog as well

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

      As people were asking for detailed screens on the graphical, so updated in this blog.

      As window functions work on row , was thinking of multiple ways to do the same and see if we can get any performance benefit if we are using graphical.

      I will have to still validate with more volumes of data. But my gut feeling says that I eventually have to agree with you.

      Regards,

      Krishna Tangudu

      (0) 
  2. Remya Raj

    Hi Krishna,

    Any update on the performance for this approach?

    We have the same scenario and thinking if the graphical version will be faster over the SQL one.

    Thanks,
    Remya

    (0) 
    1. John Appleby

      In this case I would expect SQL to be faster because it creates a single SQL execution plan in the column store.

      Some empirical evidence either way would be interesting.

      (0) 
  3. Remya Raj

    Hi John,

    Even I had the same thought, but I got this note from SAP:

    Window functions (RANK()) in HANA at the moment are not nice, since they have to be calculated in the row engine and are not natively supported in the column store. Try to rebuild the ranking with features which are running in column store.

    @Krishna,

    Please share some test results on these scenarios if you have.

    Thanks,

    Remya

    (0) 
  4. Vikram B. Divekar

    Hi,

    Nice solution. Just small rectification in Aggregation 2 section,  Aggregation Type should be MAX for MINIMUM column. Otherwise, you will not get data for RANK 1 and you will skip it.

    Regards,

    Vikram

    (0) 

Leave a Reply