Skip to Content
Technical Articles
Author's profile photo Sreekanth Surampally

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    –  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:

  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.

Thanks

Sreekanth

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      Sreekanth Surampally

      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 ?
      Regards,

      Michael

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally
      Blog Post Author

      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.

      Author's profile photo Michael Cocquerel
      Michael Cocquerel

      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:

      PROCEDURE "hdi_api.all_hdi::SP_RefreshDataPreviewOnNodesRoles"(
        OUT RETURN_CODE INT,
        OUT REQUEST_ID  BIGINT,
        OUT MESSAGES_ALL    "hdi_api::TT_MESSAGES"
      )
         LANGUAGE SQLSCRIPT
         SQL SECURITY DEFINER
         AS
      BEGIN
      	DECLARE MESSAGES    "hdi_api::TT_MESSAGES";
      	DECLARE VAR_PRIVILEGES "hdi_api::TT_API_PRIVILEGES";
      	DECLARE VAR_T_NO_PARAMETERS TABLE LIKE "__SYS_DI.T_NO_PARAMETERS";
      	DECLARE CURSOR C_APIS FOR 
      		SELECT 'EXECUTE' "PRIVILEGE_NAME", 'MAKE' "OBJECT_NAME" from "SYS.DUMMY"
      		UNION
      		SELECT 'EXECUTE' "PRIVILEGE_NAME", 'WRITE' "OBJECT_NAME" from "SYS.DUMMY"
      		UNION
      		SELECT 'EXECUTE' "PRIVILEGE_NAME", 'GRANT_CONTAINER_SCHEMA_PRIVILEGES' "OBJECT_NAME" from "SYS.DUMMY";
      	DECLARE CURSOR C_PRIVILEGES FOR 
      		SELECT 'EXECUTE' "PRIVILEGE_NAME", 'SYS' "SCHEMA_NAME", 'CREATE_INTERMEDIATE_CALCULATION_VIEW_DEV' "OBJECT_NAME" from "SYS.DUMMY"
      		UNION
      		SELECT 'EXECUTE' "PRIVILEGE_NAME", 'SYS' "SCHEMA_NAME", 'DROP_INTERMEDIATE_CALCULATION_VIEW_DEV' "OBJECT_NAME" from "SYS.DUMMY";
      	DECLARE CURSOR C_COUNTAINERS (v_PRIVILEGE_NAME VARCHAR(256), v_OBJECT_NAME VARCHAR(256)) FOR 
      		SELECT "ROLE_SCHEMA_NAME" FROM "SYS.ROLES" "A"
      		LEFT OUTER JOIN "SYS.GRANTED_PRIVILEGES" "B"
      		ON "A"."ROLE_SCHEMA_NAME"||'#DI'="B"."SCHEMA_NAME"
      		AND "B"."OBJECT_NAME" = :v_OBJECT_NAME
      		AND "A"."ROLE_NAME"="B"."GRANTEE"
      		AND "B"."GRANTEE_SCHEMA_NAME" =  "A"."ROLE_SCHEMA_NAME"
      		AND "B"."PRIVILEGE" = :v_PRIVILEGE_NAME
      		WHERE "B"."PRIVILEGE" IS NULL
      		AND "A"."ROLE_NAME" = 'RO_DataPreviewOnNodes';
      	DECLARE CURSOR C_COUNTAINERS_PRIVILEGE (v_PRIVILEGE_NAME VARCHAR(256), v_SCHEMA_NAME VARCHAR(256), v_OBJECT_NAME VARCHAR(256)) FOR 
      		SELECT "ROLE_SCHEMA_NAME" FROM "SYS.ROLES" "A"
      		LEFT OUTER JOIN "SYS.GRANTED_PRIVILEGES" "B"
      			ON "A"."ROLE_NAME"="B"."GRANTEE"
      			AND "B"."GRANTEE_SCHEMA_NAME" =  "A"."ROLE_SCHEMA_NAME"
      			AND "B"."SCHEMA_NAME" = :v_SCHEMA_NAME
      			AND "B"."OBJECT_NAME" = :v_OBJECT_NAME
      			AND "B"."PRIVILEGE" = :v_PRIVILEGE_NAME
      		WHERE "B"."PRIVILEGE" IS NULL
      			AND "A"."ROLE_NAME" = 'RO_DataPreviewOnNodes';
      	CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE "hdi_api::TT_API_PRIVILEGES";
      	VAR_T_NO_PARAMETERS = select * from "__SYS_DI.T_NO_PARAMETERS";
      	FOR API AS C_APIS DO
      		FOR COUNTAINER AS C_COUNTAINERS(API.PRIVILEGE_NAME, API.OBJECT_NAME) DO
      			INSERT INTO #PRIVILEGES (PRINCIPAL_SCHEMA_NAME,PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT COUNTAINER.ROLE_SCHEMA_NAME, 'RO_DataPreviewOnNodes', API.PRIVILEGE_NAME, API.OBJECT_NAME from "SYS.DUMMY";
      			VAR_PRIVILEGES = select * from  #PRIVILEGES;
      			CALL "__SYS_DI#SYS_XS_HANA_BROKER.GRANT_CONTAINER_API_PRIVILEGES"(COUNTAINER.ROLE_SCHEMA_NAME, :VAR_PRIVILEGES, :VAR_T_NO_PARAMETERS, RETURN_CODE, REQUEST_ID, MESSAGES); 
      			MESSAGES_ALL = select * from :MESSAGES_ALL union all select * from :MESSAGES;
      			TRUNCATE TABLE #PRIVILEGES;
      		END FOR;
      	END FOR;
      	DROP TABLE #PRIVILEGES;
      	IF RETURN_CODE IS NULL THEN 
      		RETURN_CODE=0;
      		REQUEST_ID=0;
      	END IF;
      	FOR V_PRIVILEGE AS C_PRIVILEGES DO
      		FOR V_COUNTAINER_PRIVILEGE AS C_COUNTAINERS_PRIVILEGE(V_PRIVILEGE.PRIVILEGE_NAME, V_PRIVILEGE.SCHEMA_NAME, V_PRIVILEGE.OBJECT_NAME) DO
      			EXECUTE IMMEDIATE 'GRANT ' || V_PRIVILEGE.PRIVILEGE_NAME || ' ON ' ||  V_PRIVILEGE.SCHEMA_NAME || '.' || V_PRIVILEGE.OBJECT_NAME || ' TO "' || V_COUNTAINER_PRIVILEGE.ROLE_SCHEMA_NAME || '"."RO_DataPreviewOnNodes";';
      		END FOR;
      	END FOR;
      END
      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally
      Blog Post Author

      Thanks Michael, This is very helpful. I will go through and see if we can leverage it our system.