Misaq Tonse

September 7, 2016 3 minute read

Table Functions in SAP HANA – Demo

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.

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:

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.

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.

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

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.

Result of Table Function.

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

25 Comments

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

Former Member

November 18, 2016 at 7:14 am

THANK YOU SO MUCH. VERY USEFUL.

Sudhakar Gurram

December 14, 2016 at 6:22 am

I read somewhere that Table Functions can read R as well. Can you confirm on that

Misaq Tonse

Blog Post Author

April 13, 2017 at 9:56 am

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

Former Member

December 30, 2016 at 12:48 pm

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 ??

Ibrahim Noorali

March 20, 2017 at 10:27 am

As per the table function syntax, the phrase should be RETURNS.

Misaq Tonse

Blog Post Author

April 13, 2017 at 9:59 am

Yes, That's Right Ibrahim!

Subhasish, Please correct the keyword to RETURNS instead of RETURN

Lakshminarasimhan N

April 5, 2017 at 2:20 am

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

Benedict Venmani Felix

April 5, 2017 at 3:59 am

Hi Lakshminarasimhan,

You can register for SAP JAM. The SAP HANA International Focus Group @ SAP JAM has various webinar sessions whenever a new product or feature is released. It would be a good place for the kind of information you are looking for. Also, the SAP HANA Academy channel in YouTube is a great place to start.

Lakshminarasimhan N

April 5, 2017 at 5:26 am

Thanks Benedict 🙂

Mujahid Mohammad

October 22, 2018 at 4:16 am

Hi Benedict,

how can I register myself with SAP JAM ? It is asking me to request for invitation to join the group.

A quick response will be greatly appreciated.

Thanks.

Ashwin Narayan

April 17, 2017 at 4:29 pm

Hi Misaq,

Great Blog!! Way to go. Very helpful .

Thanks,

Ashwin

Utente Vimar Generico Utente Vimar Generico

June 12, 2017 at 12:58 pm

Hi Misaq,

Thanks for this great Blog!

Have you try to use the Table Function with return table containing SECONDDATE data type field? I have a problem with this as described here https://answers.sap.com/questions/220949/hana-modeling-table-function-doesnt-return-secondd.html.

Any suggestion?

Thanks,

Denis P.

Misaq Tonse

Blog Post Author

June 13, 2017 at 6:52 am

Hi Utente,

SECOND DATE is always interesting and unique!

Your question has been answered in the thread opened by you 🙂

Good luck!

nilima rodrigues

June 27, 2017 at 11:13 pm

Hello Misaq,

May be a basic question but I would like to understand why would I use the table-function instead of using the stored procedure concept ? I am trying to understand in which scenarios table function based calculation view makes more sense than using procedures?

Thank You,

Nilima

Misaq Tonse

July 3, 2017 at 9:14 am

Hi Nilima,

A procedure is a module that is intended for particular task. They are reusable processing blocks can be called with CALL statement in SQL console. You cannot report on top of a procedure.

To have meaningful report(for the business decision) , we can create a procedure and then we call the procedure in the calculation view (i.e. Table Function)

For instance a scenario, in which you are creating a Table function and calling a procedure in it.

Former Member

November 23, 2017 at 6:11 am

Things that a Table function can do but "SQL Script (stored procedure)" in a scripted calculation view cannot:

Reuse the same script in multiple calculation views
Select directly from table function using select statement (although it's just a change in syntax i.e., use select instead of call)

Former Member

July 18, 2017 at 7:29 pm

Hi Misaq,

Thanks. This is very much helpful and useful.

However, some questions and doubts.

By default, this table function will get created in the schema of the user who logged in. If I log in with my ID as MAHENDRA, this function definition will get created/opened as "MAHENDRA.<package path>::<Table Function Name>
Even though we can edit the this schema in the definition, is there a way we can do a dynamic schema mapping.
As we have schema variance while moving into higher environments, is there a way we can auto map the schema.

We are stuck up with the schema where our table function is getting created. It would be great if you can throw some insight on this.

Thanks in Advance.

Mahendra.

Misaq Tonse

Blog Post Author

July 25, 2017 at 10:05 am

Hi Mahendra,

Sorry for the delay in my response.

Table functions acts like FUNCTIONS, you can use them in your calculation views to address the business scenario. They are present in your schema under the folder Function.

I would say, as a developers you create all your table functions in one schema and use them wherever you require. (make sure your id has the access on this schema)

If you want to promote any of the deliverables (calculation view or Table function) from DEV to QA, please do a schema mapping from your DEV_SCHEMA to QA_SCHEMA in QA system.

Former Member

August 11, 2017 at 6:11 am

Hi Misaq!

Can you explain me in more detail about mapping from DEV_SCHEMA and Q_SCHEMA or PROD_SCHEMA?

For example - in Table Function I have sql like "select * from DEV_SCHEMA."/BIC/ATABLE" ".

I have an error on transport in to PROD_SYSTEM , because prod system have another shema .

Thanks in Advance.

Alexander

Misaq Tonse

November 6, 2017 at 9:57 am

Hi Alexander,

Sorry for the late reply, This can be fixed by Schema Mapping, You need to map the references of script-based calculation views / Table Functions and procedures manually by changing the script, and by checking if the tables are qualified with the schema. If the tables are not qualified, the default schema of the view is used, and the schema mapping is also applied to the default schema.

For more details refer : https://help.sap.com/doc/fc5ace7a367c434190a8047881f92ed8/2.0.00/en-US/4cba3069bc39448581e7f83d52218bc5.html

Kabilarasan R

January 31, 2018 at 6:10 am

Hi Experts,

I created a Table function. I want to know how to move it from Dev System to Quality System..

I also tried the delivery units. it move only views and other object it wont move Table Function...

Misaq Tonse

Blog Post Author

April 11, 2018 at 1:42 pm

Hi Kabilarasan R

There are 2 ways.

Take the Table Function definition and replace the reference to the DEV table with that of QA tables. and ask the DBA to run the script in QA.
Embed the Table Function into a graphical based CV and promote it to QA as part of Delivery Units.

Kabilarasan R

January 31, 2018 at 6:34 am

Hi Misaq T ,

In a Body of the table function, I tried to use Common Table Expressions(CTE) lith With CTE... But it Throws a syntax Error i.e., Return is misplaced , near that... WITH

Veera Kommineni

April 21, 2020 at 5:37 pm

Hi Misaq,

logic in your screenshot of table function is not clear . please can you zoom it up.

Misaq Tonse

Blog Post Author

May 6, 2020 at 6:01 am

HI Veera Kommineni ,

Place below code under RETURN:

select F.ORDER_NUMBER, C.CUST_NAME, C.SHIP_CITY, 
       C.SHIP_COUNTRY, F.ORDERED_DATE,
       F.QTY_ORDERED, F.AMOUNT,
	   case when C.SHIP_COUNTRY = 'INDIA' then 0.99
		 else F.DISCOUNT 
	   end new_discount,
       F.COST_PRICE
from <SCHEMA>."FACR_ORDERS_DATA" F
join <SCHEMA>. "CUST_INFO_PRI" C
      on F.CUST_NO = C.CUST.NO

Table Functions in SAP HANA – Demo

Assigned Tags