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 UPS name that you created to access classic database.
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 UPS 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 - for creation of debug view
SYS.DROP_INTERMEDIATE_CALCULATION_VIEW_DEV - for deletion of debug view
-- 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" ;
Clean up: These intermediate views are created in your container Schema as column views(run time only), you don't need them in there for future reference. So you will have to clean them by calling the DROP procedure as below.
call <CONTAINER_SCHEMA>."dropDebugView"('mydebugtest') ;
this activity can be done on a weekly on monthly basis.
Notes:
- The HDI Admin and firefighter user set up is one time activity, intermediate view generation will be changed every time.
- 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.
Alternate option (Available only on hana cloud with Business Application studio):
You can separate the access of deployment and data preview of the intermediate nodes, with that Admin user who has the access to data preview can perform node level data preview however, objects can be changed/deployed with the user. So, in BAS you will see an option to run data preview with other DB user as shown below.
Thanks
Sreekanth