Extended Where-Used search for HANA Information model objects

[Ravi_Channe]

While working on HANA development activities, many a times an object needs to be modified. It is also required to find out which all objects might get impacted due to the given object change. HANA studio provides very easy way to identify the directly impacted objects using "Where-Used" functionality.

The Where-Used window can be invoked by right click on the HANA Information model object.

The window can also be opened from the Menu option Window -> Show View -> Other -> Modeler -> Where-Used List

During development life cycle the information might be required to identify which objects might get impacted due to the change to the:

• Given Information model object - identify First level objects
• First Level objects - identify Second level and then to the Third level and so on.
• Table in a schema

Currently the Multi-level Object impact or impact due to the change to a Table is not directly visible.

The change to the table is more relevant when BW objects are exposed as HANA Information models and it is required to identify the impacted objects due to a change in InfoObject ( P Table / T Table) or DSO Active table.

Please find below a piece of code which provides a simple way to quickly identify the impacted objects.

Please feel free to comment / help enhance the code.

Any other easier way to find the information is most welcome. :smile:

/*

  ***********************************************************************************

--  Custom Program: To identify Multi-Level Where-Used List

--  Developed By: Ravindra Channe

--  Version: 1.0

--  Date: 05-Mar-2013

--  Version: 1.1

--  Edited: 27-Dec-2013

--  Modification: Increase object name column size to 256.

--  Usage: call PR_R_WHERE_USED('<object_name>');

--         <object_name> = Name of Table, Attribute View, Analytic View, Calc View

--  Tables used:

--    Custom Table: lt_obj_ref

--    System Table: _SYS_REPO.active_objectcrossref

--  Pre-requisit: SELECT Access to _SYS_REPO objects, especially active_objectcrossref

  ***********************************************************************************

*/

CREATE COLUMN TABLE lt_obj_ref (V_OBJ_NAME varchar(256), V_COMMENTS varchar(300), V_SEARCHED varchar(1));

-- V_SEARCHED flag values: n = New, s = Search, c = Search Completed

DROP procedure PR_R_WHERE_USED;

-- Please ignore any error if the Procedure doesn't exist

CREATE PROCEDURE PR_R_WHERE_USED (in  vi_obj_name  varchar(256))

LANGUAGE SQLSCRIPT AS

   vl_cnt       integer := 1;

   vl_level     integer := 1;

BEGIN

-- Empty the table for the new search

   delete from lt_obj_ref;

-- Insert data for the first level objects

   INSERT INTO lt_obj_ref (V_OBJ_NAME, V_COMMENTS, V_SEARCHED)

   SELECT distinct FROM_OBJECT_NAME, 'Object "'||:vi_obj_name||'" found in '||FROM_OBJECT_SUFFIX||' in object "'||FROM_PACKAGE_ID||'\'||FROM_OBJECT_NAME||'" at level '||:vl_level, 'n'

   FROM _SYS_REPO.active_objectcrossref

   WHERE to_object_name = :vi_obj_name;

-- Check if there are any objects to search 

   SELECT count(*) into vl_cnt FROM lt_obj_ref WHERE V_SEARCHED = 'n';

   WHILE :vl_cnt > 0 DO

      vl_level := :vl_level + 1;

--    Set the objects to Search from New

      update lt_obj_ref set V_SEARCHED = 's' where V_SEARCHED = 'n';

      insert into lt_obj_ref (V_OBJ_NAME, V_COMMENTS, V_SEARCHED)

      select distinct a.FROM_OBJECT_NAME, 'Object "'||b.V_OBJ_NAME||'" found in '||a.FROM_OBJECT_SUFFIX||' in object "'||a.FROM_PACKAGE_ID||'\'||a.FROM_OBJECT_NAME||'" at level '||:vl_level, 'n'

      from _SYS_REPO.active_objectcrossref a, lt_obj_ref b

      where to_object_name = b.V_OBJ_NAME and b.V_SEARCHED = 's';

--    Set the objects to Search Completed from Search

      update lt_obj_ref set V_SEARCHED = 'c' where V_SEARCHED = 's';

--    Check if there are any objects to search 

      select count(*) into vl_cnt from lt_obj_ref where V_SEARCHED = 'n';

   END WHILE;

END;

-- Check the data from Where-Used Extended Search

SELECT V_COMMENTS FROM lt_obj_ref;