Hierarchy visualization of objects

[chethan_lingaraju]

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.

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:

          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

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, '}'

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