Introduction:
I worked on a migration project where we are supposed to create a new system and migrate few specific models from one system to another. The challenge faced is to identify dependencies and end-to-end flow of models created in the system.
In HANA, Data is consumed from different systems in the form of different objects.
Several objects consume/ refer calculation views but the end-to-end flow of a model within the system cannot be identified with the existing SAP functionality.
Every time a change has to be made in a calculation view, it’s impact is not identified as it may be used in a stored procedure/ XS job…
Current State:
Existing SAP Functionality can only identify the “where used list” of a Calculation View.
Future State:
A Stored Procedure is created which will identify the end-to-end flow usage of the Calculation Views/ Module.
Migration:
i) A lot of time is saved in manual identification and preparation of the list of dependent objects
ii) Chances of error are eliminated as human intervention is not involved.
Flexible and easily adaptable across any business area/ system.
Every time an enhancement is done, time for analysis will be considerably reduced as the footprint of the model and other affecting areas are easily identified.
The transport sequence of objects can be maintained as the level of dependency is identified.
Chances for the failure of transport will be reduced.
Solution:
◉ A custom solution in the form of Stored Procedure is developed in HANA which will take calculation Views/ Module as input and identify all dependent *modelling objects which are consumed in it as well as the objects which refer to it across the system.
◉ *modelling objects: Calculation views/ SAP tables/ Custom tables/ BW objects/ stored procedures/ xsjob/ XSODATA)
In the above-highlighted part, if I have to make changes in CV1, existing Where-Used-List will only display CV2 but the code provided in this procedure will display the completed end-to-end flow of all the objects from bottom to top captured in the above image.
Steps:
I would like to see the where-used list/ references of CV2 in the system.
In the above program, the CV2 (Level1) consumes model CV1 and CV1 further consumes models CV3 and CV4.
List of objects consumed in CV2 are identified as Level1 and this will be again passed to the select statement and objects in Level2 are identified, this further goes on till Level9 as hardcoded in Line Number 11.
DECLARE count VARCHAR(2):= 9;
In case you wanted to run the procedure for one calculation view, execute the following statement to input the model for which stored procedure needs to be run and remove the comments (–) for Line Number 5.
INSERT INTO “ZTABLES”.”ZINPUT_MODELS” VALUES (‘CV2‘);
In the given code, I am executing the stored procedure for all the models under one package ‘ZSD’.
call “ZTABLES”.”ZTABLES::ZDEPENDENCIES_INPUT_MODELS” (‘ZSD%’);
ZOBJ_DEP_LIST – All the objects with duplicates are captured.
ZOBJ_DUP_DEL_LIST – All the objects with duplicates deleted.
ZOBJ_SP_XSJS_LIST – Captures Stored Procedure, xsjob list along with the CV and ZTABLE for the entered input module.
ZOBJ_WUL_SP_LIST – Where-Used List for the entered input CV’s.
ZINPUT_MODELS – Input calculation views.
Open SQL Console and execute the code provided under ‘Creation of tables’, this will help in the creation of custom tables to capture the end result. Next, create a Stored Procedure and cop+paste the code under ‘Stored Procedure’ and run it.
1. Creation of tables:
CREATE COLUMN TABLE "ZTABLES"."ZOBJ_DUP_DEL_LIST" ("LEVEL" NVARCHAR(256),
"OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECTS" NVARCHAR(256) NOT NULL ,
"PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"OBJECT_SUFFIX" NVARCHAR(255) NOT NULL ,
"BASE_SCHEMA_NAME" NVARCHAR(256),
"BASE_OBJECT_NAME" NVARCHAR(256),
"BASE_OBJECT_TYPE" VARCHAR(32),
"DEPENDENT_SCHEMA_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_TYPE" VARCHAR(32) NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZOBJ_DEP_LIST" ("LEVEL" INTEGER CS_INT,
"OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECTS" NVARCHAR(256) NOT NULL ,
"PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"OBJECT_SUFFIX" NVARCHAR(255) NOT NULL ,
"BASE_SCHEMA_NAME" NVARCHAR(256),
"BASE_OBJECT_NAME" NVARCHAR(256),
"BASE_OBJECT_TYPE" VARCHAR(32),
"DEPENDENT_SCHEMA_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_TYPE" VARCHAR(32) NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZOBJ_SP_XSJS_LIST" ("FROM_PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"FROM_OBJECT_NAME" NVARCHAR(256),
"FROM_OBJECT_SUFFIX" NVARCHAR(256),
"TO_PACKAGE_ID" NVARCHAR(256) NOT NULL ,
"TO_OBJECT_NAME" NVARCHAR(256)) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZOBJ_WUL_SP_LIST" ("TYPE" VARCHAR(32),
"BASE_SCHEMA_NAME" NVARCHAR(256),
"BASE_OBJECT_NAME" NVARCHAR(256),
"BASE_OBJECT_TYPE" VARCHAR(32),
"DEPENDENT_SCHEMA_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_NAME" NVARCHAR(256) NOT NULL ,
"DEPENDENT_OBJECT_TYPE" VARCHAR(32) NOT NULL ) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "ZTABLES"."ZINPUT_MODELS" ("ZINPUT_MODELS" VARCHAR(250)) UNLOAD PRIORITY 5 AUTO MERGE
2. Stored Procedure:
PROCEDURE "ZTABLES"."ZTABLES::ZDEPENDENCIES_INPUT_MODELS" (
--IN count VARCHAR(2),
IN Module VARCHAR(4)
-- Use the below line of code in case single calculation view is to be passed as input
--,IN MODEL TABLE (ZINPUT_MODELS VARCHAR(250))
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS BEGIN
DECLARE count VARCHAR(2):= 9;
DECLARE number VARCHAR(2) := 0;
DECLARE SP_ROW VARCHAR(2) := 0;
DECLARE SP_INPUT NVARCHAR(256);
create local temporary table #mytemp(
RN NVARCHAR(256),
LEVEL INTEGER,
OBJECT_NAME NVARCHAR(256) NOT NULL,
DEPENDENT_OBJECTS NVARCHAR(256) NOT NULL,
PACKAGE_ID NVARCHAR(256) NOT NULL,
OBJECT_SUFFIX NVARCHAR(255) NOT NULL,
BASE_SCHEMA_NAME NVARCHAR(256),
BASE_OBJECT_NAME NVARCHAR(256),
BASE_OBJECT_TYPE VARCHAR(32),
DEPENDENT_SCHEMA_NAME NVARCHAR(256) NOT NULL,
DEPENDENT_OBJECT_NAME NVARCHAR(256) NOT NULL,
DEPENDENT_OBJECT_TYPE VARCHAR(32) NOT NULL
);
create local temporary table #mytemp_SP1(
FROM_OBJECT_NAME NVARCHAR(256)
);
create local temporary table #mytemp_SP2(
ROW_NUMBER INTEGER,
FROM_OBJECT_NAME NVARCHAR(256)
);
create local temporary table #mytemp_xsjs(
PACKAGE_ID NVARCHAR(256),
XSJS_JOB NVARCHAR(256),
OBJECT_SUFFIX NVARCHAR(256),
TO_PACKAGE_ID NVARCHAR(256),
STORED_PROCEDURE NVARCHAR(256)
);
--Deletion of tables before every run
delete from "ZTABLES"."ZOBJ_DEP_LIST";
delete from "ZTABLES"."ZOBJ_DUP_DEL_LIST";
delete from "ZTABLES"."ZOBJ_WUL_SP_LIST";
delete from "ZTABLES"."ZOBJ_SP_XSJS_LIST";
--Deletion of tables before every run
--Fetching the level1 data from standard tables for entered input CV's
INSERT INTO "ZTABLES"."ZOBJ_DEP_LIST"
(
SELECT
od.DEPENDENCY_TYPE AS LEVEL, ao.OBJECT_NAME
, CASE WHEN od.BASE_SCHEMA_NAME = '_SYS_BIC' THEN SUBSTR_AFTER (od.BASE_OBJECT_NAME,'/')
WHEN od.BASE_SCHEMA_NAME = 'ZTABLES' THEN SUBSTR_AFTER (od.BASE_OBJECT_NAME,'::')
ELSE od.BASE_OBJECT_NAME
END AS DEPENDENT_OBJECTS,
ao.PACKAGE_ID,
ao.OBJECT_SUFFIX, od.BASE_SCHEMA_NAME, od.BASE_OBJECT_NAME, od.BASE_OBJECT_TYPE,
od.DEPENDENT_SCHEMA_NAME, od.DEPENDENT_OBJECT_NAME, od.DEPENDENT_OBJECT_TYPE
FROM _SYS_REPO.ACTIVE_OBJECT ao
INNER JOIN OBJECT_DEPENDENCIES od
ON '_SYS_BIC' = od.DEPENDENT_SCHEMA_NAME
AND ao.PACKAGE_ID||'/'||ao.OBJECT_NAME = od.DEPENDENT_OBJECT_NAME
AND 'VIEW' = od.DEPENDENT_OBJECT_TYPE
AND '1' = od.DEPENDENCY_TYPE
WHERE ao.OBJECT_NAME
--WHERE ao.OBJECT_NAME = :MODEL
-- Comment below line and use the above line in case single calculation view is to be passed as input
in (SELECT * FROM "ZTABLES"."ZINPUT_MODELS")
ORDER BY
"LEVEL",
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID",
"OBJECT_SUFFIX",
"BASE_SCHEMA_NAME",
"BASE_OBJECT_NAME",
"BASE_OBJECT_TYPE",
"DEPENDENT_SCHEMA_NAME",
"DEPENDENT_OBJECT_NAME",
"DEPENDENT_OBJECT_TYPE"
ASC
);
--Fetching the level1 data from standard tables for entered input CV's
count := :count - 1;
WHILE :count > 0 DO
count := :count - 1;
number := :number + 1;
--Taking the level1 dataset as the input for level2 and henceforth
INSERT INTO "ZTABLES"."ZOBJ_DEP_LIST"
(
SELECT :number +1 AS LEVEL, ao.OBJECT_NAME
, CASE WHEN od.BASE_SCHEMA_NAME = '_SYS_BIC' THEN SUBSTR_AFTER (od.BASE_OBJECT_NAME,'/')
ELSE od.BASE_OBJECT_NAME
END AS DEPENDENT_OBJECTS,
ao.PACKAGE_ID,
ao.OBJECT_SUFFIX, od.BASE_SCHEMA_NAME, od.BASE_OBJECT_NAME, od.BASE_OBJECT_TYPE,
od.DEPENDENT_SCHEMA_NAME, od.DEPENDENT_OBJECT_NAME, od.DEPENDENT_OBJECT_TYPE
FROM _SYS_REPO.ACTIVE_OBJECT ao
INNER JOIN OBJECT_DEPENDENCIES od
ON '_SYS_BIC' = od.DEPENDENT_SCHEMA_NAME
AND ao.PACKAGE_ID||'/'||ao.OBJECT_NAME = od.DEPENDENT_OBJECT_NAME
AND 'VIEW' = od.DEPENDENT_OBJECT_TYPE
AND '1' = od.DEPENDENCY_TYPE
WHERE ao.OBJECT_NAME in (SELECT DEPENDENT_OBJECTS FROM "ZTABLES"."ZOBJ_DEP_LIST" WHERE LEVEL = :number)
AND ao.OBJECT_SUFFIX = 'calculationview'
ORDER BY
"LEVEL",
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID",
"OBJECT_SUFFIX",
"BASE_SCHEMA_NAME",
"BASE_OBJECT_NAME",
"BASE_OBJECT_TYPE",
"DEPENDENT_SCHEMA_NAME",
"DEPENDENT_OBJECT_NAME",
"DEPENDENT_OBJECT_TYPE"
ASC
);
--Taking the level1 dataset as the input for level2 and henceforth
END WHILE;
--Logic to delete duplicate records
INSERT INTO #mytemp
(
SELECT ROW_NUMBER() OVER (PARTITION BY
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID"
ORDER BY
"LEVEL" DESC
) as RN, *
FROM "ZTABLES"."ZOBJ_DEP_LIST"
);
delete from #mytemp where RN <> 1;
INSERT INTO "ZTABLES"."ZOBJ_DUP_DEL_LIST"
(
SELECT
"LEVEL",
"OBJECT_NAME",
"DEPENDENT_OBJECTS",
"PACKAGE_ID",
"OBJECT_SUFFIX",
"BASE_SCHEMA_NAME",
"BASE_OBJECT_NAME",
"BASE_OBJECT_TYPE",
"DEPENDENT_SCHEMA_NAME",
"DEPENDENT_OBJECT_NAME",
"DEPENDENT_OBJECT_TYPE"
FROM #mytemp order by "LEVEL", "OBJECT_NAME" ASC
);
drop table #mytemp;
--Logic to delete duplicate records
--To fetch Where-Used List for the entered input CV's
INSERT INTO "ZTABLES"."ZOBJ_WUL_SP_LIST"
(
SELECT
'WUL_INPUT_CV',
BASE_SCHEMA_NAME,
BASE_OBJECT_NAME,
BASE_OBJECT_TYPE,
DEPENDENT_SCHEMA_NAME,
DEPENDENT_OBJECT_NAME,
DEPENDENT_OBJECT_TYPE
FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE
BASE_OBJECT_NAME IN(
SELECT distinct OD.PACKAGE_ID||'/'||OD.OBJECT_NAME AS MODEL
FROM "ZTABLES"."ZOBJ_DEP_LIST" OD
INNER JOIN "ZTABLES"."ZINPUT_MODELS" IM
ON OD.OBJECT_NAME = IM.ZINPUT_MODELS
)
AND DEPENDENCY_TYPE = '1'
AND DEPENDENT_OBJECT_NAME in (
SELECT BASE_OBJECT_NAME FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE DEPENDENCY_TYPE = '1'
)
);
--To fetch Where-Used List for the entered input CV's
--To fetch Stored Procedure list for the entered input CV's
INSERT INTO "ZTABLES"."ZOBJ_WUL_SP_LIST"
(
SELECT
'STORED PROCEDURE',
FROM_PACKAGE_ID,
FROM_OBJECT_NAME,
FROM_OBJECT_SUFFIX,
TO_PACKAGE_ID,
TO_OBJECT_NAME,
TO_OBJECT_SUFFIX
FROM _SYS_REPO.active_objectcrossref
WHERE FROM_OBJECT_NAME in (
SELECT FROM_OBJECT_NAME FROM _SYS_REPO.active_objectcrossref
WHERE TO_OBJECT_NAME in (
SELECT distinct DEPENDENT_OBJECTS FROM "ZTABLES"."ZOBJ_DEP_LIST"
WHERE BASE_SCHEMA_NAME = 'ZTABLES'
AND FROM_OBJECT_SUFFIX = 'hdbprocedure'
))
);
--To fetch Stored Procedure list for the entered input CV's
--To fetch Stored Procedure, xsjob list along with the CV and ZTABLE for the entered input module
INSERT INTO "ZTABLES"."ZOBJ_SP_XSJS_LIST"
(
SELECT
FROM_PACKAGE_ID,
FROM_OBJECT_NAME,
FROM_OBJECT_SUFFIX,
TO_PACKAGE_ID,
TO_OBJECT_NAME
FROM _SYS_REPO.active_objectcrossref
WHERE
FROM_OBJECT_SUFFIX like 'hd%'
AND FROM_OBJECT_NAME like :Module
ORDER BY FROM_OBJECT_NAME ASC
);
INSERT INTO "ZTABLES"."ZOBJ_SP_XSJS_LIST"
(
SELECT
FROM_PACKAGE_ID,
FROM_OBJECT_NAME,
FROM_OBJECT_SUFFIX,
TO_PACKAGE_ID,
TO_OBJECT_NAME
FROM _SYS_REPO.active_objectcrossref
WHERE
FROM_OBJECT_SUFFIX like 'xs%'
AND FROM_OBJECT_NAME like :Module
ORDER BY FROM_OBJECT_NAME ASC
);
--To fetch Stored Procedure, xsjob list along with the CV and ZTABLE for the entered input module
--To fetch xsjs list for the entered input module
INSERT INTO #mytemp_SP1
(
SELECT DISTINCT
FROM_OBJECT_NAME
FROM "ZTABLES"."ZOBJ_SP_XSJS_LIST"
);
INSERT INTO #mytemp_SP2
(
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY FROM_OBJECT_NAME) AS ROW_NUMBER,
FROM_OBJECT_NAME
FROM #mytemp_SP1
);
SELECT count(*) into SP_ROW FROM #mytemp_SP2;
WHILE :SP_ROW > 0 DO
SELECT FROM_OBJECT_NAME into SP_INPUT FROM #mytemp_SP2 WHERE ROW_NUMBER = :SP_ROW;
SP_ROW := :SP_ROW - 1;
INSERT INTO #mytemp_xsjs
(
SELECT PACKAGE_ID, OBJECT_NAME, OBJECT_SUFFIX, '', :SP_INPUT FROM _SYS_REPO.ACTIVE_OBJECT
WHERE CONTAINS(CDATA, :SP_INPUT)
ORDER BY OBJECT_NAME
);
END WHILE;
DELETE FROM #mytemp_xsjs WHERE OBJECT_SUFFIX <> 'xsjs';
INSERT INTO "ZTABLES"."ZOBJ_SP_XSJS_LIST" ( SELECT * FROM #mytemp_xsjs);
--To fetch xsjs list for the entered input module
drop table #mytemp_SP1;
drop table #mytemp_SP2;
drop table #mytemp_xsjs;
END;
No comments:
Post a Comment