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.
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.
- 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.
Regarding the announcement that the data preview on graphical nodes will be available on Business Application Studio by end of 2022, will it be on development system only like for WebIDE ?
Hi Michael, using BAS, even now also we can target our MTA project to QA or Prod space or system and get the data preview on CV nodes. However, if we do that, there is a risk in getting these objects changed manually in QA/Prod. So Current PoC that SAP dev team mentioned is to achieve the data preview in QA/Prod by not allowing the object changes.
Going via Firefighter process is fine for production system but for Quality system, it is an heavy process as developers may need such privileges in their daily activity.
Also, in my company, we have setup an alternative solution that consists on creating a RO_DataPreviewOnNodes empty role in each hdi containers. There is then a regular job running in quality only that grants the required privileges to those role. This way, developers can get required authorizations only for the containers they are responsible for and not for all containers as it would be the case with Firefighter.
Here is the coding of the procedure doing the job:
Thanks Michael, This is very helpful. I will go through and see if we can leverage it our system.
Hi Sreekanth Surampally,
Thanks a lot for sharing this awesome workaround, I would like to ask if you have any update about the 3rd note:
"Update from SAP: Product team is working on getting the data preview on graphical nodes by end of 2022 only for Business Application Studio"
Do you know the current status?
Hi, there is an update on this topic and it was explained in the Jan's blog below.