- … remaining<\/em> durations<\/li>
- … remaining<\/em> work<\/li>
- … criticality based on remaining<\/em> duration<\/li><\/ul>\n\n\n\n
Before reading this blog post, you need to read the blog post Colored Network Diagrams (Part 1)<\/a> first to fully understand the context.<\/p>\n\n\n\n
Preconditions<\/h2>\n\n\n\n
In order to use the approach described here, you should:<\/p>\n\n\n\n
- … have access to a Linux machine or account<\/li>
- … have a MySQL<\/a> or MariaDB<\/a> database server set up, running, and have access to it<\/li>
- … have the package graphviz<\/a> [<\/span>2<\/a>]<\/span> installed<\/li>
- … have some basic knowledge of how to operate in a Linux environment and some basic understanding of shell scripts<\/li><\/ul>\n\n\n\n
Description and Usage<\/h2>\n\n\n\n
In the previous blog post Colored Network Diagrams (Part 1)<\/a> we looked at a method on how to create colored network graphs (PERT charts<\/strong>) based on the duration, the work, the slack, and the criticality of the individual tasks. We also looked at how a network graph looks like when some of the tasks have already been completed (tasks in green<\/span> color). We will now enhance the script sets in order to process remaining duration<\/strong> and remaining work<\/strong>. In addition, this information shall also be displayed for each task. For this, the scripts create_project_db.sql<\/strong>, msp2mysql.sh<\/strong>, and mysql2dot.sh<\/strong> require modifications so that they can process the additional information. I decided to name the new scripts with a “-v2” suffix so that they can be distinguished from the ones in the previous blog post.<\/p>\n\n\n\n
Let us first look at the script create_project_db-v2.sql<\/strong> which sets up the database [1<\/a>]. Compared to the previous version, it now sets up some new fields which have been highlighted in red<\/span> color below. For the sake of readability, only parts of the script have been displayed here.<\/p>\n\n\n\n
...\n task_duration INT UNSIGNED DEFAULT 0,\\\n task_work INT UNSIGNED DEFAULT 0,\\\n free_slack INT DEFAULT 0,\\\n total_slack INT DEFAULT 0,\\\n finish_slack INT DEFAULT 0,\\\n rem_duration INT UNSIGNED DEFAULT 0,\\\n rem_work INT UNSIGNED DEFAULT 0,\\\n<\/span>`%_complete`<\/code><\/code> SMALLINT UNSIGNED DEFAULT 0,\\\n...\n successor SMALLINT UNSIGNED NOT NULL,\n link_lag INT DEFAULT 0)<\/span> ENGINE=MyISAM DEFAULT CHARSET=utf8;\n...<\/pre>\n\n\n\nIn addition to the fields rem_duration<\/strong> and rem_work<\/strong>, we also prepare the database for 3 out of the 4 slack types that tasks have (free_slack<\/strong>, total_slack<\/strong>, finish_slack<\/strong>), in order to be able to execute some experiments in future releases. Furthermore, we can now capture the link_lag<\/strong> between two linked tasks; this information will not be used now, but in a future version of the script set.<\/p>\n\n\n\n
Step 1: Parsing the Project Plan<\/h3>\n\n\n\n
The script msp2mysql-v2.sh<\/strong><\/a> parses the Microsoft\u00ae<\/sup> Project<\/strong> plan in XML<\/strong> format. It contains a finite-state machine<\/a> (see graph below), and the transitions between the states are XML tags (opening ones and closing ones).<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/msp2mysql-v2-451x1024.png\")
Finite-State Machine (v2) parsing the Project Plan<\/figcaption><\/figure>\n\n\n\n Step 2: Generating a Graph<\/h3>\n\n\n\n
Now we can extract meaningful data from the database and transform this into a colored network graph; this is done with the bash script mysql2dot-v2.<\/strong><\/a>sh<\/a><\/strong>. mysql2dot-v2.<\/strong><\/a>sh<\/a><\/strong> can create colored network graphs according to:<\/p>\n\n\n\n
- … the task duration<\/li>
- … the work allocated to a task<\/li>
- … the total slack of a task<\/li>
- … the criticality of a task<\/li>
- … the remaining task duration<\/li>
- … the remaining work allocated to a task<\/li>
- … the remaining criticality of a task<\/span><\/li><\/ul>\n\n\n\n
mysql2dot-v2.<\/a>sh<\/a> relies on a working installation of the graphviz<\/a> package, and in fact, it is graphviz<\/a> and its collection of tools that create the graph, while mysql2dot-v2.<\/a>sh<\/a><\/a> creates a script for graphviz<\/a> in the dot<\/a><\/em> language. Let us look close to some examples:<\/p>\n\n\n\n
Example 8: A real-world example<\/h2>\n\n\n\n
Example 8 was already mentioned in the blog post Colored Network Diagrams (Part 1)<\/a>. It is a fictious simplified project plan with development, verification, and validation activities where some tasks have already been completed. The green<\/span> vertical line in the plan shows the status date.<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-1024x724.jpg\")
Example of a Development Project with<\/strong> completed tasks (Status Date 1<\/strong>)<\/figcaption><\/figure>\n\n\n\n Tasks that have been completed 100% are colored in green<\/span> color across all network graphs indicating that these tasks do not require any more attention in the remaining project. While the network graphs had already been shown in the blog post Colored Network Diagrams (Part 1)<\/a>, they shall nevertheless ben shown again as the new script mysql2dot-v2.<\/a>sh<\/a> shows additional information (remaining duration<\/strong>, remaining work<\/strong>) for each task. For completed tasks, those values as well as total task slack are 0, of course.<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-duration-2-1024x216.png\")
Network Graph colored according to task duration (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-work-2-1024x216.png\")
Network Graph colored according to task work (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-slack-2-1024x216.png\")
Network Graph colored according to task slack (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-criticality-2-1024x216.png\")
Network Graph colored according to task criticality (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n The new script mysql2dot-v2.<\/a>sh<\/a> offers 3 additional graphs (menu choices 5…7):<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Shell-1.jpg\")
Script mysql2dot-v2.<\/a>sh<\/a> executed in a shell<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-remaining-duration-1-1024x216.png\")
Network Graph colored according to remaining<\/em> task duration (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-remaining-work-1-1024x216.png\")
Network Graph colored according to remaining<\/em> task work (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_8-remaining-criticality-1-1024x216.png\")
Network Graph colored according to remaining<\/em> task criticality (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n Let us have a closer look at these graphs and compare some of them:<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Color-Shift-8-8-Duration-1024x465.jpg\")
Network Graphs colored according to task duration [left] and remaining<\/em> task duration [right] (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n The comparison of the graphs above show two tasks whose colors have changed in different directions of color shades:<\/p>\n\n\n\n
- The task with UID=116<\/strong> changes to a lighter color shade. This is because the left graph takes the duration into account (D=72000), and the right graph takes the remaining<\/em> duration into account (rD=36000). As the remaining<\/em> duration is less than the duration, we can expect that change in the color shade.<\/li>
- The task with UID=92<\/strong> changes to a darker color shade although the values for the duration (D=14400) and the remaining<\/em> duration (rD=14400) are equal. Why is that?
The explanation is that the spread between the color shades (the variable ${color_step}<\/strong> in the script mysql2dot-v2.sh<\/a>) is computed based on the maximum remaining<\/em> duration<\/strong> in the graph on the right side, different from the maximum duration<\/strong> in the graph on the left side. Consequently, if there is a task with a very long duration in the left graph and this task has a significantly lower remaining duration, this might occur in a smaller spread (a smaller value of ${color_step}<\/strong>) when the network graph for the remaining<\/em> duration is computed.<\/li><\/ol>\n\n\n\nI am personally open to the discussion of whether a different spread as seen in example (2) makes sense or not. For the moment, I decided to proceed like that. But there are valid arguments for both approaches:<\/p>\n\n\n\n
- Leaving the spread equal between both graphs makes them directly comparable 1:1. This might be important for users who regularly compare the network graph colored according to task duration with the one colored according to remaining<\/em> task duration.<\/li>
- Computing the spread from scratch for each graph sets a clear focus on the tasks which should be observed now because they have a long remaining<\/em> duration, irrespective of whether the tasks once in the past had a longer or shorter duration.<\/li><\/ul>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Color-Shift-8-8-Work-1024x457.jpg\")
Network Graphs colored according to task work [left] and remaining<\/em> task work [right] (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n The shift of color shades in the graph above seems logical and corresponds to the case (1) of the previous example. For both the tasks with UID=100<\/strong> and UID=116<\/strong>, the value of the remaining<\/em> task work is half the value of the task work, and therefore, in the network graph for the remaining<\/em> task work, both tasks feature lighter color shades as they are “less problematic”.<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Color-Shift-8-8-Criticality-1024x455.jpg\")
Network Graphs colored according to task criticality [left] and remaining<\/em> task criticality [right] (Example 8-v2)<\/figcaption><\/figure>\n\n\n\n As already explained in the blog post Colored Network Diagrams (Part 1)<\/a>, criticality is computed based on the information of task slack<\/strong> and task duration<\/strong>. Whereas the script mysql2dot.sh<\/a> used the task finish slack<\/strong> for this calculations, I decided to change to task total slack<\/strong> in the newer script mysql2dot-v2.sh<\/a>; that seemed to be more adequate although in my examples, both values have been the same for all involved tasks.<\/p>\n\n\n\n
In both versions of the scripts, logarithmic calculations are undertaken, and their outcome is different from the calculations based on the square root for the network graphs according to (remaining) task durations. As a result, we only observe a color shift in the task with UID=116<\/strong>. As the remaining<\/em> task duration is half of the task duration, this task is less critical (hence, a lighter color shade) on the right side of the image above.<\/p>\n\n\n\n
Example 9: Same project plan (more in the future) <\/h2>\n\n\n\n
Jumping to the future, more tasks of the same project plan have been completed either entirely or partially. Again, the green<\/span> vertical line in the plan shows the status date.<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-1024x724.jpg\")
Example of a Development Project with<\/strong> completed tasks (Status Date 2<\/strong>)<\/figcaption><\/figure>\n\n\n\n The resulting graphs are:<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-duration-1024x216.png\")
Network Graph colored according to task duration (Example 9-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-work-1024x216.png\")
Network Graph colored according to task work (Example 9-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-slack-1024x216.png\")
Network Graph colored according to task slack (Example 9-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-criticality-1024x216.png\")
Network Graph colored according to task criticality (Example 9-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-remaining-duration-1024x216.png\")
Network Graph colored according to remaining<\/em> task duration (Example 9-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-remaining-work-1024x216.png\")
Network Graph colored according to remaining<\/em> task work (Example 9-v2)<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Example_9-remaining-criticality-1024x216.png\")
Network Graph colored according to remaining<\/em> task criticality (Example 9-v2)<\/figcaption><\/figure>\n\n\n\n We will now look at some of the graphs and compare them with the equivalent graphs in Example 8. Apart from tasks being colored in green<\/span> color once they have been completed, there are only differences in color shades of tasks belonging to the network graphs of:<\/p>\n\n\n\n
- remaining<\/em> task duration<\/li>
- remaining<\/em> task work<\/li>
- remaining<\/em> task criticality<\/li><\/ul>\n\n\n\n
as completed tasks have their remaining<\/em> values set to 0 and therefore diminish the set of tasks that are considered for the calculation. In contrast to this, there are no changes in the network graphs of:<\/p>\n\n\n\n
- task duration<\/li>
- task work<\/li><\/ul>\n\n\n\n
as the respective values do not change for completed tasks. We could, however, in theory experience changes in the network graphs of:<\/p>\n\n\n\n
- task slack<\/li>
- task criticality<\/li><\/ul>\n\n\n\n
as complete tasks have their slack values set to 0 and therefore diminish the set of tasks that are considered for the calculation. This was not the case in our examples, though. It should also be mentioned that there might be changes in the color shade for a task where the duration or work is increased due to a revised assessment of the project manager from one status date to the other.<\/p>\n\n\n\n
![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Color-Shift-8-9-Remaining-Duration-1024x472.jpg\")
Network Graphs colored according to remaining<\/em> task duration from Example 8 [left] and Example 9 [right]<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Color-Shift-8-9-Remaining-Work-1024x470.jpg\")
Network Graphs colored according to remaining<\/em> task work from Example 8 [left] and Example 9 [right]<\/figcaption><\/figure>\n\n\n\n ![\"\"](\"https:\/\/caipirinha.spdns.org\/wp\/wp-content\/uploads\/Color-Shift-8-9-Remaining-Criticality-1024x469.jpg\")
Network Graphs colored according to remaining<\/em> task criticality from Example 8 [left] and Example 9 [right]<\/figcaption><\/figure>\n\n\n\n As mentioned above, the changes in the color shade of the highlighted tasks in the comparisons above are due to the fact that either the remaining duration or the remaining work of the respective tasks change between the earlier and the later status date.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
The scripts create_project_db-v2.sql<\/a>, msp2mysql-v2.sh<\/a> and mysql2dot-v2.sh<\/a> and the examples provided above show how, using the powerful graphviz<\/a> package, traditional project plans created with Microsoft\u00ae<\/sup> Project can be visualized in a set of graphs that can help a project manager to focus on the right tasks. Compared to the set of scripts in the blog post Colored Network Diagrams (Part 1)<\/a>, the improved set of scripts additionally allow the examination of the task duration, task value, and task criticality based on remaining<\/em> values of the tasks, and so can answer the question “How does the project look now?” more adequately.<\/p>\n\n\n\n
Outlook<\/h2>\n\n\n\n
In the near future, I plan to rewrite the script mysql2dot-v2.sh<\/a> into PHP because I want to incorporate an algorithm which I developed back in 2010 that can show the first x<\/em> critical paths of the project using a recursive PHP function and several large multi-dimensional arrays.<\/p>\n\n\n\n
Sources<\/h2>\n\n\n\n
- [1<\/a>] = Introduction to Project XML Data<\/a><\/li>
- [<\/span>2<\/a>] = <\/span>Graphviz Color Names<\/a><\/li><\/ul>\n\n\n\n
Files<\/h2>\n\n\n\n
- create_project_db-v2.sql<\/a>\u00a0sets up the database in\u00a0MySQL<\/a>\u00a0or\u00a0MariaDB<\/a>. The script works with the user\u00a0gabriel<\/em>\u00a0who assumes to have access to the database server without password; you might have to adapt the script to your environment and needs.<\/li>
- msp2mysql-v2.sh<\/a>\u00a0reads a Microsoft\u00ae Project plan in XML, parses it and writes the data into the\u00a0MariaDB<\/a>\u00a0database.<\/li>
- mysql2dot-v2.sh<\/a>\u00a0reads from the\u00a0MariaDB<\/a>\u00a0database and creates a script for\u00a0dot<\/a>, a tool of the\u00a0graphviz<\/a>\u00a0suite.<\/li>
- Example_8-v2.zip<\/a>\u00a0contains all files with respect to Example 8 (created with the current set of scripts).<\/span><\/li>
- Example_9-v2.zip<\/a>\u00a0contains all files with respect to Example 9.<\/li><\/ul>\n\n\n\n
Disclaimer<\/h2>\n\n\n\n
- The program code and the examples are for demonstration purposes only.<\/li>
- The program code shall not be used in production environments without further modifications.<\/li>
- The program code has not been optimized for speed (It\u2019s a bash script anyway, so do not expect miracles.).<\/span><\/li>
- The program code has not been written with cybersecurity aspects in mind.<\/li>
- While the program code has been tested, it might still contain errors.<\/li>
- Only a subset of all possibilities in [1<\/a>] has been used, and the code does not claim to adhere completely to [1<\/a>].<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"
In this blog post, I describe some improvements and enhanced capabilities of the scripts that have been presented in Part 1 already. The new set of scripts can now additionally color network graphs (PERT charts) according to:<\/p>\n
1) remaining durations
\n2) remaining work
\n3) criticality based on remaining duration<\/p>\n","protected":false},"author":1,"featured_media":723,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,36],"tags":[94,93,95],"class_list":["post-683","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-it","category-pm","tag-graphviz","tag-ms-project","tag-pert"],"_links":{"self":[{"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=\/wp\/v2\/posts\/683","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=683"}],"version-history":[{"count":6,"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=\/wp\/v2\/posts\/683\/revisions"}],"predecessor-version":[{"id":731,"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=\/wp\/v2\/posts\/683\/revisions\/731"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=\/wp\/v2\/media\/723"}],"wp:attachment":[{"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=683"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=683"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/caipirinha.spdns.org\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- msp2mysql-v2.sh<\/a>\u00a0reads a Microsoft\u00ae Project plan in XML, parses it and writes the data into the\u00a0MariaDB<\/a>\u00a0database.<\/li>
- [<\/span>2<\/a>] = <\/span>Graphviz Color Names<\/a><\/li><\/ul>\n\n\n\n
- [1<\/a>] = Introduction to Project XML Data<\/a><\/li>
- remaining<\/em> task work<\/li>
- Computing the spread from scratch for each graph sets a clear focus on the tasks which should be observed now because they have a long remaining<\/em> duration, irrespective of whether the tasks once in the past had a longer or shorter duration.<\/li><\/ul>\n\n\n\n
- The task with UID=92<\/strong> changes to a darker color shade although the values for the duration (D=14400) and the remaining<\/em> duration (rD=14400) are equal. Why is that?
- The task with UID=116<\/strong> changes to a lighter color shade. This is because the left graph takes the duration into account (D=72000), and the right graph takes the remaining<\/em> duration into account (rD=36000). As the remaining<\/em> duration is less than the duration, we can expect that change in the color shade.<\/li>
- … have the package graphviz<\/a> [<\/span>2<\/a>]<\/span> installed<\/li>
- … remaining<\/em> work<\/li>