Data preview on Intermediate nodes of a CV in HANA cloud/On-premise

Data preview on intermediate nodes of a calculation view is a regular task while using HANA studio as a development tool. it is useful for debugging/troubleshooting data output at each node level of a CV.

But after migrating to HANA XSA(On-Premise) or HANA cloud(Cloud platform) then you will need to use Web IDE for HANA(On-Premise) or Web IDE fullstack(Cloud) as development tools. Usually, Web IDE is connected to Dev instance and Not to Prod. if you try to connect to Prod, there is a risk of unexpected changes deployed in Prod.

In this situation, if you wanted to do the data preview on a graphical CV in Web IDE, it can only happen in Dev instance. For Production, we will have to implement the firefighter access set up and run SQL to generate data preview on the intermediate nodes.

Note: This process is applicable even if you are using Business Application studio.

Process: I have implemented the instruction from the SAP note 2853770 to the most part, there are couple of changes that I need to make in order to make it working for HANA cloud. I have mentioned them in the process steps. Also provided more explanation to note instructions.

Step1: Download the Zipfile from the link debugfile-download, then import that into your MTA project. It has to be placed under the src folder of db module. you can also get this zip file from SAP note 2853770 however procedure parameters to be adjusted.

Step2: Modify the hdbgrants file in the debugviewhelper package, replace with CUPS name that you created, after these 2 steps your project will look like this.

Once you made sure all CVs are good, then you can build and deploy the MTA project in Dev and Prod instances of HANA.

Step3: In DB Explorer, Using DBADMIN(in case of HANA cloud) or ADMIN user(HANA on-Premise), Grant the authorization on the procedure below to the user mentioned in CUPS service. Because these procedures are in SYS schema of Classic DB. In order to access them inside container, Grantor service will be granted the access to HDI container.

SYS.CREATE_INTERMEDIATE_CALCULATION_VIEW_DEV

-- Grant the privilege the CUPS user.

GRANT EXECUTE on SYS.CREATE_INTERMEDIATE_CALCULATION_VIEW_DEV to <Cups user> WITH GRANT OPTION;

GRANT EXECUTE on SYS.DROP_INTERMEDIATE_CALCULATION_VIEW_DEV to <UPSuser> WITH GRANT OPTION;

Step4: Now, create a HDI container Admin user. Then Grant Container Admin Privileges to this user.

--Create HDI_ADMIN user

CREATE USER HDI_ADDMIN PASSWORD "APassw0rd" NO FORCE_FIRST_PASSWORD_CHANGE;

-- Grant Container Group Privileges to HDI_ADMIN

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'HDI_ADMIN', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_GROUP_ADMIN_PRIVILEGES;

CALL _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES('BROKER_CG', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #PRIVILEGES;

Note: if you are using HANA on-premise the default container group name is “SYS_XS_HANA_BROKER”, for HANA Cloud it is “BROKER_CG”. You can adjust the script accordingly. Even you create your Container Group as well. To list the existing container groups, you can run the SQL statement below 

-- using DBADMIN or ADMIN user

select * from _SYS_DI.M_ALL_CONTAINER_GROUPS ;

Step5: Log in With HDI_ADMIN user and grant Privileges to FIREFIGHTER user, if you haven’t created a FIRFIGHTER user then create one first with DBADMIN or SYSTEM user as your HDI_ADMIN will not have authorization to create new users.

-- Swith to HDI_ADMIN user log in

-- Replace CONTAINER_SCHEMA name in the CALL Procedure with your DB module schema in mta.yaml file.

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'FIREFIGHTER', 'EXECUTE', 'WRITE' FROM dummy;

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'FIREFIGHTER', 'EXECUTE', 'MAKE' FROM dummy;

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'FIREFIGHTER', 'EXECUTE', 'GRANT_CONTAINER_SCHEMA_PRIVILEGES' FROM dummy;

CALL _SYS_DI#BROKER_CG.GRANT_CONTAINER_API_PRIVILEGES('<CONTAINER_SCHEMA>', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #PRIVILEGES ;

Then Grant access on executeprocedures procedure to FIREFIGHTER user, it allows to create the intermediate nodes for data preview.

and then Grant access on container default role to FIREFIGHTER

-- Grant Execution privilege on Procedure to Firefighter

GRANT <CONTAINER_SCHEMA>."executeProcedures" TO FIREFIGHTER;

-- Grant default Container access role to Firefighter

GRANT "<CONTAINER_SCHEMA>::access_role" TO FIREFIGHTER;

Step6: Now, Log in with FIREFIGHTER user,

call debug view procedure to create intermediate node view, passing parameters are CV name, Node name and the name of the intermediate node that you wanted to generate.

Lastly, you can run the select statement to run the data preview on that node, if you have any input parameters used in the CV, you can adjust the select statement to accommodate them.

-- Calling the debugview procedure, to create intermediate view

call <CONTAINER_SCHEMA>."debugView"('CV_DIM_TEST', 'Projection_1', 'mydebugtest') ;

-- Display data in the intermediate node, 

SELECT * FROM <CONTAINER_SCHEMA>."mydebugtest" ;

Notes:

1. The HDI Admin and firefighter user set up is one time activity, intermediate view generation will be changed every time.

2. for Clean up of the intermediate views, another procedure is available in the same package that is downloaded in step1. for instructions you can follow the SAP note 2853770

3. Update from SAP: Product team is working on getting the data preview on graphical nodes by end of 2022 only for Business Application Studio.