Technical Articles
Table Functions in SAP HANA
In this article I’m going explain how to create SAP HANA Calculation View using Table Function. In my previous article SAP HANA Calculation View – SQL Script, I explained by using SQL Script with SQL Scripted Calculation View, but in current article I’m going to use the same SQL code using in “Table Function”.
In SAP HANA, there are multiple ways to provide a solution for a given business requirement. Choosing Method-1 or Method-2 or Method-n depends on various parameters/situations. It is not like using Scripted Views are old method and using Table Functions are new method. In below example, I used the same code (from my previous article), it is working in the below Calculation view. But it is always good to choose the best method based on requirements/situation.
We are getting new things from version to version in SAP HANA DB, because SAP is doing lot of R&D and technology is evolving.
Lets start our journey with User-Defined Functions (UDF), and in this article I’m going to explain/use Table User-Defined Functions (UDF).
In SQL, we have Scalar and Table User-Defined Functions (UDF), User Defined Function (UDF) enables you to build complex logic into a single database object.
Scalar user-defined function (UDF) is a custom function that will returns only one scalar value as output.
Table user-defined function (UDF) is a custom function that will returns only one Tabular value as output.
Step 1: Create a table type which is required to display OUTPUT (it may be from one table or from multiple tables).
In SAP HANA Calculation View – SQL Script, I used five tables, here also I’m going to use the same five tables and same OUTPUT fields.
Use the below SQL Code and create a Table Type.
See the table type once the above code is executed successfully.
Step 2: Use SAP HANA Development Perspective, create a Work Space in Repositories and create XS Project in Project Explorer and assign Work Space.
Then go to Repositories, Create a Repository Package, or if you have already then choose your repository package and create new Table Function and give File name “GET_SALESORDERS_TF”
The below Function will creates with template (code).
Step 3: We need to do simple modifications to above code and keep our code here.
Activate.
Step 4: Create a Calculated View in SAP HANA Modeler Perspective and call/assign the above Table Function. Once we add the Table Function, then it will show all fields which are available in Function (Table Type).
Add all fields in Projection–>Aggregation–>Semantics.
Save & Activate.
Step 5: Display Data.
Surendra,
Just for clarification, it is important to note there should no longer be an either/or choice consideration between the two approaches of Scripted Calculation Views vs Table Function based Calculation Views. Since SAP is deprecating Scripted Calculation Views, the recommended approach for all new modelling is Table Function based Calculation Views, as described in the blog: Table Functions in SAP HANA – step by step guide by Konrad Załęski.
While Scripted Calculation Views will continue to be supported in the HANA XS Classic architecture, once the next major version of HANA is released, XS Classic will be completely replaced by XS Advanced, as per SAP Note 2465027, so those customers wishing to upgrade to the next major release of HANA will need to plan for the migration of Scripted Calculation Views. SAP’s recommendation is reiterated in the following documentation:
Convert Script-based Calculation Views to Graphical Calculation Views
So in a nutshell, going forward, any new modelling requiring scripting in calculation views should be performed using Table Function based Calculation Views.
Regards,
Mustafa.
Hello Mustafa,
Thank you, I saw Table Functions in SAP HANA – step by step guide by Konrad Załęski. it is good blog and have the details & comments.
You are correct, but the problem is…….
1. We are getting so many versions and releases, most of the developers and customers are on confusion.
2. Today, still some people are developing views with GUI and without using and SQL code because there is a lot of confusion and they know classic BW/BI they were not into SQL Code.
3.If we do all our developments using current trend eg: Table Function, and what is the guaranty like tomorrow again SAP releases some other new feature/function. So there is lot of confusion.
Due to so-many versions & releases, everybody is in confusion, most of the people don’t know what feature is there in what version. Of course we have SAP documentation on online, but do we have time to ready all documents, understand it and implement it? I don’t think so.
There is no time to developers to focus on upgrading themselves and I don’t think most of the companies are providing proper training’s and giving good time to developers to understand current technology.
Only work pressure is there on Developers from Management, like work on latest technology but without enough skills and confident and with tight project schedule.
At some point we need stability, I can understand HANA is evolving technology and every release we are getting new things.
These are all what my findings, and I agree with you, it is good to use new features.
Regards
Surendra
That "next thing" is documented and is also the reason why scripted views (among others) are not recommended, as they won't migrate as Mustafa Bensan quotes.
So if there is confusion, even if I value this effort to contribute to the community, your blog post is not helping to clear it.
I'm wondering how you reach the conclusion and recommendation in your second paragraph, as this post implies you missed the multiple existing posts on this subject (some dating back to 2016) and ignored the recommendations too.
I understand that as functionality and products evolve, it may be hard to keep up with the changes but we are talking about something that has been out there for more than a couple of years. I'm interested in other sources of confusion, setting the lack of time to invest on self-training aside.
On that point, as of this date, I would recommend this playlist to catch up on the latest and the greatest: https://www.youtube.com/watch?v=eGOxt_acA7o&list=PLoc6uc3ML1JR38-V46qhmGIKG07nXwO6X
Thank you Lucia, I understand what you mean and the link is useful, it helps to understand latest changes.