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.
-- 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 ;-- 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.
-- 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;-- 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
- Update from SAP: Product team is working on getting the data preview on graphical nodes by end of 2022 only for Business Application Studio.
