Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

[Former Member]

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

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

BUSINESS_UNIT_D

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.

    

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

    

  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.

    

     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.

     
    

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

    

  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.

               

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