Calling Table functions in SAP HANA calculation views

[abhimanyu_sharma]

Most of the times, we came across requirement to use SQL functions in HANA calculation view in order to derive calculated dimensions. But there are few functions which can’t be used in Calculated Dimensions straight forward like ADD_WORKDAYS, WORKDAYS_BETWEEN etc. These are really powerful SQL l functions to get the workdays between two days or adding workday to existing date. These functions will take care of public holidays and weekends as internally it reads TFACS table which stores the factory calendar.

 

There are lot of blogs which provides the workaround of using these functions by transposing the Factory Calender which seems to be very tedious task and consumes lot of time. Also from best practices It doesn’t meet that criteria.

 

Hence, the approach which we can use here is to use Table functions and use these SQL functions into it and get the output. Then , call this table function in SAP Calculation view by adding a projection node.

 

One of the requirements that I have faced recently in my project is to add workdays in the existing date.

Requirement :

If my move in date > future date, then my target bill date should be move in date + 1 workday  ( excluding weekends and public holidays )

If my Move in date <= current date , then my target bill date should be Move in Document date + 1 Workday  ( excluding weekends and public holidays )

Analysis :

I did my analysis and found out that we can use SQL functions ADD_WORKDAYS to get this requirement done.

Approach :

 

1. First I have checked that if I can use this function directly in Calculated Dimension , but unfortunately it doesn’t support these functions ( may be due to older version ) as mentioned above

 

2. Then I came up with the approach of using Table function and deriving above logic into it. After that I can consume the output directly in my calculation view by adding this table function as projection node.

 

Steps :

 

1. Open the HANA repository, go to the package and right click and select Table function


2. Create the table function and write the logic in it :

BILL_DOC = Billing Document

0UC_MOVEIN_D = Movein Date

0CREATEDON = MoveIn Document Date

3.	FUNCTION <SCHEMA NAME>:Z_TF_TARGET_BILLDATE" ( ) 

4.		RETURNS TABLE ("BILL_DOC" VARCHAR,

5.							"0CREATEDON" VARCHAR,

6.							"0UCMOVEIN_D" VARCHAR,

7.							"ZTARGETBILLDATE" VARCHAR)

8.		LANGUAGE SQLSCRIPT

9.		SQL SECURITY INVOKER DEFAULT SCHEMA

10.		 "SAPHANADB" AS

BEGIN

11.	RETURN

12.	SELECT 

13.	

14.	A."BILL_DOC",

15.	A."0CREATEDON" ,

16.	B."0UCMOVEIN_D",

17.	

18.	

19.	CASE WHEN ( B."0UCMOVEIN_D" <= CURRENT_DATE)

20.	THEN

21.	ADD_WORKDAYS('GB', A."0CREATEDON", 1, 'SAPHANADB') 

22.	WHEN ( B."0UCMOVEIN_D" > CURRENT_DATE)

23.	THEN 

24.	ADD_WORKDAYS('GB', B."0UCMOVEIN_D", 1, 'SAPHANADB') 

25.	ELSE ''

26.	END "ZTARGETBILLDATE"

27.	

28.	from "_SYS_BIC"."system-local.bw.bw2hana/ZTEST_123" AS A 

29.	INNER JOIN 

30.	"_SYS_BIC"."system-local.bw.bw2hana/ZTEST_456" AS B 

31.	ON 

32.	A."BILL_DOC" = B."BILL_DOC";



END;

 

3. Once it is done and tested, we can add this in calculation view as projection and use the target bill date in it.

 

Conclusion :

 

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.