Wednesday, 5 April 2017

Paddling Upstream: How to find all HANA views that use a particular table/column

Requirement:


Find all HANA views that use a particular table/column.

If a field is dropped/modified from a table, identify the upstream model impacts.

Problem Faced:


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.

Current Scenario:


There is no table in HANA which will give this info.

Solution Approach:


The approach is to look for “specific” tags in the repository XML to find if a table/field is used in a model.

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications

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.

Prerequisites:


Select access to table “_SYS_REPO”.”ACTIVE_OBJECT”

Solution:


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.

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications
SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications

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.

CALL "FIELD_DEPENDENCY"('PackageName');

Example:

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

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications
The procedure won’t run until the invalid entries are corrected.

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications
Once the correction is made the Procedure can be run to get the output.

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications

These fields are being used in the below models.

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications

SAP HANA Tutorials, SAP HANA Materials, SAP HANA Guide, SAP HANA Certifications