Greetings…

Premise:

Few weeks back, while perusing through some of the insightful information on optimizing SAP HANA modeling performance, the statement which caught my attention was:

‘Use table functions instead of scripted calculation views (SP09)’

This sounded like, the coveted scripted calculation views, which were a part & parcel of any real-time customer implementation were facing an extinction threat! To fathom what that means, warranted some investigation and here’s my attempt to get the basics in place w.r.t

User-defined Table-valued Functions (a.k.a TV-UDFs).

Test Case:

Scripted calculation view Vs Graphical calculation view (using TV-UDFs)

Dataset:

Sales fact table, SALES_F, with 1 million records (hoping that this volume will provide some basis for comparison).

/wp-content/uploads/2016/08/1_1006008.png

Please see references section for links to various documents used for assistance for this exploration.

Scenario:

Table used: SALES_F, BUSINESS_UNIT_D

Example Data:

SALES_F

/wp-content/uploads/2016/08/2_1006009.png

BUSINESS_UNIT_D

/wp-content/uploads/2016/08/3_1006016.png

Required Output:

Business Unit

Month

Total Sales

3 months Running Total Sales

Implementation:

  1. First, we will create a Calculation View of type – Scripted – and write the code to achieve the desired output.

    *Please pardon my sub-optimal ‘nested select’ methodology for the code. I am aware that this code can be made much more efficient but

     I deliberately wanted to check the performance with a sub-optimal code.

     /wp-content/uploads/2016/08/4_1006017.png

  2. Second, we encapsulate our code in a TV-UDF (Table Valued User Defined Function)

     /wp-content/uploads/2016/08/5_1006018.png

  3. Then, we create another Calculation View – type Graphical – and add the table function we created.

       Note that now, along with tables, we can also include our table UDFs in the nodes.

     /wp-content/uploads/2016/08/6_1006019.png

     So the table UDF behaves just like any other table and the fields gets added to the Calculation View just like any other table fields.

     
     /wp-content/uploads/2016/08/7_1006020.png

  4. Finally, we compare the performance from both the artifacts:

     /wp-content/uploads/2016/08/8_1006021.png

  5. Conclusion:

  • Performance: Both showed equal performance in this particular scenario.

     However, please remember that this cannot be treated as a basis for any benchmarking since

     this dataset differs from a real-time customer scenario where the code is complex and the volume might be higher.

     In such cases, the TV-UDF might be faster than scripting.


  • Coding: As per my understanding, the coding is still present, only relocated to a better place for a better usage.

     Therefore, the quality of code still matters and will be the fountainhead for optimization.

  • Reusability:
    • TV-UDF is reusable as a table for other modeling views, such as Analytical views.
    • TV-UDF can also be reused in regular SELECT statements and JOINS.

                /wp-content/uploads/2016/08/9_1006025.png

  • Preferred Choice: Given a choice, TV-UDF will be preferred.

The journey has just begun and, I hope, will continue to amaze us with newer and better ways to achieve results.

———————————————————————————–

References:

  1. Generating PoC Data – https://scn.sap.com/thread/3286683

  2. Table UDFs – http://scn.sap.com/community/developer-center/hana/blog/2012/12/07/table-user-defined-functions-table-udf-in-hana

To report this post you need to login first.

4 Comments

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

    1. Debanshu Mukherjee Post author

      Hi AMS HP,

      If you mean modeling view, then yes, you can use them but the graphical modeling tools doesn’t provide an easy way to implement SQL Window functions or other similar complex coding constructs.

      If you are referring to a database view, you can use them but then again the complex SQL required to fulfil the requirement can’t be accommodated.

      Please let me know if I understood your point of view and answered appropriately.
      If not, then I would request you to expound a bit more on your option.

      Reg

      Deb

      (0) 

Leave a Reply