Skip to Content

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.

doc_03.JPG

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. 🙂

/*

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

—  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;

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Martin Fei

    Hi Ravindra,

    Nice script. A couple of remarks:

    Column FROM_OBJECT_SUFFIX does not exist anymore. Do you mean column FROM_OBJECT_TYPE_ID?

    Also, names quickly become very long with package hierarchy and generated views like …/hier/… for each field. Therefore, I recommend increasing column sizes of object name and comment to 300. Maximum length of object names is 256.

    Now all I need is to inverse the logic to actually walk down the reference tree from the top view.

    Best regards,

    Martin

    (1) 
  2. Amaury Van Espen

    Thank you Martin, Ravindra,

    it is a very interesting script .

    How could we implement it into Eclipse ? or maybe directly suggest to add it into SAP Tables ?

    Best

    Amaury

    (0) 

Leave a Reply