Skip to Content

Hierarchy visualization of objects

Ever got into a situation where you had to list out child workflows and dataflows of a job? May be for documenting, or may be for checking object usages, Real challenge is when you have to represent it in organizational chart.

Here is an example:


For better view – click on the image, on the preview window, right click and save to desktop.

/wp-content/uploads/2015/08/data1_770943.png

How do we do?

Of-course you can drill down every object in designer, navigate and draw chart in Microsoft Visio, MS-Word etc. But, how about generating hierarchical chart from repository metadata rather than drawing it?


Its just three steps away

1. Start by populating AL_PARENT_CHILD table, from designer or from command line,

          a. From designer:

Untitled2.png

          b. From command line using al_engine.exe

          command: “%LINK_DIR%\bin\al_engine.exe” -NMicrosoft_SQL_Server -StestSQLhost -Udb_user -Pdb_pass -Qtestdb -ep

Untitled2.png

2. Login to repository database and execute the below query:

WITH CTE AS(

      SELECT [PARENT_OBJ]

      ,[PARENT_OBJ_TYPE]

      ,[DESCEN_OBJ]

      ,[DESCEN_OBJ_TYPE] FROM [AL_PARENT_CHILD] PC

      WHERE ‘JOB_CORD_BW_TD_OHS_POPULATE_SDL’ = PARENT_OBJ

      UNION ALL SELECT PC.[PARENT_OBJ]

     ,PC.[PARENT_OBJ_TYPE]

      ,PC.[DESCEN_OBJ]

      ,PC.[DESCEN_OBJ_TYPE] FROM CTE INNER JOIN [AL_PARENT_CHILD] PC ON CTE.DESCEN_OBJ=PC.PARENT_OBJ

) SELECT 2 AS ID,[PARENT_OBJ] + ‘->’ + [DESCEN_OBJ] CODE FROM CTE

WHERE PARENT_OBJ_TYPE IN (‘Job’,‘WorkFlow’,‘DataFlow’)

AND [DESCEN_OBJ_TYPE] IN (‘Job’,‘WorkFlow’,‘DataFlow’) UNION

SELECT 1 AS ID, ‘digraph a { node [shape=rectangle]’ UNION

SELECT 3 AS ID, ‘}’

Untitled1.png

From the output, copy only the contents in second column without header.

My data looks like this:

digraph a { node [shape=rectangle]
JOB_CORD_BW_TD_OHS_POPULATE_SDL->WF_Job_Workflow_SSP_Container_CORD_BW_TD_OHS__SDL
WF_CORD_BW_TD_OHS_BW_To_Z_BODS_01_SDL->DF_BW_Z_BODS_01_STG_TO_BW_Z_BODS_01_SDA
WF_CORD_BW_TD_OHS_BW_To_Z_BODS_01_SDL->DF_S_BW_Z_BODS_01_to_SDA_BW_Z_BODS_01_SDL_1
WF_CORD_BW_TD_OHS_BW_To_Z_BODS_01_SDL->DF_S_BW_Z_BODS_01_to_SDA_BW_Z_BODS_01_SDL_2
WF_CORD_BW_TD_OHS_BW_Z_BODS_02_SDL->DF_BW_Z_BODS_02_STG_TO_BW_Z_BODS_02_SDA
WF_CORD_BW_TD_OHS_BW_Z_BODS_02_SDL->DF_S_BW_Z_BODS_02_to_SDA_BW_Z_BODS_02_SDL_01
WF_CORD_BW_TD_OHS_BW_Z_BODS_02_SDL->DF_S_BW_Z_BODS_02_to_SDA_BW_Z_BODS_02_SDL_02
WF_CORD_BW_TD_OHS_POPULATE_S_BW_Z_BODS_01_SDL->WF_CORD_BW_TD_OHS_BW_To_Z_BODS_01_SDL
WF_CORD_BW_TD_OHS_POPULATE_S_BW_Z_BODS_02_SDL->WF_CORD_BW_TD_OHS_BW_Z_BODS_02_SDL
WF_Job_Workflow_SSP_Container_CORD_BW_TD_OHS__SDL->WF_Job_Workflow_SSP_Group_CORD_BW_TD_OHS_SDL
WF_Job_Workflow_SSP_Group_CORD_BW_TD_OHS_SDL->WF_CORD_BW_TD_OHS_POPULATE_S_BW_Z_BODS_01_SDL
WF_Job_Workflow_SSP_Group_CORD_BW_TD_OHS_SDL->WF_CORD_BW_TD_OHS_POPULATE_S_BW_Z_BODS_02_SDL
WF_Job_Workflow_SSP_Group_CORD_BW_TD_OHS_SDL->WF_Master_Workflow_Staging_CORD_BW_TD_OHS_SDL
WF_Master_Workflow_Staging_CORD_BW_TD_OHS_SDL->WF_Staging_Workflow_Container_CORD_BW_TD_OHS_SDL
WF_Staging_Workflow_Container_CORD_BW_TD_OHS_SDL->WF_Staging_Z_BODS_01_to_S_BW_Z_BODS_01_SDL
WF_Staging_Workflow_Container_CORD_BW_TD_OHS_SDL->WF_Staging_Z_BODS_02_to_S_BW_Z_BODS_02_SDL
WF_Staging_Z_BODS_01_to_S_BW_Z_BODS_01_SDL->DF_OH_Src_Z_BODS_01_To_Stg_S_BW_Z_BODS_01_Map_SDL
WF_Staging_Z_BODS_02_to_S_BW_Z_BODS_02_SDL->DF_OH_Src_Z_BODS_02_To_Stg_S_BW_Z_BODS_02_Map_SDL
}

3. Open the webpage webgraphviz

  1. Clear the existing contents in text box
  2. Past the code you copied
  3. Click generate graph button and scroll down to see the generated graph.


That’s all, Org chart of your job is ready !


Note:

  1. The SQL Query
    1. Given query works only on MS-SQL Server.
    2. Modify the WHERE clause in the query to match with your job.
    3. You can also use “IN” instead of “=” and put multiple job names.
    4. Query is restricted only to job, workflow & dataflow. You can modify the conditions to include other objects too.
  2. We are generating only parent child hierarchy, not the execution flow. i.e. two child node at same level may not execute in parallel.
  3. Since its not the execution flow, conditional workflows will not appear in chart.
  4. Webgraphviz is alternate for Graphviz tool which supports command line usage when installed.

Appreciate your comments/feedback. Cheers

Be the first to leave a comment
You must be Logged on to comment or reply to a post.