Paddling Upstream: How to find all HANA views that use a particular table/column
Find all HANA views that use a particular table/column.
If a field is dropped/modified from a table, identify the upstream model impacts.
Our team’s analytics was based on tables from multiple source systems.
There were instances where fields were being dropped from source tables as a part of continuous changes.
These were affecting the HANA models built on top of those tables and there was no direct way to find out which models will be affected if fields from tables were dropped/modified.
This activity had to be done manually, the effort required might depend on how many models/sub models needed checking.
There is no table in HANA which will give this info.
The approach is to look for “specific” tags in the repository XML to find if a table/field is used in a model.
The field is identified irrespective of whether it is propagated to the top most node.
The repository XML is stored in CDATA field of “_SYS_REPO”.”ACTIVE_OBJECT” table.
Select access to table “_SYS_REPO”.”ACTIVE_OBJECT”
This is automated through a procedure.
The steps to create the procedure and associated tables are below.
Run the below code snippet to create the tables and the procedure.
CREATE COLUMN TABLE "FI_INPUT" ("NUM" INTEGER CS_INT PRIMARY KEY, "SCHEMA" NVARCHAR(40), "TABLE" NVARCHAR(40), "FIELD" NVARCHAR(100)) UNLOAD PRIORITY 5 AUTO MERGE ; ALTER TABLE "FI_INPUT" ADD ("FIELD_QUERY" NVARCHAR(200) GENERATED ALWAYS AS '%source="' || UPPER("FIELD") || '"%') ; ALTER TABLE "FI_INPUT" ADD ("TABLE_QUERY" NVARCHAR(200) GENERATED ALWAYS AS '%<input node="#' || UPPER("TABLE") || '%') ; CREATE COLUMN TABLE "FI_OUTPUT" ( "TABLE" NVARCHAR(40) CS_STRING, "FIELD" NVARCHAR(100) CS_STRING, "PACKAGE" NVARCHAR(300) CS_STRING, "OBJECT_NAME" NVARCHAR(300) CS_STRING ) ; CREATE COLUMN TABLE "FI_TEMP" ( "ID" INT CS_INT, "PACKAGE" NVARCHAR(300) CS_STRING, "OBJ_NAME" NVARCHAR(300) CS_STRING, "CDATA" NCLOB MEMORY THRESHOLD 1000 ) ; CREATE PROCEDURE FIELD_DEPENDENCY (IN PACKAGE_NAME NVARCHAR(100)) LANGUAGE SQLSCRIPT AS BEGIN DECLARE I INTEGER ; DECLARE GEN_QUERY NVARCHAR(1000) ; DECLARE COUNTER INTEGER ; DECLARE C1 INTEGER ; DECLARE PK NVARCHAR(100) ; PK := ''''||PACKAGE_NAME||'%''' ; ---Find out Existence of table/field in HANA Schemas T1 = SELECT TABLE, FIELD, CASE WHEN COALESCE(POSITION, 0) = 0 THEN 'WRONG FIELD/TABLE' ELSE 'YES' END AS "FIELD_VALIDITY" FROM FI_INPUT LEFT OUTER JOIN TABLE_COLUMNS ON TABLE = TABLE_NAME AND FIELD = COLUMN_NAME AND SCHEMA = SCHEMA_NAME ; SELECT COUNT(*) INTO C1 FROM :T1 WHERE FIELD_VALIDITY = 'WRONG FIELD/TABLE'; IF C1 > 0 THEN SELECT * FROM :T1 WHERE FIELD_VALIDITY = 'WRONG FIELD/TABLE' ; ELSE DELETE FROM FI_OUTPUT ; DELETE FROM FI_TEMP ; SELECT COUNT(*) INTO COUNTER FROM FI_INPUT ; FOR I IN 1..:COUNTER DO GEN_QUERY = 'INSERT INTO FI_TEMP (SELECT ' ||:I|| ',PACKAGE_ID,OBJECT_NAME ,"CDATA" FROM "_SYS_REPO"."ACTIVE_OBJECT" WHERE ("PACKAGE_ID" LIKE '||:PK||') AND (CDATA LIKE (SELECT FIELD_QUERY FROM FI_INPUT WHERE NUM = '||:I||') AND CDATA LIKE (SELECT TABLE_QUERY FROM FI_INPUT WHERE NUM = '||:I||') ))' ; EXEC (:GEN_QUERY) ; END FOR ; INSERT INTO FI_OUTPUT (SELECT TABLE, FIELD, PACKAGE, OBJ_NAME FROM FI_INPUT INNER JOIN FI_TEMP ON ID = NUM) ; SELECT * FROM FI_OUTPUT ; END IF ; END ;
The Procedure and the below tables will be created once the snippet is run.
Running the Procedure:
The FI_INPUT table needs to be populated with the fields to be identified.
Insert values for the first 4 fields, the last two fields are auto generated, incorporating the XML tag info mentioned earlier.
INSERT INTO "FI_INPUT" VALUES(1,'SCHEMA_NAME','TABLE_NAME','FIELD_NAME');
The procedure only runs for valid Table/fields and will keep displaying the erroneous table/field in the output until corrected.
The procedure takes a package name as input, If a root package is specified then all models under the root/sub-packages will be searched.
INSERT INTO FI_INPUT VALUES(1,'ECC','VBRP','NETWR'); INSERT INTO FI_INPUT VALUES(2,'ECC','VBRK','VBELN'); INSERT INTO FI_INPUT VALUES(3,'ECC','VBRK','ABCD');--THIS IS FOR A TEST
The procedure won’t run until the invalid entries are corrected.
Once the correction is made the Procedure can be run to get the output.
These fields are being used in the below models.
Please let me know if there are any questions.