Skip to Content
Author's profile photo Former Member

Table Valued – UDFs Vs Scripted Calculation Views – Rudimentary Exploration



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)


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.



Example Data:





Required Output:

Business Unit


Total Sales

3 months Running Total Sales


  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.



  1. Generating PoC Data –

  2. Table UDFs –

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Yes, please do continue the journey and share more insights 🙂

      Author's profile photo AMS HP
      AMS HP

      If I create one view and use it instead of UDF ?

      Author's profile photo Former Member
      Former Member
      Blog 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.



      Author's profile photo AMS HP
      AMS HP

      I mean using view as datasource in projection / Aggregation instead of UDF.

      Author's profile photo Former Member
      Former Member

      Good Article..How do you compare this with Database Views?