Darrin is a senior project controls professional with 20 years of experience in project cost control, planning, and finance. Darrin has worked for Fluor, Bechtel, Jacobs and Samsung C&T.
Serving mostly the EPCM and EPC business, Darrin’s job experience runs the full spectrum from all sides of project controls and almost all businesses. He has worked at Oil Refineries, Roads, Bridges, Telecommunication, Infrastructure, Rail, and Mining from both a site and home office location. Darrin has extensive international exposure having held positions in Asia, the Middle East, United States and Australia.
A core understanding of project control fundamentals, systems, change management and EVMS (including the use of Ecosys, PRISM, Primavera, and databases) allows Darrin to ensure efficient project delivery and clear visibility into progress, forecasts and costs. The use of digital integration and systems is embedded into his management approach.
Will Agile ideas and approaches fit the construction world – Definitely.
This does not mean I think that a lot of the tried and true practices we have developed need to change. Quite the contrary, the construction world have for decades been the pioneers for quality project management.
What has changed is the way in which people communicate and the culture we live in. Agile does have a lot of good properties in the personal communication. So I believe the construction world needs to embrace some aspects of Agile and the software tools that have been developed to support Agile management
Introduction to Agile in Construction
In this presentation, we can see that indeed many of the terms used in Agile have existed for a long time in the construction PM space. So, in many ways, we already are Agile. Thus, if you go down a path to embrace new management approaches, you first need to understand the current overlaps and also where opportunities exist.
Using JIRA to improve communication on construction projects
There are some great possibilities in using JIRA in the construction world. I do not believe a typical Agile approach fits into the project space; however, many of the ideas are sound and specifically the tools that have been built to facilitate Agile can be molded to fit the construction project space.
Specifically, this relates to the way we communicate and track the work we are performing or the work we are overseeing. Applications such as JIRA and DevOPS have capabilities that I feel are a perfect match for the construction world and we need to be looking how we can customize these new tools to really revolutionize the way construction projects are managed.
This article captures 2 issues that are related and perhaps not clearly discussed or understood (not even by me): Level of Detail, and Transactional vs Account based tracking
Many cost tools, for right or wrong, are now almost entirely Control Account based. This leads to some conceptual issues where people have been use to managing data in a more transaction way.
Additionally, when we begin to establish defined control accounts, picking the right level of detail, and how changes are managed between Controls Accounts, requires a lot of creative accounting (thoughtful process mapping through all your systems & Digital Strategy)
The below is just a primer for a potential discussion. This is a post on what it means to pick a correct level of detail and what it really means to how you manage your costs.
This is my wheelhouse, the way my brain primarily works when dealing with projects. List Management. Everything is a new record.
We have contracts with detailed line items, we want to retain our budget line items, each contract line item will have various columns for Committed, Forecasts, Incurred and Paid values. We manage these detail items in this way. Below is a example of how we really manage costs (and progress and deliverable). Excel (sharepoint, or a simple flexible database) provides an ideal solution for users to manage this detail.
However, the new age of cost tools want us to view projects at a more “control account” level. In the above example, I have created a control account to a WBS and Commodity code level of detail.
In the above example, we have 4 contractors working on this scope in various capacities. Although, its way more subtle then that, we actually only have 1 contract and 3 expected contracts. We have parsed our budget into what we expect to be 4 different contracts. Thus only 1 contract has a commitment, but yet we have a forecast (and budget) for all 4. Each contract will have a full detailed list of detail items that we will manage. We will items for specified growth, perhaps contingency, maybe a few site instructions. A transactional list!
Quantity Growth – new line or modify existing?
Here again is one of the conundrums of how we manage (specifically related to progress measurement. Consider a project with some concrete and steel.
If we have a change in quantity for a foundation, where do we capture the change and what does it look like in our database. Too often, we look at this and manage it using a simple excel file – which can make the process easy. However, this is a very complex issue. If we add a line item and base our progress off committed quantities, we will have to update 2 line items with %’s. However, so many options exist to capture this.
And again, if instead this item is managed at the “control account” level, all we need is the total actual quantity, or simply the overall % for the control account. When you look at the above from a control account level, you capture all the detail, but yet for % management, you can disregard all the %’s to the details and only insert a % to the control account.
Which method is right, which method fits into your cost/progress system, which approach aligned to your specifications?
In the second method above, you loose the ability to calculate a specific % for variations. So again, we have taken just a simple issue, and created a complex nightmare. Obviously, we all solve these problems day in and day out. The issue here is again just to bring this topic to light and how the new range of cost tools may not be flexible enough to really capture what we do – nor should they! The real answer is as we all do now, some detail is managed inside excel and some abstraction ends up in the system.
Control Account Management
In the above, we have seen the importance of picking the right control account level of detail, but, we perhaps haven’t conceptualized what a controls account is in the first place. For “control account” management, we want to manage “SCOPE”. For this package of scope, we sum up all the detail coded to the same codes.
When we look at scope, it is much easier to compare against our estimate which was built to this level before we had to detail with specified growth, claims, site instruction and even contractor commitment. This is why we want control account management and why so many cost tools are forcing us down this path.
Whats the Problem?
In the above example, the “control account” is meaningless. We can not “manage” anything at the control account level. The control account is only a metric.
Instead we are going to manage our contracts in isolation. Each contract will have its own specifics and likely its own approvals when we modify a forecast or a commitment.
A solution to this conundrum is to split the above into 4 control accounts (or more). However, that creates a nightmare for everyone dealing with the new cost systems where creating cost accounts, loading budgets and costs is not straight forward. Doubly so as we haven’t even begun to discuss at what level we manage our time phased data.
All the new tools also allow us to manage “detail items”. Budget again, as soon as you start to push the level of detail of management into the detail items, you may as well make the detail item its own control account.
The Problem is – Whats the right Level of Detail?
You can run this problem down rabbit holes with how we manage engineering deliverables, how we manage progress items, manhours, quantity management, etc. Here I have just presented the problem related to strictly just a cost control level. But yet the dimensions for each of the items above is multiplied by each of the additional management datasets we also track.
Picking the right level of detail that goes into our cost tools, is more of an artform than it is a science.
My view is that we need easy flexible transactional capabilities from cost systems to ease the excel hell aspects. But yet at the same time, need to understand how the transactional records join up to perhaps a more formal CTR or Control Account level of detail.
This post is an extension to that which instead of looking at engineering model development, instead looks at construction development. I don’t want to delve too much into the details about exactly how this was built (again see the post above).
Some big differences is that I have used a resource assignment view. in addition to the date metrics This allows for resources histogram and progress curves to be quickly sorted down to an activity level. This approach also follows a prior post Resource Analysis Dashboard .
The underlying data is very similar to our engineering progress example. We can use a flat file export direct from P6 with a standard set of columns. As I have mentioned before, you can achieve this in a SQL query as part of a larger data model, although with everything, a delicate balance is needed (balancing database formalism and easy excel solution)
We will also have the resource assignment data
The WBS Slicer and Area Selection
This design element doesn’t work for project with too many WBS elements. For this example, each major area only has about 10 WBS elements, therefore I could pull this off with no drama. I really prefer this selection as opposed to drop downs where it is often difficult to quickly make selection.
The Pie and Metrics
Here we follow much of the look and feel I used with the engineering progress; however instead of just using activity count metrics, I have also inserted hour and percent complete metrics. There is nothing fancy about these.
The Data Table
I’ll sound like a broken record again, when you have a good design with one aspect of a project, you can likely take that and run with it for many other areas. In a following post I will detail this systems engineering aspect to nearly everything we touch.
Obviously the key inclusion into the table is the budget units and %’s. I still prefer these tables views vs the GANTT views. Having clear visibility into the last month dates, the prior month dates, and variances is the purpose of this view.
Again, the extension of this are endless. At this stage, we are starting to see how pre filtered views provide more focused dashboard as compared to a one size fits all. Sitting in an EPCM world, most of the detailed activities and schedules are managed by our contractors. Thus, this construction view is more suited to using an export from a contractor Level 4 schedule.
At some point, we will need to begin to discuss an overarching design where a user can navigate to our various dashboard in a logic way.
In this article, I will run through all the steps required to produce an elegant Engineering Progress Report.
The intent is not to delve into the manner in which the progress or schedule are updated. I have assumed you have a schedule and progress status for each key area. It is quite amazing how easy is to generate this dashboard, and also the extensions available to use this not just for engineering, but for fabrication, material deliveries, major milestones, contractor key activities, etc.
I will outline the format for our 2 key datasets and then follow with the creation of 2 dashboards: An Overall Status Gauge, and the full detail EPR Dashboard
P6 Schedule Data
Below is our data set we want to use. This data set has been specifically tailored to our resulting visual. Thus, instead of linking directly to an XER, importing into a data model, and performing perhaps too much data work, a nice trick is to instead define specific VIEWS inside P6, so that you can easily copy-paste directly into Excel, then import directly into your dashboard. Thus, the below can be quickly generated each schedule update cycle.
A very nice aspect of this data set is the field “TYPE”. It is good practice to tag activities of a specific type (this ties into my belief about using a framework approach to approach controls). Thus, in theory, you can export the entire schedule, and drive many different dashboards by just filtering on different TYPE fields. In this example I have used
M090 = 90% Model Review
M100 = 100% AFC
Although, consider tagging every concrete pour Activity in your schedule with C010. You can then use that code to drive a similar dashboard for concrete pours: Or you use F100 for Module Fabrication, where we tag the completion activity for each module for use in dashboard. Ultimately you create a catalog of TYPE codes and can go dashboard crazy with how easy this turns out to me.
This data does not have all the fields we will need in our dashboard. Specifically we will want to create a several measures that will allow for a few metrics. We will need to know if an activity is “FINISHED”, “NOT FINISHED”, and “Critically LATE”. Because these fields are dependent on your target audience, its best to leave the generation of these to code (because everyone can code right!). If you wanted to display metrics on “Started”, then your source data would need to include the start date and perhaps the activity status field from P6. Again, its important to understand the relationship between your visual and your data. In this example, I am treating these activities as effectively milestones in which case the concept of “started” doesn’t apply. key conceptual discussions such as this are vital.
The progress data in this example is only overall progress. The intents is to just show an overview for the entire project and quick metrics for model reviews. Ultimately, you would want a “WBS Specific” dashboard that would display more information over the entire lifecycle of that WBS. In that view, you could present the engineering curve and perhaps EVMS metrics.
Strategy – Do not do everything in one place- keep focus
Too often, I see users pushing design features into dashboards, for what appears to just be whimsical value. Dashboards are not meant to answer 100 questions. Its easier to have 100 dashboards each displaying a key metric, as opposed to 1 dashboard displaying 100 metrics. Keep your approach CLEAN and FOCUSED.
Ideally, our progress data will include fields such as Area, WBS. In this example I have pulled data with just 1 data date and only 1 dataseries (Engineering_Overall). Your backend progress data will likely have data from multiple cut off dates and for multiple series.
Our progress data will look like this. The full data set will also contain a series for “Construction_Overall” too. This will be used on our summary page to outline the power in using this approach to progress data.
Linking our Data into PowerBI
In this example, both data files are simply Excel based files with the data converted to table. This allows for the easiest importing (and also allows for quick refresh of data). Housing the data in the excel files can also facilitate a movement to a more digital way of thinking (more on that in another article)
VISUALIZATION 1 – SUMMARY GAUGE
I am a firm believer in Overall Project Flash reports. So, when we think about dashboards we should have a starting point our overall project status. Thus, the elements presented here are only a key subset of metrics and visuals I would expect on a Project Status Report dashboard.
In this example, looking at engineering progress, we want to see what Percent % Complete we are and how that compares against our Planned % Complete.
A Gauge is a good way to provide a quick visual (Bullet charts are other, and really, the skies the limit)
To generate this we need to create 2 measures: Actual % and Planned %. This is where you really need to understand how dashboards work and how databases work. If you feed a computer a data source, it is no innate way of know something as simple as “What is the current %”. Therefore, we need to write some code.
Because of the format of our progress data, we can search for the maximum data date, then find the value of our actual % field on that date. We can follow an identical approach for the Planned %. Depending on your data, you would need to custom build these measures.
Code to generate our measure for Current %
M_Progress_Actual = CALCULATE (
SUM ( data1[Actual] ),
data1[DataDate] = MAX ( data1[DataDate] ) && data1[Date]=MAX(data1[DataDate]
Code to generate our measure for Planned % (similarly we could also pull in our Plan late)
M_Progress_Plan = CALCULATE (
SUM ( data1[BL_Early] ),
data1[DataDate] = MAX ( data1[DataDate] ) && data1[Date]=MAX(data1[DataDate]
The required fields for the gauge are obviously these 2 measures.
Value = M_Progress_Actual
Target Value = M_Progress_Plan
We will also need to provide a filter where Series=”Engineering_Overall” (note that this gauge can now be easily reproduced to showcase planned vs actual for all Series inside our data source. Obviously in the image above you can see I created 2 gauges each with a filter for the specific data series. Ultimately if your back end data has multiple data series for progress sliced and diced different ways, all you have to do is adjust your filter and you can display an endless series of graphs. Of, you can fancy with smart slicers too.
VISUALIZATION 2 – Engineering Progress Report
This is perhaps the most easy to read, interactive and intuitive view into engineering I have ever seen. We can immediately filter into what areas are complete, what areas are critical, scroll to see upcoming deliverables and see an overall graph.
It might seem we have a lot going on here, but again, this is all driven off 2 quite simple data sources, and for this page, mostly everything here is from 1 schedule driven table.
The Data Table
The Table is just pulling from our Schedule data (although I have inserted a page level filter to only include activities with the TYPE = M100 and M090). Our fields are as
In the above image, you can see I have had to insert a few measures. I don’t want to go into them all. I’ve inserted some conditional formatting into the Actual/Forecast date column. To achieve this, I created a measure Activity_Status_Num
Then, with these values I can select a formatting specific just for that column in the table. This is very nice feature of the tables in PowerBI that can add nice level of polish.
The Donut Charts
A nice feature of the donut chart is the count metric in the middle. It is generated from a nice little bit of code as seen below. We have 2 Donut Charts. One for our 90% activity and another for the 100%. Thus, all we need to do is place a visual level filter on each.
In the above, there are 2 measures: “IsFinished” and “DonutCount”. Again if you want anything to display on a dashboard in a digital world, you are going to have to see this type of code
The real power of the Donut Chart is to allow for very quick sorting – after all we want to see the critical late activities right! Just click the red 90% or 100% section.
We have a progress graph too. This is effectively a dumb page level graph. It is not linked to a specific progress series for each WBS. So it will not auto update, and our data model does not link these tables. Although, the graph should add context to the overall page. Deviations from the plan curves, should be viewed by a growing number of critically late packages.
Care needs to be made whenever we look at schedule dates and progress graphs. We do not typically create progress graphs at an activity level (although, you can certainty consider it – I would offer caution against going down that route).
This example has show the power simple data sets can have to improve visibility into our projects. This only showcased a few engineering based activities. However, if you read between the lines, you will understand there is nothing “engineering specific” about what I have done. This approach is completely universal. Given this example also included a progress data set for Construction, obviously, the easiest extension will be to link in a few construction activities in the same way.
Can you imagine using Twitter in the world of Construction – I can! Read on..
Recently, it has hit me, just how poor our project management world is. The ability to clearly communicate and understand what is occurring on a project appears completely lost. Planners have absolutely amazing schedules (I do believe this), cost engineers have incredible detail about the cost build ups sliced and diced every which way, our document management systems are full with every manner of communication – BUT we are still left sitting in meetings where everyone is confused with various key information either not available, or buried inside someone own excel file, bounced off 100 emails threads, or a million other permutations.
Issue – Visibility into our Projects Sucks
Why has this happened? In my view, our leadership teams have failed at pushing good practices of project management. Leadership teams have transitioned from “manage the work” to “manage the people” (thank you Edin M for this quote).
Solution – Get Back to Basics
Project Management is task management (you can argue, but even all the new people management issues – are still tasks and can be managed in the same way).
We are building something – lets focus on the activities required to build (and engineer, and contract and procure). All the activities that exist in our Primavera schedules! We need these activities front in center and OWNED by project management. Honestly, how can we even have a management discussion and NOT have the schedule front and center. The schedule says what we should be working on, the schedule says when we should finish something, the schedule says what comes next!
I believe that schedules have been forgotten in project management because they are too unwieldy, to abstract, can only be run by P6 jockeys and not the project at large. We need to get our schedules into the hands of those that actually manage the scope.
In the past, our leadership were more in-tuned to schedules and this synergy was easier. However i fear in today’s world, our leadership have lost the tools and dealing with our schedules (no thanks to our reliance on antiquated tools like P6 that perpetuate the need for designated planning teams to operate the software in pure isolation to the real PM teams).
Issue – We need better Project Management tools.
Answer – They exist everywhere!
Commercially built, off the shelf project management software has risen to be one of the dominant fields of software development. The construction world needs to embrace the tools and get back to basics. It is odd in that 20 years ago, the leaders in project management was the construction world. However, when the technology world sprung up, they didn’t have the knowledge we did, so they built their own tools and approaches. I now believe the tide has turned to the point construction project management now severely trails the rest of the business world.
So How Does This Work?
First, the corporate strategy teams need to decide on a platform (hint – USE JIRA).
JIRA Quick Demo
Here is an example of a JIRA typical managemetn page using JIRA.
This is a short quick example of some substation work. I have populated JIRA with a few activities that might mirror what you currently manage inside your existing schedule. The difference here is that these activities are not updated by a planner, they are activity managed.
This process to push the ownership of schedule tasks to those that actually manage and deliver the scope is where immense value can be gained. Additionally, each activity allows for commentary and discussion. The ability to insert comments and discuss an activity or its relationships with other activities also brings teams together an allows for a focus on touch points to be activity managed.
However, really the choice of software isn’t that critical, its the work processes you are going to change – the new online PM tools are structurally all the same. What we are pushing is simply “clarity in what we are doing”. We are pushing the management oversight of what we do, into the hands of those that actually manage the scope. Don’t hide your schedule, don’t hide weekly and daily reports inside your document control system – embed everything into what is effectively a social media platform.
In this example, I am using OpenProject.org , however, keep in mind there are a lot of systems that all work similar.
The starting point for me would be to add you P6 activities to your tool. This is the natural place to begin. You schedule already has a structure and usually a very good balance of level of detail.
In the above, I have added a typical task that will exist in our P6 schedules. Immediately off the bat, we can see we are operating in a distributed web based environment. We have a nice detailed description for this activity and we have the ability to assign this task to a person.
Up to this point, we are a little overlapped with P6. However, what is lacking in P6 is the ability to really discuss and communicate and UPDATE information associated with a task. The ability to pull the task into a proper project management discussion.
The above example says more than you can find in any weekly or monthly report. A picture tells a 1000 stories! The picture is also properly assigned to the activity it represents. The activity has a clearly visible Finish date than can be live edited 24/7.
Our new tools are not meant to replace P6. They are meant to force our discussions into properly structured slices of the project. They are meant to clearly communicate the status of activities. They are meant to get everyone onto the same playing fields when discussing something so that 5 different people do not end up with 10 different dates.
An activity only has 1 start and finish date, an activity only has one percent complete. It is maddening when a project manager asks me to insert the contractual dates into a report. Honestly, when you are building something, the contract date is useless in helping you decide “when will this finish”. It was only the starting point. When you get people out of their office view, in into “I need to manage this scope” you quickly understand that the contract date, or even contractors weekly reports are useless. You have to make a determination of when an activity will finish based on what you know at that time – and be proactive in actively editing the dates when required
Empower people to update activities!
This is So Simple?
I sit and look at this capability of something I built in 30 minutes on a Sunday morning and really wonder why our Project Management is leading us down what may not be avenues of real improvement to projects. Does our Project Leadership have the vision to accept such simple solutions to improve our communication?
I have discussed this before, digital transformation is all about Keywords – not project management. Real digital transformation is about altering the way we work – not building a dashboard or a database. This is why digital transformation is not working. Provide tools and process to manage the work, enable your staff to manage their own scope, and clearly communicate and update their tasks.
Really think about how you manage your scope and how implementing a more social platform to break down the walls of communication. Understand how this is disruptive to our old ways of working (not updating schedules). Get people talking off just one play-sheet!
Twitter in Construction
I’d like to end this with what I thought was the most amazing application of this new management approach.
I kid you not. During construction, these guys posted nearly daily pictures and updates.
While working on this project from the home office, I got better updates from the project twitter site, then I did from the project manager. Yes, are you finally able to see that solutions exist, creative solutions exist, that can bring construction in the new digital world!
Who doesn’t love the glossy Level 1 reports our project produce. But really, when you look into these beauties, really understand the difficulty that goes into them. What follows is first a description of what a typical Level 1 report is, and how we can structure our excel based data to be a bit smarter.
This is by no means a fully comprehensive guide on this subject. It is instead just a primer to get us thinking about how we feed data into our reports.
Who doesn’t love the glossy Level 1 reports our mega construction projects produce. But really, when you look into these beauties, do you really understand the difficulty that goes into them. What follows is first a description of what a typical Level 1 report is, and how we can structure our excel based data to be a bit smarter (which is the real message to this article).
Interspersed with hopefully be a few key strategy points which can guide your work.
I’ll then showcase how you can take what will now be structured data and upload into a powerBI visual (although the process to capture the data into any database and drive any visualization tool would be the same)
Strategy – Don’t be afraid to use excel (not everything needs to be automated)
Key Elements of a Level 1 Report
Cost and Progress
Here we are presented with:
Overall progress curve
Cost & Commitment curves
Some may argue what to lead with – for me its always %. No bigger value highlights where your are more than what % are we. Not displayed on the image above is a data series reflecting how many people are have and comparison against planned. People achieve progress. Its impossible to talk progress without talking how many people we have. The graphs provide enough enough context to allow for discussions about productivity without having to muddy the waters
The cost sections should include visibility into what our final forecast costs will be (and comparison against baseline). Underneath that key metric are a few sub items such as how much contingency we have, a few cost curves associated with spend profiles and commitment profiles.
Schedule and Narrative
The schedule aspects of a Level 1 report are always tricky. Do we need to only display the final project milestone? For me, on major projects no single DATE has any meaning. Thus even on a Level 1, I still prefer to include 10-15 dates that represent some key aspect of the project. All dates should be compared against what we said last month to highlight current month variances, and dates should be compared against our project baseline (or whatever current approved version thereof).
The narrative section of a Level 1 can nearly always be updated by simply reading the progress, cost and schedule tables. Just put words to the graphs. Key adders here are insights into RISKs. What may come in the future that will alter what we are saying today!
As always, safety metrics are also usually front and center. For me, this has always been a difficult aspect of our jobs. A political correctness that is forced into our reporting. Don’t get me wrong, safety is the most important aspect of a project. So, including a safety table somewhere on the Level 1 is always done. For this article, I want to instead focus on the key project control elements and data integration.
Level 1 Data Structures
So, we all know what a Level 1 report looks like, and I would fathom we can all mostly agree these are the elements included and can be rolled out as a standard for any major construction contractor. Most of our reports likely already report this information in some manner or another. The entire point of this article is that we should really focus on entering the data in a smart data centric way so that if you want to automate anything down the line, you have the foundations to do so.
At this stage, I don’t want to talk about the source data used to generate your summaries. We can leave that for a later discussion.
Key Data Domains
We are aiming towards consistency here and want to actually represent all the data required for our key Level 1 chart to be housed in a database. Therefore we need to have structure.
Strategy – Do not focus on systems, focus on DATA
A critical strategic element in my approach is that I do not care what systems you use. Our reporting is not a function of our systems (at least in this step 1 phase). We instead need a structure from which we can extract data and as easily as possible, move that data into a template or format in which we can drive our level 1.
If you go down the path to seamlessly integrate source systems with a Level 1, you unwittingly constrain yourself.
Typically our (time phased) progress data will be sourced from Primavera. There are other systems where the progress data may live, but again, that isn’t the focus of this article – I don’t care where it lives and neither will any seasoned project controls manager. We just need to know it exists and has a common structure
Here, a few key notes, use a consistent data format. The above structure is how all your progress data should be housed, not just Level 1. All time phased data, all the way down to Level 5 detail items should be managed in a data structure, not a fancy formatted excel file. Trust me, updating a table such as the above will serve you in the long run. Even if your data is fully managed inside a system such as P6 or PRISM or ECOSYS or COBRA, you should be able to at least extract Level 1 into the format defined above.
You guessed, we can capture our Level 1 cost data in exactly the same format
In the graphs we are building, there are only 11 specific datasets. Only 4 of these require update on a period basis. So again, we really boil this down to something simple.
Strategy – Do not over complicate anything in your Level 1 layer
The implementation of the specific data model I have outlined above fits the strategic approach to keep your level 1 simple. Any project can implement this data model for Level 1 with without any integration into source systems. Level 1 can be updated by the project controls team doing a few copy-pastes into excel to capture project wide data. Again, I would assume your teams already do this, but perhaps end up copying this data into various other corporate systems as well.
Again, we are keeping a simple approach and only capture the required information.
Here, we are forced into a different structure. So whereas the cost and progress data can fit the same data model (as seen above), we will need a different template for schedule dates. We will typically be using Primavera, as such this model fits P6, but the idea is universal.
I do not believe this information can ever be fully automated from our scheduling systems. These paths will continually be adjusted. The planning lead will always refine what activities are being tracked to be displayed on the Level 1. Behind the scenes, there are tricks upon tricks to pull the dates, however, again, we are talking about the data layer here, not necessarily HOW you get the data into this format.
It is entirely possible to have the assignments encoded into P6 activity codes. Therefore, it would be possible to integrate your Level 1 data directly into either the source P6 database, or an XER export. In my experience, any automation that is attempted in this arena (for Level 1 data), is futile. We are only talking 10-15 key activities. Let you lead planner sort out how they get the data into this format. Again, our strategy is to not over complicate this. If the data is provided to a digital team in the format about, you are for all intents done.
The model above only captures the finish dates. If added visuals with simplified GANTT charts are needed in your Level 1 (and will be discussed in my next Level 2 article), you would have to edit the above.
The nice value of the above structure is that we have effectively created an interface, an integration layer, between what will be P6 data and our dashboard. The list of what activities can easily be edited by way of a sharepoint list. Then, in your data model, you can link on scheduleID to pull the relevent date data (I suspect many do this).
Too often, narrative comments are shuffled between parties via email, entered into several documents, edited, customized, etc. The project controls team is always struggling sourcing commentary from various sources, and in my experience, we end up entering in something ourselves.
Level 1 data structures have to fit into these complications. In this realm, sharepoint offers a canned solution by way of sharepoint lists.
Strategy – If Technology already exists, use it
Strategy – Technology can be used in innovative ways – use a mashup mindset to use existing technology in a new way
I find that sharepoint lists offer unparalleled capabilities for commentary. However, for lists to be really functional, they need to be embedded into FORMS or some routines that provide export functionality
In this example, I have mocked up a simple INFOPATH form that could represent our sharepoint fields. The sky is the limit when it comes to existing technology that can automate the capture of this type of commentary.
The value adder here is that instead of allowing unstructured comments (via email or manually marking up a word , excel or power point file), we have structured comments that are housed in a database and that database can be updated in a distributed manner using WEB based technologies.
The above would be a web based form which will be updated by the associated responsible parties. However, we can’t quite import a form into our data model. When the above form is filled out, the data will be stored in a data model (which we will have to design first before we can even build the form above). Thus, what we are looking for is something akin to the below
The above is just a table in an excel file, but again, when we house data in this format, it can naturally flow into a database. That is what we need to focus on. Even in our excel reporting world, if you can capture commentary in this tabular data centric way, you can still link to it from your main dashboard tabs to be “smarter” in how information is managed.
Strategy – Focus on the DATA! (I can’t say this enough)
Everything we do can be captured in a data model. Every report we design should be able to pull direct data out of a data structure. Thus, before we add anything to reports, first consider the entire flow of data required.
Putting it all together
At no point in time in the above have I had to rely on a source system. However, I have been able to take a typical Level 1 report and extract everything from it. I have taken this data and outlined a data model (in simple form) that can drive not just 1 project, but an entire corporate endeavor in this space.
As with everything, nothing novel here. Many companies already have systems that capture some of this information. This is more just a thought experiment for those that perhaps do not have a clear data model that supports level 1 reporting. It also highlights the discussion topics of “what are the manual steps” – because there will be manual steps in getting the data into the right format.
For me, everything above has to be manual at some point up or down the food chain. Your projects and portfolios need to have the discussions about where this type of Level 1 data is housed. If all projects already have this data in consistent databases, all you need to do is query that source. Everything discussed here is system independent. You can easily generate these data tables by way of query a source system directly (if you can), but I have not limited or require that approach
Strategy – Whatever you do, allow for flexibility
Even though my data model is entirely excel based, the data structure is very powerful. I can, in quite automated steps, import and convert these datesets into a more database model and thus gain value from dashboards that wouldn’t be custom for your project, but could drive an entire portfolio (and when you see how this scales to Level 2 data and beyond, the worlds your oyster).
If you actually want to proceed with a dashboard, and if you have your data as outlined above, here is what you can do with it. In fact, I would recommend that your source tab in excel that is driving your dashboard looks like the below.
The above data isn’t “immediately” friendly for digital reporting. A few transformations are required. The key steps involved are (the below was done as just an example using PowerQuery)
Unpivot the Timephase date columns
Pivot the the “SeriesName” column to create a unique “Column” for each dataset (this is need to create unique lines on our dashboard graphs)
At this stage, we have a nicely formatted table and we can now import into PowerBI. The intent here is not to showcase a beautiful Level 1 dashboard in PowerBI. My intent is more to showcase the data structures need to drive a dashboard. With the above data, we get pull each data series into graphs, tables, cards, KPI metrics, etc.
Our model has tagged each record with a “As-Of” date. Thus you can utilize this structure to have your dashboard display ALL prior months by way of a slider or select. Given more advanced skills, you can also pull out metrics about current incremental values vs what we said last month. Although, I feel those metrics are best served in Level 2 report where more detail is available.
Apologies for the look and feel below, I just pulled in the data to showcase that indeed you can drive a dashboard with what is effectively just a few lines of data that every project already has. We can bring together cost, schedule, progress, and commentary quite easily and in a very data friendly way.
For me, there is no substitute for an excel based dashboard. The value in this for me is ensuring that when I produce a Level 1 Dashboard (in Excel), I should give consideration to ensuring my data is structured appropriately. This gives us a fighting change to perhaps go down the path of creating a more digital world. It also allows for perhaps more flexibility in dealing with Level 2 data to maybe have some real automation of rolling up of data.
Level 2 obviously. I hope to showcase how the same ideas and concepts here can also help you structure your raw excel based Level 2 data to perhaps be better utilized in a more digital world
In dealing with P6 data, sometimes what you expect, is not what you get. When it comes to date formats, this is quite relevant. Here is a guide to transform XER and P6 copy-pasted values into proper date formats.
In dealing with P6 data, sometimes what you expect, is not what you get. When it comes to date formats, this is quite relevant. Here is a guide to transform XER and P6 copy-pasted values into proper date formats.
Problems with XER file format
When dealing with a native XER file, you need to be careful because you can’t always use the field you want. Below is a screen print from a typical TASK dataset.
When an actual start/finish date has been captured (as seen in the “act_start_date” and “act_end_date” fields above), then the dates that are stored in the “early_start_date” and “early_end_date” fields are no longer valid.
Thus, you need a routine to check if an actual start date exists, and use that in lieu of the early dates. Ultimately there is no good way to deal with, excel to write a routine somewhere in your data import routines – if importing in a database. Another option is to edit your XER import excel file to add 2 new columns for simply “start” and “finish” that will run your check for you in the native excel file before you import the data into a database. This is an easy hack anywhere you manage your data: However, a core issue here is:
Where will you clean your data?
For me excel is often easier, but that does mean your processes will not be fully automated. Either way, the fix is fundamentally as seen below
If you are not dealing with an XER, you will likely simply copy-paste directly from P6 into Excel. Here again, we have to deal with a few (minor annoying) complications. Everyone I know in the planning world deals with this and has their own routines. I wish I could post them all, because some truly elegant solutions exist. The below is not meant to be “do it this way”, just more of an indication that if perhaps you are running into difficulty “this will work!”.
Above we can see a typical copy-paste result from P6. Obviously, this is filled with non-date formatted cells. This is caused by the ” A” indicator for activities that have an actual date, and a “*” for activities that have some sort of constraint applied. I have seen a few equation based solutions to strip the bad characters out. I find a code based solution to be slightly more elegant. It also means I do not have to deal with adding extra columns to my file, or performing any copy-paste values. But again, I am sure we all have nice solutions.
This routine only works on 1 column at a time. For me, I can simply copy and paste the select statement and insert a routine for each excel column with a P6 date (typically just start and finish). After pasting in P6 data, I will open the routine and click “play”. You will need a macro enabled file and will need to be at least a little comfortable with “view code”. There are a few options to auto call this function using a button, call the function when you close the file, many different options.. They all require a bit of VBA knowledge, although, like seriously, who isn’t in our world.
For the above, I ripped most of this from a routine someone else wrote (a routine that stripped the ” A” off the string. I had to add the xChars = “*” aspect.
For a find/replace statement, it is possible to use the below string to strip the “*” off. Here we have to use ~*~ because if we use *, it will replace the entire string. Again, a million ways to handle this.
Again, there are a 100 ways to skin this cat, and all achieve the same result. When we deal with data from P6, I find it so amazing that when you ask 10 people how we deal with the data, you will end up with at least 20 replies. Ultimately, I believe this issue is a telling critical flaw in the underlying software. In the digital world, we need a completely different paradigm shift in the way we store and manage data.
Specifically in the Project Management world, I doubt anyone who uses tools such as JIRA or DevOps have to deal with annoyances such as this. Thus, perhaps we too should be using those tools!