How is an engineering deliverable list a bit of a misnomer? Find out of a web page with editable fields can streamline the tracking of progress on list items.
I am a firm believer in the use of a standard project website portal from which the project team at large can quickly access key data and metrics about the project. This is not meant to be confused with a project dashboard, or PowerBI visualization. This is simple html file linked to a database with key flat-file information. Using smart JavaScript code, it is also possible to EDIT some of the key information.
With everything, before we even begin, we want to focus on what core digital strategies we are trying to tackle. Again, the project website is just a tactical approach, underlying it are the more relevant strategic goals we want to operate under.
The key data sources involved with engineering progress will be:
Engineering Deliverable List
Manhours per WBS
Your engineering deliverable list is a bit of a misnomer. A lot of engineering and design tasks are not specifically related to a “deliverable.” In my view of the world, while you will have specific deliverables and need to track them, your progress list should also include everything else you are doing too – up to a point where perhaps the level of detail is too small.
For analysis, productivity factors for engineering are critical. As such, this post would not be complete without a discussion and visibility into hours and productivity factors.
The Menu
Our menu follows many of the key functions of project controls. However this specific post will dive into the ENGINEERING section only.
If we expand our menu, we see various views into our data: by project, discipline, contracts, schedule IDs and a unique view for controls engineering
Detail Views / EDIT MODE
Perhaps before we look at our summary and drill downs, the main control screen we would use on a day to day basis would be the Deliverables by WBS and Discipline
In the below screen, we can see all out progress items, our budget hours, progress% as well as the mapping to P6 ID (as all progress items should be mapped to into your schedule)
This view into deliverables by itself is not specifically unique. Where the magic happens is when we enter EDIT MODE.
Inside our EDIT MODE, we can directly update the progress %, and update our mapping to schedule ID. These features in my mind are your killer features that distinguish this from any other app. The shear ease in updating items in this way is a breath of fresh air, not to mention the live feedback and visibility throughout your team.
JIRA
Each combination of WBS and Discipline can be mapped to a JIRA task. Obviously we know our WBS can be considered an EPIC. The power in using a tool such as JIRA is that we can now track more detailed L4/5 tasks using JIRA Subtasks. These are completely flexible to allow the user to manage their own tasks.
Additionally, the ability to embed commentary and status is a brilliant way to distribute and communicate key status and blockers if any.
Summary Pages
Now that we have seen how we will actually interact with a project website, we can now showcase the summary level reporting. Specifics of how you report can often times be better captured using an analytics platform like PowerBI; however, more often then not, simple summaries and metrics are what drive our business.
In the summary by project, we can easily see key metrics per project. Each project would have a link to allow us to drill into the details for each project
In the above, we are now looking at the individual functional delivery areas (disciplines) for our project. Again we can see key budgets, spent hours, progress and productivity measures too. By clicking each discipline, we further dive into our project by now looking into the detailed WBS elements for that Discipline
It is in this view that we can see detailed metrics per WBS/Discipline pair.
Understanding our Spent Hours from time sheets is a critical management function. As such, the SAP actuals here is a link that takes us to a screen where we can view the detailed weekly time sheet data to see who (and when) has book to this element.
Conclusion
In summary we started with some key strategic thinking and built out a tool that ticks a lot of boxes in the EPCM construction world.
More often then not, I am confronted with technology that “shows me” but doesn’t allow me to interact, edit, or collaborate. Everyone wants to solve the “one source of truth” however, information changes and is updated by people. That is the missing link in a lot of our data analytics. Look into the work processes that generate information in the first place. Look at what people need to better capture their raw information into a data format and platform that others can now use.
This approach to put management of engineering items to plain sight, with ease of access is just one approach. There are 100s of ways to approach this, but if you stick to core strategic ideals, you can’t go wrong.
Which mix of applications will improve your construction progress reports? Understand simple steps, like adding comments to SharePoint and quickly publishing Primavera construction data through Excel, Access, and PowerBI.
I have dreamed about the ability to easily integrate many of my favorite applications. A few technological roadblocks had prevented me from pursuing this, but I am finally in a position to showcase what I view to be a quite seamless integration chain and management process.
Our key objective is to
View our schedule activities
Allow our area specific team to provide commentary on each activity (if we view the activity deviating from our plan or perhaps need to include notes about key interfaces)
Allow our project wide team view our comments
Provide a tool to present schedule and progress aspects of our area
Note that I still view JIRA as providing a tool that immediately makes this post redundant. Although, in lieu of everyone jumping on JIRA, let’s dive right into an interesting use case of common applications.
Primavera
For this example, I am using dummy schedule data. The ideas here are quite universal and can be used with any schedule. Care should be take to ensure proper filtering to avoid ever displaying too many activities.
The key objective here is to be able to export our activities to Excel and then upload the data into a SharePoint list. Tools, such as XER reader, provide the ability to quickly move activities into Excel.
SharePoint
Here, a lot of interesting hacks and strategy come into play.
Digital Strategy – Enter Data Once
SharePoint is a perfect tool for editing data in one location, and to source it in many different ways without having to reenter it.
The first thing we need to do is create a list.
You can insert a few more columns to pull in Plan Dates, or prior updated dates. However, we are only looking at a comments functionality with this list. We can live with a very stripped down data set (and leave PowerBI to capture everything at a later point).
The above view is what you would see in the edit view on your SharePoint website. This functionality is fast and allows a team to provide a much more concise internal list of comments specific to each activity (or perhaps only key interface activities).
Where the above doesn’t work? It doesn’t work in situations where we might have a chain of comments. SharePoint allows effectively free text fields. We can enter multiple lines of data for each comment and include dates inside the comment for when the comment was made. There are more sophisticated data models that would allow for multiple comments to be actioned on each activity. However, this example is a lightweight solution — using easily available, off-the-shelf technology. From this point, we dive into your standard PowerBI template.
An URL with predefined filter criteria applied to the SharePoint list is simple. However, we need to use this with caution, because we may end up with 1000s of activities in SharePoint and it will be hard to update this in the future.
Microsoft Access
It is possible to directly edit a SharePoint list using MS Access. In this example, we get constant updates from our contractors on dates. Keep in mind, the SharePoint list is not the management tool for the dates or progress (however — looking at the above, it can be!).
To allow for the list to be bulk updated with new dates and progress figures, we can utilize a query in MS Access. I am a firm believer in the ability for MS Access to facilitate moving data between different systems.
PowerBI
In this example, I will be using an existing template I have previously discussed (follow this link to the Construction Progress Reporting post).
Where reports in PowerBI fall over, is that users have a difficult time actually being engaged as managers of the data. We do not have an easy ability to provide context or comments to specific data elements.
Here, we can immediately see that we can interface this dashboard with our SharePoint list. In our PowerBI queries, we can link to the SharePoint list.
As our schedule data is unique per ScheduleID, and our SharePoint list is unique per ScheduleID, we can link these 2 tables together and pull the comments into our table.
The resulting comment can the efficiently placed on a custom tooltip.
Extensions
As with any comment, it is important to include an indication of criticality. In the above picture, we don’t have an indication if a comment exists, and if a comment does exist we do now know if its important. Therefore, in our SharePoint list, we can use an extension to insert a traffic light in the cell. Then on the PowerBI visual, a traffic light is displayed using a small, colored circle. This would allow for quickly glancing at all the activities and being able to quickly drill into a critical comment.
This is different from looking at Total Float or Variations. Typically on-site, various activities have issues for various reasons that may not have anything to do with float or variances. These may be risk-related issues we are trying to prevent, or perhaps gets others to understand. This approach to comments is exactly what can lend value to a project.
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
Financial Status
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
Yes, my secondary critical path finishes after the first – gotta love random data!
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!
Safety
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
Progress
Cost
Schedule
Narrative
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.
Progress Data
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.
Cost Data
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.
Schedule Data
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).
Narrative
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
A Dashboard?
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.
Raw data captured
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.
CONCLUSION
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.
Whats Next?
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
A key quality of project controls management, is the communication of major milestones. The whole point is to review the changes of all the dates over time.
A key quality of project controls management, is the communication of major milestones. Every lead will have several spreadsheets with all our milestones listed on rows, and columns for the various interpretations (baseline, contract date, prior forecast, current forecast, contractor forecast, etc). So when we talk about dates, the difficulty is that everyone will have a different date in their mind. Continue reading “Major Milestone Tracking – By Darrin Kinney”
 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.
Small Data And self service 