Summary about Table Function and Analytical Privilege in SAP HANA

[pallab_haldar]

Today in the first part I will discuss about the table functions and its utility in HANA . Why we will use table functions?

 

1.Table function : Table function is a piece of code written is SQL script in HANA which used to return data as a tabular format after calculating a business scenario logic.Complex logic can be  can be combined with Application Function Library – AFL giving even more functions for complex analysis.Is is acting as a read only procedure.

A simple table functions -

Function TEST_PLB (IP_START_DATE DATE ,IP_END_DATE DATE)

				Return Table(EMP_ID NVARCHAR(9), 

							DESIGNATION NVARCHAR(50) , 

							PROMOTION_ELIGIBLE NVARCHAR(2))

				Language SQLSCRIPT

				SQL SECURITY INVOKER AS

				

BEGIN



RETURN(SELECT * from EMPLOYEE);



END;

Call the table function -

 

select * from SCHEMA.TEST_PLB(IP_START_DATE DATE ,IP_END_DATE) ;

2. When We will use table function ? :

• When we will need a complex logic that can not be implemented in calculated column we will use table function. Can  be used as a input to  in SAP HANA calculation view, procedure or another table function in HANA 2.0. Now there is a separate table functions in HANA 1.0 it was not possible.


• In the time of migration Table function used to convert scripted calculation view into graphical calculation view.

3. Advantages of Table function over : 

• In can be used as an input to another modeling component or object.


• It executed in  on HANA Engines(Calculation engines) that mean it will have multiple process.


• It is read only and faster than Procedures.


• You can modify the Table function.

 

Let's discuss about different kind of analytical privileges : 

Analytical privileges restrict users at row level and grant different users access to different portions of data in the same view.

1. Classical Analytical privileges where you can provide the filer selecting the field Graphically.HANA 1.0 is not supported in HANA 2.0.


2. SQL analytical view used to implement the filter condition is SQL script to implement the logic.

• In SQL analytical privileges you can define the privilege as dynamic analytic privileges by selecting the radio button.

A sample SQL Analytical Privilege (.hdb analytic privilege) :

 

1.Inside SAP HANA Database Module choose New -> Analytic Privilege.
2. Find Data Sources (Calculation view or CDS view).
3. Enable SQL Privilege by enabling SQL access
4. Associated Attribute Restrictions section, choose + (Add).
5. In n the Restriction Type dropdown list, choose.
6. the sample code which you need to add should look like it.

 

CREATE STRUCTURED PRIVILEGE AP_ROW_VIEW_CUSTOMER FOR SELECT

	 ON "VIEWOWNER"."ROW_VIEW_CUSTOMER" 

WHERE (CURRENT_DATE BETWEEN 2023-02-01 AND 2025-02-11) AND YEAR IN (SELECT VALUE FROM VIEWOWNER.AUTHORIZATION_VALUES WHERE USER_NAME = SESSION_USER)

;

 

## In Dynamic Analytical SQL privileges :

An authorization table use to store the USERNAME and the CONDITION to restrict the row data.
The a procedure created which will provides the matching condition based on user login.
Then create Analytical privilege of SQL type.Assign the Analytical privilege to the user.

****Create a Authorization tablle SQL Analytical Privilages : *************



CREATECOLUMNTABLE"EKMON_SCHEMA"."TEST"::"CUSTOMER_DYNAMIC_AUTH"("USER"NVARCHAR(200),



"FILTER_COND" NVARCHAR(400));

Insert some values to the authorization table.

Then Create a procedure which will provides the matching filter condition :

 

CREATE PROCEDURE "EKMON_SCHEMA"."TEST"::"CUSTOMER_DYNAMIC_AUTH"(OUT FILTER_COND VARCHAR(600))



Language SQLSCRIPT

SQL SECURITY INVOKER AS

READ SQL DATA AS V_FILTER_COND VARCHAR(600);



CURSOR V_CURSOR FOR SELECT "FILTER_COND" FROM  "EKMON_SCHEMA"."TEST"::"CUSTOMER_DYNAMIC_AUTH"



WHERE"USER_NAME" = SESSION_USER;



BEGIN



OPEN V_CURSOR;   



FETCH V_CURSOR INTO V_FILTER_COND;   



OUT_FILTER := V_FILTER_COND;   



CLOSE V_CURSOR;



END;