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

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:

Source: scn.sap.com