SAP HANA Table Functions

It has been very quite at the moment.Because nothing spectecular is happening in the Analysis Office world. SAP released Analysis Office 2.7 SP5. I still hope they get Analysis Office 2.8 back on track and without so much problems as they have today.  But back to topic.

Recently I had the opporunity to work with SAP HANA Table Functions. First I had a complex SAP HANA Calculation View with Joins, Aggregations and so on. But I read a blog post on the SCN by Konrad Załęski and thought maybe I can use Table Functions to do the same as I do with my Calculation Views.

 

I use Eclipse 2018-09 (4.9.0) and the BW Modeling Tools 1.19.35 so maybe the options are not at the same position in another version. As far as I know SAP, the buttons are definitly not at the same position anymore. 🙈

 

To reduce the complexity I used three calculation views to narrow down my problem step by step.

Calculation View 1
Calculation View 1
Calculation View 2
Calculation View 2
Calculation View 3
Calculation View 3

After I tested my SQL commands to get the same results, I could remove Calculation View 1 with this simple SQL Statement.

FUNCTION "SCHEMA"."test::test_function" ( ) 

RETURNS TABLE ("POSITION" NVARCHAR(4),

"CHANNEL" NVARCHAR(3),

"PRODUCT" NVARCHAR(20),

"MATERIAL" NVARCHAR(18),

"CURRENCY" NVARCHAR(5),

"AMOUNT" DECIMAL(17,2),

"YEAR" NVARCHAR(4))

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

BEGIN

RETURN

/*

* Get all Position, Sales Channels for all Products and Currency 

* where Process = Allocation and Market Number is empty.

*/

SELECT DISTINCT "POSITION",

"CHANNEL",

"PRODUCT",

"MATERIAL",

"CURRENCY",

sum( "AMOUNT" ) as "AMOUNT",

"YEAR"

FROM "SAPABAP1"."/BIC/ATEST2" where "PROCESS" = 'A' and "MANUM" = ''

group by

"POSITION",

"PROCESS",

"CHANNEL",

"MANUM",

"PRODUCT",

"MATERIAL",

"CURRENCY",

"YEAR";

 

END;

After that I was thrilled with SQL commands. It's been a while since I used SQL. Maybe the last time before I started as a BW consultant. So I looked into it. You find a lot of help on help.sap.com or on udemy.com. Since then I just think about my existing Calculation Views and how I can make them better with Table Functions.

 

So here is my question for this post. Do you use Table Functions and how complex do you build them?

These posts might also be interesting:

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

Analysis Office - The Comprehensive Guide 4th edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.7 and contains 299 Pages.
59,99 €
Analysis Office - The Comprehensive Guide 3rd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.6 and contains 272 Pages.
39,99 €
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
29,99 €
Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
9,99 €

Write a comment

Comments: 2
  • #1

    Aaron Benner (Monday, 06 May 2019 22:20)

    Great post! I think you are saying, you have replaced graphical calculation views with table functions?
    If you were already using sql scripted views, it seems table functions are quite similar. It is just that SAP has decided to deprecate sql script in favor of table functions (to allow/encourage reuse as far as I know).

  • #2

    Tobias (Wednesday, 08 May 2019 19:11)

    Hi Aaron,

    sorry for the delayed answer. Yes I replaced the graphical calculation view with a table function. I don't use sql scripted views because they are deprecated by SAP and should not longer be used. Yes it is just SQL. So you are right it is the same old "shit" in a new dress.