Dear All,


          Post SPS 11 there are changes and restructuring in the way of HANA modeling. In this document I am trying to discuss about Table Functions (User Defined Functions).

The recommendation is to make use of Graphical Calculation views as the end product, so that the complexity of the information views are hidden inside Table Functions.

SAP recommends to Migrate Script-based Calculation Views to Table Functions and then into the Graphical Calculation Views. In the future, SAP recommends using graphical calculation views for modeling any analytic use cases. A migration tool within the SAP HANA modeler allows you to convert existing script-based calculation views available in your system to table functions and graphical calculation views.


First of all let us understand what is Table Function? And how they are different from stored procedures?

Table Functions are read-only user-defined functions which accept multiple input parameters and return exactly one results table. SQLScript is the only language which is supported for table functions. Since these functions are ready-only and side-effect free, only read-only statements like SELECT can be used within the function. So you may not use statements like INSERT, UPDATE or DELETE.

Whereas Stored Procedures groups the SQL statement into a single block. Stored Procedures are used to achieve certain result across applications. The set of SQL statements and the logic that is used to perform some specific task are stored in SQL Stored Procedures. Procedures can make use of DDL languages and can be called using “CALL” statement like below.

IMG1.png

On the other hand Table functions can be executed in the FROM clause of your SELECT statements. You can pass the input parameters as well. You can call the function from the SQL Console as shown:

IMG2.png

Ideally the table functions are used as the Data Foundation for the Graphical Calculation view. (We will see this in the demo example in the upcoming pages)

In this document I would like to show how to address a business scenario with the help of Table Functions. (By this we are replacing Script based calculation view)

Scenario: Creating the information model to get the Customer Master Data (CUST_INFO_PRI) and Fact Order Data (FACT_ORDERS_DATA) by joining the tables on CUSTOMER_NUMBER. If the SHIPPING CITY is INDIA then create the new column as NEW_DISCOUNT = 0.99; for others existing discount is applied.

  • Open the HANA Development Perspective. Create the Project and the Workspace associated to it. (Your package is imported in this Workspace)

Right Click on the Workspace ->New-> Others.

Select the Table Function Wizard.

IMG3.png

Select the Project having the relevant Workspace and the package underneath. Provide the filename.

.hdbtablefunction will be appended upon clicking FINISH.

The Table Function editor will open for you. Add your Logic.

IMG4.png

Save and activate. Upon Activation you will find below object under your package.

IMG_0.png

  • Switch back to the Modeler perspective. Now we will see how to execute and use this Table function as part of Information modeling.

To check for the data consistency open the SQL console.

IMG5.png

Result of Table Function.

IMG6.png

Now we can use this table function in the calculation view, like any other table.

Conclusion: As recommended by SAP, We have used the concept of Table Function inorder to replace the script based calculation view.


Kindly spare some time and provide your feedback inorder to enhance my knowledge.

Thanks for reading!

Thanks for your time!!

Thanks for your support!!!

To report this post you need to login first.

10 Comments

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

    1. Misaq T Post author

      Hi Sudhakar,

      If you can see the syntax of Table function I have used “LANGUAGE SQLSCRIPT” which tells the HANA system that SAP HANA SQL Script would be the language used to code the logic for this table function. You can try using the analytical language ‘R’ instead, by using “LANGUAGE R”.

      (0) 
  1. Subhasish Haldar

    I am trying to use WITH clause in UDF, it’s throwing syntax error “Return is incorrect or misplaced” I have tried with

    – ‘return’ before the ‘WITH’.

    – ‘return’ before the final ‘SELECT’, still error continues, any help ??

    (0) 
  2. Lakshminarasimhan N

    Hi, Nice document. Is there any place where i can check what SAP is heading to.

    For example – I want creating SQL script based cal. view for many scenarios.Now i realize “Table Functions” soon going to replace the Script based cal. view.

    If i had not come across your document, i never know!! 🙂

    So is there any place i can look for the SAP’s upcoming changes(From developer point of view).

    I have checked road maps for HANA, its very much high level(No mention of Table functions there).

    (2) 

Leave a Reply