Monday, 1 August 2016

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

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

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

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

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

BUSINESS_UNIT_D

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

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.

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

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

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

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.

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

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.

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

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

Table Valued - UDFs Vs Scripted Calculation Views - Rudimentary Exploration

Source: scn.sap.com