Visualizing Critical Path with Workflow Visio(n)

Two weeks ago, Joel Wiesmann introduced his Tool Workflow Visio(n) for the visualization of workflows here in this blog.

When I first heard about this tool, I was quite enthusiastic about it. Joel demonstrated it to me personally and had me convinced of its usefulness right away.

That made me think immediately about another user application: The visualization of the critical path. I’ve dealt with this subject previously in 2014 in my former AutomicBlog article on how to find the critical path of a workflow via SQL.

Below a brief summary of how that works, and how to visualize the critical path with Joel’s Visio Tool.

The Critical Path of a Workflow

Normally, a workflow has several sequences of tasks. For example, in this screenshot you can see a simple workflow with three paths.

The paths are:

• 1 -> 2 -> 5 > 6
• 1 -> 3 -> 6
• 1 -> 4 -> 6

Most of the time, the path with the longest runtime is referred to as the critical path. For a workflow object, this means: The path whose tasks add up to the longest estimated runtime.

So, which path in this example is the critical path?

To find out, you need first of all a recursive database query. It finds the paths of a workflow and already contains the estimated runtime of all tasks.

Pay attention when copying the code: You need to add the client number and the name of your workflow on lines 15 and 16.

WITH wpath (Client, Wflowname, Idnr, Tasknr, path, ertsum) as (
  --Anchor -> tasks without predecessor
  select OH_Client, OH_Name, OH_Idnr, JPP_Lnr, 
  cast (JPP_Lnr as VARCHAR2(200)),  --T-SQL: CAST(JPP_LNR as VARCHAR(MAX))
  case
    when JPP_Otype = ''
     then 0
    else
      coalesce (
        (select b.OH_ERT from OH b where a.OH_CLIENT=b.OH_Client and b.OH_NAme = JPP_Object), 0
      )
  end
  from OH a
  inner join JPP on OH_Idnr = JPP_OH_IDNr
  where OH_CLIENT = 1000    -- !! YOUR CLIENT !! 
  and OH_NAME = 'JOBP.002'  -- !! YOUR WFLOW NAME !!
  --only tasks without predecessor
  and not exists (
    select 1
    from JPPA where jppa_oh_idnr = OH_Idnr and JPP_Lnr = jppa_jpp_lnr
  )
  UNION ALL
  select Client, Wflowname, Idnr, JPP_Lnr, 
  path || '.' || JPP_Lnr,
  ertsum + coalesce (
        (select b.OH_ERT from OH b where Client=b.OH_Client and b.OH_NAme = JPP_Object), 0)
  from wpath
  inner join JPPA on IDNR = JPPA_OH_Idnr and JPPA_PreLnr = Tasknr
  inner join JPP on IDNR = JPP_OH_IDNR and JPPA_JPP_Lnr = JPP_Lnr
)
select Client, Wflowname, Path, Ertsum 
from wpath a
--eliminate sub-paths 
where not exists ( 
  select 1
  from wpath b where b.path like a.path || '.%'
)

I’m not going to deal with the script here. If you have questions, just send me a comment. In any case, the query results in a table with all paths of the workflow. It lists the client, the name of the workflow, the path and the sum of the expected runtime.
For the example shown in the above screenshot, the table looks as follows:

Therefore, the critical path is quite clear 1→2→5→6.

Complex Workflows Can be Confusing

For small workflows, as shown in the above example, a separate visualization of the critical path is hardly worthwhile. There are not many paths, and everything is quite easily seen.

It looks quite different when we are dealing with complex workflows.

The following workflow is also still quite benign, but it already has 43 different paths. Count them if you like.

With the above script, you can discover the critical path. The path is 1.6.7.8.10.11.16.17.22 .

Can you see it as first glance? In this case, a visualization makes a lot more sense and prevents your having to search each time.

Illustration with the Visio Tool

Joel has already explained everything in detail. His tool needs two files as input.

• csvRelDataFile
  A CSV file with one entry per relation between two tasks. Workflow Visio() makes available SQL scripts for creating this file for a few typical user applications.
• csvTaskDataFile
  A CSV file with one entry per workflow task. This file must contain at least the lnr, the type, and the X and Y coordinates for each task. Additional fields are optionally possible; for example, name, description or highlight.

You can find detailed information in Joel’s guest article.

For my example, I simply created the csvRelDataFile with the available script oracle_nonrecursive_oh-rel.sql. The table has 32 lines, one for each relation between two tasks. I named the file criticalpath_rel.csv.

No template exists for the csvTaskDataFile, and I had to write it myself. Joel’s example HeatData (oracle_nonrecursive_oh-task_default_or_heatmap.sql) already contains many useful fields and is a good starting point. It contains the following fields:

LNR: The JPP_LNR of the task within the workflow

OBJECTNAME: Name of the Object

NAME: Alias name des Object in case one has been defined. Otherwise the Object Name

TYPE: Object Type

Y: Y-Coordinate for the Position within the Workflow

X: X-Coordinate for the Position within the Workflow

ACTIVE: Is the Task in the Workflow active?

DESCRIPTION: The Object Title as a description

ERT: The “Estimated Runtime” of the Object in seconds (OH_ERT)

AGENT: Agent on which the job runs (i.e., Src and Dst Agent for Filetransfers)

HEATDATA: The “Estimated Runtime” of the Object in the “HH:MM:SS” format

HEAT: A percentage value calculated in HEATDATA for the Heatmap Illustration with traffic lights. The lower the value, the greener the task illustration. The higher the value, the redder the task illustration.

All this is already quite useful, but for us the most important information is missing: Does the task belong in the critical path? Therefore, I have adapted the script and included my above-mentioned query. The result now shows an additional field:

HIGHLIGHT: Possible values: 0 or 1. Tasks with “1” are emphasized, i.e., outlined in red, tasks with “0” are not.

Highlight then has the value of 1 if the task is in the critical path.
Here is my adapted script (for Oracle only. Sorry!).

with tasksErt (WflowIdnr, Client, WflowName, Lnr, objectName, Name, Type, Y, X, Active, Description, Ert, Agent) as ( 
  select
    wflow.OH_Idnr, wflow.OH_Client, wflow.OH_Name,
    JPP_Lnr, JPP_Object, coalesce(JPP_Alias, JPP_Object), JPP_OType,
    JPP_Row, JPP_Col, JPP_Active, tasks.OH_Title, tasks.OH_Ert as Ert,
    CASE 
      -- You can add the LoginDst / LoginSrc as well
      WHEN tasks.OH_OTYPE = 'JOBS' THEN (select JBA_HostDst from JBA where JBA_OH_Idnr = tasks.OH_idnr)
      WHEN tasks.OH_OTYPE = 'JOBF' THEN (select JFA_HostSrc || '=>' || JFA_HostDst from JFA where JFA_OH_Idnr = tasks.OH_idnr)
    -- EVNT is missing here
    ELSE 'n/a'
    END 
  from jpp
  inner join OH wflow on JPP_OH_Idnr = wflow.OH_Idnr
  left join OH tasks on tasks.OH_Name = JPP_Object and tasks.OH_Client = wflow.OH_Client and tasks.OH_Otype = JPP_Otype --ignore 
  where wflow.OH_Client = 1000
  and wflow.OH_Name = 'JOBP_CRITICALPATH_DEMO'
),
taskswithheat as (
  select 
    tasksErt.*,
    CASE WHEN ERT IS NOT NULL THEN TO_CHAR(TRUNC(ert/3600),'FM9900') || ':' || TO_CHAR(TRUNC(MOD(ert,3600)/60),'FM00') || ':' || TO_CHAR(MOD(ert,60),'FM00') END as heatData,
    round((ert - (select min(Ert) from tasksErt)) / (select max(Ert) from tasksErt) * 100, 0) as heat
  from tasksErt
),
wpath (Client, Wflowname, Idnr, Tasknr, path, ertsum) as (
  select Client, Wflowname, Wflowidnr, Lnr, 
  cast (Lnr as VARCHAR2(200)),  --T-SQL: CAST(JPP_LNR as VARCHAR(MAX))
  case
    when Type = ''
     then 0
    else
      coalesce (ERT, 0)
  end
  from taskswithheat
  --only tasks without predecessor
  where not exists (
    select 1
    from JPPA where jppa_oh_idnr = WFLowIdnr and Lnr = jppa_jpp_lnr
  )
  UNION ALL
  select Client, Wflowname, Idnr, JPP_Lnr, 
  path || '.' || JPP_Lnr,
  ertsum + coalesce (
        (select b.OH_ERT from OH b where Client=b.OH_Client and b.OH_NAme = JPP_Object), 0)
  from wpath
  inner join JPPA on IDNR = JPPA_OH_Idnr and JPPA_PreLnr = Tasknr
  inner join JPP on IDNR = JPP_OH_IDNR and JPPA_JPP_Lnr = JPP_Lnr
), 
maxpath as (
  select path, ertsum 
  from wpath a
  --eliminate sub-paths 
  where not exists ( 
    select 1
    from wpath b where b.path like a.path || '.%'
  )
  and ertsum = (
      select max(b.ertsum)
      from wpath b
    )
)
select LNR, OBJECTNAME, NAME, TYPE, Y, X, ACTIVE, DESCRIPTION, ERT, AGENT, HEATDATA, HEAT, 
case
  when exists (
    select 1 from maxpath where regexp_like('.' || path || '.', '\.' || lnr || '\.')
  ) then 1
  else 0
  end as HIGHLIGHT
from taskswithheat;

I save the result in a file called criticalpath_task.csv. This gives me all needed files, and I can activate WorkflowVision(s):

powershell.exe -ExecutionPolicy Bypass .\workflowVision.ps1 -csvTaskDataFile .\criticalpath_task.csv -csvRelDataFile .\criticalpath_rel.csv -outputFile .\criticalpath.svg

As you can see, I have chosen SVG as an output format.

The Heatmap colors the tasks in accordance with their estimated runtime. The “redder” a task, the longer it runs.

Through my script adaptation all tasks in the critical path are outlined in red. Useful, isn’t it?

Are you using Workflow Visio(n) yet?

I think Joel’s tool is simply cool. And it’s great that he invested the effort to publish it and make it available at no charge. Thanks so much, Joel!

Have you used Workflow Visio(n) yet, or do you have a fantastic idea of what else can be done with it? Just email me or send me a comment. I’ll be happy to introduce your idea here in this blog.