Summary about Table Function and Analytical Privilege in SAP HANA
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.
- Classical Analytical privileges where you can provide the filer selecting the field Graphically.HANA 1.0 is not supported in HANA 2.0.
- 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;