Table Functions in SAP HANA - step by step guide

[KonradZaleski]

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:

  

2. Go to Repositories tab:

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

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

5. Provide Table Function name and click Finish

6. Table function window will open.

7. Adjust the code to the requirements

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.

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

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:



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.



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



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.

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.

There is a workaround for that limitation, which can help to fulfill requirement of passing multiple values directly to the query. I will describe it in the next post of my blog. 

Check out my new blog-post about: Passing multi-value input parameter from Calculation View to Table Function in SAP HANA – step by st...

[former_member267922]

Good to see this. Even we know TF is replacing SQLScript CV but not using them and keep doing SQL CV.

[susuang]

Great article Konrad! I would add two small details regarding parameters in TF’s – usage of DEFAULT values when user (or another CV which is triggering this TF), does not apply any values. So it can looks like this:

FUNCTION “_SYS_”.”workshop.test::MY_TEST_TF” (userId NVARCHAR(10) DEFAULT ‘USER123’, isTest NVARCHAR(1) DEFAULT ‘0’)

Another thing is usage of PLACEHOLDERS in TableFunctions. So if we want to pass Input Parameters as a placeholder and then use them in TF, it can looks like this:

FUNCTION “_SYS_”.”workshop.test::MY_TEST_TF” (userId NVARCHAR(10) DEFAULT ‘USER123’, isTest NVARCHAR(1) DEFAULT ‘0’)

[…body of function / SQL SELECT statement]

FROM “_SYS_”.”workshop.test2/CV_TEST” (PLACEHOLDER.”$$InputParam1$$” =>  :userId, “$$InputParam2$$” => :isTest)

Anyway, it’s really great that you’re sharing your knowledge! Keep doing that, community needs people like you

[former_member458936]

Thanks Damian, it's very motivating to hear that 🙂

And thank you for your input on that topic, very good comments!

[lbreddemann]

Thanks for the nice write-up!

Would be good if you had a look at the current SAP HANA releases (HANA 2 SP03) where you can use table functions in graphical calculation views as a kind of "pass-through" element. That is, you can "feed" a table parameter in and return a table - that's very neat.

This is of course also possible in HANA 1 when you build a data flow from table function to table function (outside of graphical calc views). One can build very powerful semantic expressions with that.
The problem of keeping the signature correct can be handled by using table types instead of inline table definitions.

Concerning the statement "By definition graphical views provide better performance thanks to HANA’s optimizer": that's just plain wrong.
Graphical Calc views nowadays are processed through the same optimization stack (when possible) as common SQL and so are table functions. Especially for more complex scenarios like multi-store tables, virtual table access or long join chains the SQL optimizers produce better execution plans. Have a read on ESX and HEX in the SAP notes if that's of interest to you.

Looking forward to reading more about your modelling experiences.

[KonradZaleski]

Hi Lars,

Thanks a lot for your valuable comment.

Do you have any reference for that new feature in SP03?

 

[lbreddemann]

Sure, the documentation has it here: Model Table Functions as View Nodes.

As with all new features this is only available in the Web IDE and not in SAP HANA Studio.

 

 

[former_member291524]

Great Article!!

Table functions are functions that produce a collection of rows (either a nested table or a array) that can be queried like a physical database table.

[KonradZaleski]

Many thanks!

[former_member600057]

Thanks everybody for the article and the valuable comments. Maybe one of you could quickly explain how to include a table function in a calculation view when using the WEB IDE instead of HANA studio. Somehow the columns of the table which is returned by the table function do not seem to show up as potential output fields when adding the table function to e.g. a projection node. Is there any additional step required?

Thanks in advance for some hints!

[mahesh_jagnani]

Hello,

i am also facing same problem, if i don't use any input parameter, it works fine but with input parameter, it is not working.

 

Please let me know if you have any suggestion.

thanks

mahesh

[laurens_cleyman]

Hi Konrad,

 

Great article! Is there any place where i can fact-check your statement:

"Since SP11 calculation views of SQL Script type are deprecated. As an alternative SAP introduced new development artifact called Table Function."

I cannot find an official source on the internet that confirms this...

 

Secondly, Is there any place i can look for upcoming changes? Not the road maps, they are very high level.

 

Kind regards,

 

Laurens

[KonradZaleski]

Hi laurens_cleyman ,

Thanks for reading my article.To answer your first question – actually I found only one official presentation about best practice for Data Modeling:

• http://www.sapevents.edgesuite.net/TechEd/TechEd_Vegas2015/pdfs/DMM208.pdf (slide 14)
• https://events.sap.com/teched/en/session/26543 (17:30)

Moreover XSA which is the new environment for SAP HANA does not support SQL scripted views, so before you migrate from XSC to XSA you will need to migrate all obsolete objects (SQL scripted views, Analytic/Attribute views etc) into the new objects (the sooner the better).

Here you can find additional blog posts on this topic

• https://blogs.sap.com/2017/09/01/overview-of-migration-of-sap-hana-graphical-view-models-into-the-ne...
• https://blogs.sap.com/2016/06/07/how-to-migrate-attribute-analytical-sql-script-based-calculation-vi...

For the latest updates the only source that I know is official SAP page:

• https://help.sap.com/viewer/p/SAP_HANA_PLATFORM
• You can also follow rich.heilman who is publishing very nice posts about SAP HANA new features on regular basis.

[laurens_cleyman]

Hi Konrad,

Thank you for your quick and comprohensive answer!

 

I have a calculation view TEST_TABLE_FUNCTION.

I need the output of projection node Source_for_TableFunction as source (FROM) of the table function.

Screenshot:

https://imgur.com/a/wgjrbQB

I am currently doing this by calling the Column View from the node directly :

SELECT "IOBJNM"

FROM "_SYS_BIC"."XXXXXX/TEST_TABLE_FUNCTION/dp/Source_for_TableFunction";

Is there a way to connect the output of the Source_for_TableFunction node directly to the TableFunction node?

Reason:

The functionality of the table function must also be used in other calculation views. So I can not use the name of a node in the FROM section.

[KonradZaleski]

Can't you just create SELECT SQL query directly on top of calculation view (TECH_IOBJ) instead of calling the node with that calc view?

Anyway It would be better if you raise that question on SAP Community with detailed description.

[former_member195433]

@konrad

Great Post ! Is there any way to Debug a table function ? like viewing Temporary values and table etc.

[KonradZaleski]

It is not possible to use debugger for table functions however you can put the sql logic of TF into the anonymous block and run it in SQL Console. There is nice article by doteiserman , where she describes that process in details.

[former_member286361]

Hi Konrad,

I tried your example, and transfered it to my use case (I need to join a column as a filter, containing a partner list). Unfortunately I can't activate the calc view:

after puting table function in the projection node it fails with this error:

 

Mapped source attribute partner is missing in node _SYS_SS2_RETURN_VAR_(calculationNode (finalProjection) -> inputs -> input (getPartnerList_forDelta) -> mappings -> mapping (partner))

 

Is there something I have missed?

[former_member628477]

Hey group members, while trying to activate the SAP HANA Development objects, I am getting an error which says"unable to determine schema mapping" in the job details pane, also, "A number of objects were not activated because they contain errors, or their activation would cause errors in affected objects". Can anyone tell me how to resolve this error? Any help would be highly appreciated!!

[kirill_jung]

Hi Alexander,

i've got the same error and it was due to the low case letters in the column names.

After i've changed them to upper case in the definition of the table function, the calculation view could be activated ...

Best Regards,

Kirill

[lek_chunhui]

Thank You

[shrinivasan]

Hi

I am getting below error message while activating my hana object.

"UNABLE TO DETERMINE  SCHEMA MAPPING"

FUNCTION "VN50IYX"."ZVn50::ZTEST_TABLEFUNCTION" (IP_FROM_DATE DATE, IP_TO_DATE DATE)
RETURNS TABLE ("ORDERNUMBER" INT, "ORDERDATE " DATE, "SHIPPEDDATE" DATE, "CA_WORKDAYS_DIFF" INT)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA ZVn50
READS SQL DATA AS
BEGIN

RETURN
SELECT
"ORDERNUMBER",
"ORDERDATE",
"SHIPPEDDATE",
"WORKDAYS_BETWEEN"('PL',"ORDERDATE","SHIPPEDDATE",'ECC')AS "CA_WORDAYS_DIFF"
FROM
"ORDERS"
WHERE "ORDEREDDATE" BETWEEN :IP_FROM_DATE AND IP_TO_DATE;

END;

[andrewlewis]

I know this is a long time since the original post, but I am trying to create a Table Function in our ECC HANA, which consumes a virtual table.

That virtual table is the result of a HANA calculation view in the BW system, which has a mandatory input paramete.

 

SQL Code to query the virtual table, including the input parameter:

select count(*)

from "_SYS_BIC"."BW_CA_SEASONAL_SALES" (PLACEHOLDER."$$ip_Site$$"=>'270')

 

That executes perfectly

 

In the Table Function, I am using the below code, but cannot activate the TF as I get an error message.

 

	   from 

	   		"_SYS_BIC"."BW_CA_SEASONAL_SALES" (PLACEHOLDER."$$ip_Site$$" => '270')

 

I have tried with a variable from the TF as well:

 

"_SYS_BIC"."BW_CA_SEASONAL_SALES" (PLACEHOLDER."$$ip_Site$$" => :ipSite)

 

Both return with the error message : "Dependent object not found: SqlScript; _SYS_BIC.BW_CA_SEASONAL_SALES: symbol not found"

 

Any ideas on what I am doing wrong ?

[andrewlewis]

As an addition to the above, I have replicated the scenario, but not used a virtual table.

In this new scenario, I have a calculation view that has a mandatory input parameter, that calculation view is then consumed via a table function. The table function has it's own input parameter, which is mapped to the calculation view input parameter using the PLACEHOLDLER terminology.

I can activate the Table Function, and conusme it in another calculation view.

 

So the problem, for me at least, seems to be in the virtual table not allowing the PLACEHOLDER functionality in a table function. As shown in the code examples in my previous post, it absolutely handles / considers the input parameters when consumed in a SQL script, so this is a bit confusing.

[oliver_reichardt2]

Hi Konrad,

thanks for your good introduction into Table functions. Calculation workdays between two dates is excatly what I need at the moment.

But I have the following problem with this:

I have a graphical calculation view with projection node. This node has order numbers and different date fields for each order number calculate the workdays between. So using Input parameters is not an option for me, as the dates for calculation differ for each order.

Using the table function in a join and linking the date fields of my projection with the input parameters of the table function doesn't work - I get a error message A'ttribute 'IP_FROM_DATE' not found in result for conversion".

Is it possible to cover my requirement with a table function? - I am using HANA 1.0 SPS11

Thanks in Advance,

Oliver

[KonradZaleski]

Hi Oliver,

I'm not sure if I understood your issue. Could you share your TF code? Or better open a question on sap community, describing your scenario with more details.

[oliver_reichardt2]

Hi Konrad,

my TF-code is the follwing:

FUNCTION "SAPABAP1"."xxx::TF_NUMBER_OF_WORKDAYS" ( I_FROM_DATE DATE , I_TO_DATE DATE )
RETURNS TABLE
(
"I_FROM_DATE" DATE,
"I_TO_DATE" DATE,
"CA_WORKDAYS_DIFF" INT
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
DEFAULT SCHEMA "SAPABAP1"
AS

BEGIN
RETURN
SELECT
"I_FROM_DATE",
"I_TO_DATE",
WORKDAYS_BETWEEN('12',"I_FROM_DATE","I_TO_DATE", 'SAPABAP1') AS "CA_WORKDAYS_DIFF"
FROM
"DUMMY"
;
END;

 

My intention is to use it like this in a graphical calculation view:

TF in graphical View

[KonradZaleski]

Hi Oliver.

You are trying to use Table Function as Scalar UDF function and consume it within CV, but it doesn't work like that.

You need to implement entire logic as TF, meaning to query the data from VBAP table there. Create a table function with following logic and then consume it via Calc View (no joins  between VBAP and TF are required, since entire logic will be implemented in TF):

SELECT

"MANDT",

"VBELN",

"POSNR",

WORKDAYS_BETWEEN('12',"ERDAT","ZZDATGALFA", 'SAPABAP1') AS "CA_WORKDAYS_DIFF"

FROM

VBAP;

Also make sure that in SAPABAP1 schema, there is a TFACS table which is mandatory for WORKDAYS_BETWEEN function.

[oliver_reichardt2]

Hello Konrad,

thanks for your quick reply. Unfortunately for me it is not as easy as it looks in your example because my calculation view is a more complex. The data is not only read from one table but from a more complex model involving many tables (my graphic was just an simplified example to focus on the main problem here)

So when I understand you right, I need to implement my whole logic in a TF and not only a part?

[KonradZaleski]

The best would be to create whole logic as TF, however if you prefer to simplify it and avoid recreating whole logic, you can create a TF which will query your data from calculation view instead of a table (SELECT * FROM <Calculation View>)

[former_member296478]

Hi,

Super article, We have used the tablefunction instead of the scrip view, but now we seem to have a license problem , as we are informed that we are  not allowed to use tablefunctions with a runtime license only.

Do you have any knowledge ot that ?

 

Br.

 

Kjeld Otte

[KonradZaleski]

Hi Kjeld,

Sorry but don't have licensing knowledge.

Regards,

Konrad

[former_member646210]

Nice

[former_member749125]

I am getting an error. Dependency object not found.sqlerror.

can anyone please help me to resolve this.

[former_member701856]

Hi,

 

Great article!

How can I consume Scalar UDF function within a CV?

For example: I already have a cube modelled as a CV and I want would like to create Calculated Columns using function calls.

 

Best regards.

[KonradZaleski]

Hi Marcos,

Unfortunatelly it is not possible to consume scalar UDFs in graphical Views.

Regards,

Konrad

[former_member808885]

Is there a workaround for non-cumulative KFs on hana views? I was thinking about deploying standard CDSs models for inventory management, modifying the tables with the custom or standart BIC tables in SAP BW hana and then joining/unioning them in hana sql calculation views. Would these work? Or do we have to use queries for snapshots of stocks to consume in hana views? thanks

[nhaydon70]

I know this is an old thread, but what about calling scalar functions from a calculated column. I keep getting a "user-defined function is not found in catalog" error. I am guessing this cannot be done but thought I would ask.

[vanama123]

I have a good sql logic inside a table function and when executed with where clause filter, performance is excellent and I know this filter is pushed down to the lowest level based on my logic inside.

But when I wrapped up this table function in a projection node of a cube calculation view model, when I apply the same where clause filter on the calculation view in SQL Console, the filter is not being pushed down from CV to Projection Node to Table Function, not able to see similar performance. I think in this case it is executing the table function in wide open mode and then applying the where clause, which is causing extremely bad response in my case.

How do I force to push down where clause filter from CV consumption to SQL Logic inside Table Function ? am I supposed to change any properties or am I missing a step to have full push down like this ?

Let me know if you have the similar experiences/resolutions in the past!

-satya