Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Sunday, 11 November 2018 08:01

Table Functions in SAP HANA step by step guide

Written by  http://www.saphanacentral.com/2018/11/table-functions-in-sap-hana-step-by.html
Rate this item
(1 Vote)

SAP HANA Table Functions


Recently I spoke to multiple developers working on SAP HANA Native system for developing reports. What really surprised me was the fact, that most of them are

still using calculation views of a SQL Script type instead of Table Functions. What is more, some of them were even not aware that these type of views are deprecated and replaced by Table Functions. I also haven’t found any step by step tutorial describing when to create Table Functions, how to do that, what are the benefits and how to consume them in calculation views.

This inspired me to write the article about Table Functions and share my experience on that topic.

What are Table Functions?

Since SP11 calculation views of SQL Script type are deprecated. As an alternative SAP introduced new development artifact called Table Function. HANA provides Migration tool, which enables automatic conversion script-based Calculation View into Table Function.

Table functions are used whenever the graphical views are not sufficient i.e. for loops, executing custom functions or complex queries.

What are the Pros and Cons of using Table Functions?


Pros:

◈ SQL Script gives much more functionality and flexibility (more functions available, complex logic can be implemented in easier way, SQL can be combined with Application Function Library – AFL giving even more functions for complex analysis)

Cons:

◈ Maintenance is much more difficult (preserving order and data types of output columns; data preview for part of code is not so straightforward comparing to previewing nodes in graphical views; when changing output – both Table Function and view on top of it needs to be adjusted; etc.)
◈ Multiple-value input parameters are not supported (there is no easy way of passing multiple values into single input parameter to Table Function, however there is workaround for that)
◈ By definition graphical views provide better performance thanks to HANA’s optimizer
◈ No GUI available – SQL Script knowledge is necessary

Generally Table Functions should are used in case if the logic cannot be covered by graphical view or the logic is too complex to model it graphically.

How to create Table Function?


Scenario: Business wants to display a report showing number of working days between “ORDERDATE” and “SHIPPEDDATE“. To calculate it we need to use WORKDAYS_BETWEEN SQL function, which is not available in graphical view. Additionally this function should display orders only for specific period of time which will be provided by the user.

1. To start developing database objects, go to Development Perspective:

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

2. Go to Repositories tab:

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

3. Right click on destination package and select New -> Other

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

4. In wizard type Table Function, select it and click Next

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

5. Provide Table Function name and click Finish</em>

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

6. Table function window will open.

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

7. Adjust the code to the requirements

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

I. [Optional] Provide input parameters including data types. This is needed when you want to parametrize Table Function.

II. [Mandatory] Set TABLE as the output type.

III. [Optional] Provide default schema for the query. This is needed when you transport the function between instances with different schemas i.e. in development system you are using “DEV” schema, but on production all tables are placed in “PROD” schema. If you provide the default schema in the definition of Table Function, then while transporting this schema will be automatically replaced with the schema of target system (based on schema mapping). When applying default value for schema mapping, in the query you should use table names without schemas i.e simply use “ORDERS” instead of “DEV“.”ORDERS“.

IV. [Mandatory] Add RETURN phrase before the final select statement.

V. [Mandatory] Add select statement.

VI. [Optional] Apply input parameters in the WHERE clause. Add leading colons (“:”) when calling each parameter.

VII. [Mandatory] Add semicolon (“;”) at the end of the statement.

VIII. [Mandatory] List all the output columns. Preserve the columns order, column names (case sensitive) and their data types consistent with the select statement

8. After writing Table Function definition activate it and make sure that there is no error after activation.

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

Be aware that any of Data Definition (CREATE, ALTER, etc.) or Data Manipulation (INSERT, UPDATE etc.) operators are not allowed in Table Functions.

How to query Table Functions?


Calling table Functions in SQL console is very easy. They are executed in the FROM clause. When executing the function, provide whole name of the function. In brackets provide values for input parameters. If there are no input parameters – leave the brackets empty ( they are always mandatory!!! ).

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

How to consume Table Function in Calculation View?


Using Table Function as a source in Calculation View is also very simple. Create a new calculation view, and when adding object for the projection, type name of the Table Function:

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

If Table Function is using input parameters you need to recreate them also in the view. Go to Parameters/Variables tab, click on Input Parameter Manage Mapping and select Data sources.

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

Click Auto Map By Name button to automatically copy input parameters from the Table Functions to the view.

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

Now when previewing data on the view, there will be pop up with input parameters. Once values are inputted they will be passed directly to the table function and results will be displayed.

SAP HANA Tutorial and Material, SAP HANA Learning, SAP HANA Study Materials, SAP HANA Live

What are the limitations of Table Functions?


The main limitation of table function is that you can pass only single values for each input parameter, which can be very annoying, because in most cases user wants to have possibility to pass multiple values in selection criteria of his report.
Read 103 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.