Level 1 Reporting – Source Excel Data – By Darrin Kinney

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

L1_01

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

L1_02
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

L1_03

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

L1_04

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.

L1_05.JPG

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.

L1_06.JPG

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

L1_07

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.

L1_08.JPG
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)

  1. Unpivot the Timephase date columns
  2. Pivot the the “SeriesName” column to create a unique “Column” for each dataset (this is need to create unique lines on our dashboard graphs)

L1_09.JPG 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.

L1_10.JPG

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

 

Advertisements

P6 Date Formats – Quick Data Hacks!

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.

XER_date_format

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

 START=IF(ISBLANK(act_start_date),early_start_date,act_start_date)

FINISH=IF(ISBLANK(act_end_date),early_end_date,act_end_date)

 

Problems with P6 format (copy-paste)

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!”.

Start Finish
22-Oct-18 A 9-Sep-19
09-Sep-19*
09-Sep-19*
10-Sep-19 10-Sep-19
05-Jul-19* 10-Sep-19
27-May-19 A 10-Sep-19
03-Dec-18 A 10-Sep-19

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.

The easy fix I use: Scrubb_Data().

Sub Scrubb_Data()

Dim xChars As String
xChars = “*”

Sheets(“P6_CURRENT”).Select
ActiveWindow.SmallScroll ToRight:=3
Columns(“V:V”).Select
Selection.TextToColumns Destination:=Range(“V1”), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:=xChars, FieldInfo _
:=Array(Array(1, 3), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True

End Sub

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.

Capture

Conclusion

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!

Digital Strategy (the beginnings) – by Darrin Kinney

Project Controls is the heart of every major construction project. Our teams are the ones that typically wade through information that never stops and constantly changes. Although at the core, the fundamentals of our strategy are likely the same as what everyone who manages data will encounter.

Digital Strategy for project controls is a tricky beast. We are bombarded by systems that all promise nirvana (and many can deliver nirvana for some projects). However, at the core, before we even discuss systems, we need to understand what our strategy is, and what key concepts we want to push. Below are some ideals of mine. With everything, this is a straw-man discussion. Every project will have its own culture, its own focus, its own needs. Your strategic approach has to cater to what you want from your project. Not to mention that the major companies all have quite rigorous project control plans and detailed procedures. These are great documents that do spell out out scope of services.

Project Controls is the heart of every major construction project. Our teams are the ones that typically wade through information that never stops and constantly changes. Although at the core, the fundamentals of our strategy are likely the same as what everyone who manages data will encounter.

The issues are not related to our knowledge of project controls (and project management) fundamentals. We know how to build schedule, track progress, build baselines, budgets, and track costs parsed into 1000s of potentials contracts and POs. We know how to calculate and track Earned Value, CPI, and SPI. We know change management, trends, change orders, etc. We know how to compile all the information into weekly and monthly reporting. We have the procedures and management plans.

We are the technical experts of our discipline.

However, too often, we are not able to step back and understand that the way in which we work can change, needs to change – and more importantly – the technology that is available now can be utilized to seriously disrupt the way in which we apply our technical knowledge and skills.

Below are some ideas, some concepts we can consider in everything we do to perhaps bring a more digital and smart way of operating in the new world we live in. These are not innovative in themselves, we live with these ideas already. My intent is to try to capture these ideas and through the tactical implementation of these ideas bring real innovation.

Strategy – Strategy is not Keywords

Too often, we write our strategy or integration documents and load them up with keywords and slick powerpoint. We have to embrace that we touch real things. Don’t use stick figures and cartoons  – use real live examples, get your hands dirty using the systems on your job. Understand the details. Stop writing keywords on a white board and open the application!

Digital Strategy Keywords

The real idea is to be grounded in practical things, not abstract processes.

 

Strategy – Enter data once

This is talked about by everyone, although, we have all seen it – everyone has their own spreadsheet. Every group has their own systems: SAP, Contracts, Procurement, Engineering, Cost Control, Planning: I have illustrated in the past the issue with something as standard as “where is the master contract list”. Do we really have a strategy to enter data once? If so, really delve into the data people keep on their computers in their own spreadsheets. Try as best as we can to pull key reference data out of a source system

There exist systems that can facilitate this approach and allow for integration. Although be careful and understand that integration is not strategy. The implementations of strategy will likely require integration.

Data Integration vs Digital Strategy

 

Strategy – Be Visual

Project Controls teams are the masters of reporting. Being visual is not about (only) creating dashboards or complex databases the feed our dashboards. Being visual is doing exactly what we already do. Stay away from data tables – Long Live Death by S-Curves!

The tabular data presentation methods we use are antiquated. Looking at an engineering progress report with 50 rows and 20 columns of various EV metrics – too often that doesn’t answer me the question of whether or not I am on plan or not. That is what I meant to be visual. Look at the questions be want answers for, the core underlying questions and FOCUS. If the question is infact “I was to see the productivity factor for 50 different areas both weekly and cum”. Perhaps choose a dot-plot. Place the values all on a graph, perhaps scale the size of the dot on the budget hours, use a horizontal axis for % and the vertical axis to show the PF. That is what I mean to “Be Visual”.

 

Strategy – Push processes and data ownership to the person actually responsible, not the “function” that is responsible

Payment certificates, progress measurement reports, monthly reports: the list is never ending. We have data that originates with contractors and a multitude of sources. However, too often, the sources of information do not have access to the real database  or word/excel file used by the project. If possible, structure your data sources such that owner can manage their data.

I will likely post a few case studies about this to get people thinking of possibilities.

 

Strategy – Use Agile management

  • Focus on Output not Documentation
  • Respond to Change as opposed to fixation on Original Plan
  • Focus on interactions between people, not underlying systems or tools

These topics can be very disruptive: Tell a company like Fluor or Bechtel to NOT focus on plans or procedures? Good luck. However, this is a disruptive day and age and we do need to take this approach. I have worked in this business for over 20 years and honestly, in general, project controls already (generally) follows these agile fundamentals. We have plans and systems and reams of documentation, but in the end, we do (informally) have to follow these simple strategic cultural elements

What is needed here is the firm support from the management team that a proactive view of the

“what is going to happen tomorrow”

is way more valuable that the concept of

“what was planned to happen tomorrow”

The concept that

“I need this new report now”

is more relevant than

“This is the report that is defined in the PPM”

AGILE management is not able sprints or standups. Its a culture to embrace a stance that while we have general goals and objectives, the focus needs to be on what are we doing today. The reason to have daily stand ups is to have clarity on what we are doing today. The reason to have sprints is to outline near term goals. The reason we have restrospectives is to analyze the difference between what we thought we were going to do, and what we did (and more importantly how to improve the next period). This is just good project management 101! The fact someone calls it AGILE, is not relevant.

 

Strategy – Allow users to LINK to key fundamental data

This is aligned with “enter data once”. But, this is a core strategic ideal that needs to be championed. We will have data housed in source systems, but have we enabled the wider project team to access this information. That is the primary idea here.

Is information shared by way of excel exports that are distributed via email to various people at various times? Or, do we have a project where information is open and accessible. The latter is what our strategic approach to everything should be.

 

Strategy – Facilitate live real time data

Ultimately you will need some sort of approach to cadence and integration. Will we be using real time data, will we instead focus on cutoffs.

This can if we really delve deeper into what this really means, be very disruptive. With real time data, the need for monthly reporting is useless, the need for weekly reporting – is useless. Reporting is a natural output 24/7 from the way we operate. This cultural approach to construction management is where I think we need to go to be hyper reactive to change.

 

Strategy – Be Hyper Reactive

As I mention above, we want to have a core ideal that allows us to change course immediately when required. We have submitted the same schedule and cost reports every month – WHY? Do not be afraid to change things up, support a workplace where the team can be hyper reactive to respond to everything.

For schedule management, this requires the approach to insert activities into the schedule with daily vigor to reflect what is happening today.

For cost management this requires detailed ticket management approach to our tasks to allow visibility into everything we do 24/7

 

Conclusion – Do not be afraid to push boundaries

This list above is again just a beginning of a discussion. These are discussion topics that are occurring in every company around the world.

As we head into the future, we have an entire generation of new employees who are not afraid of technology. Quite the contrary, the newer generations of people will feel more comfortable updating a web form vs updating a word file. They will feel more comfortable posting progress updates to a social media site vs writing a daily report. They will be more comfortable making mistakes, and fixing them.

The dashboard craze is here to stay. The use of dashboards and the approach needed to create PowerBI or other application based analytics tick many of the strategic elements I have outlined here.

In the end – do not be afraid to push boundaries with your approach to construction management.

Create a PowerBI Resource Analysis Dashboard – by Darrin Kinney

Feel free to provide comments directly to my LinkedIn Post that references this article and contact me directly – Darrin Kinney

Analyzing  resources in P6 is a common responsibility of all planners. However, ultimately the data typically stays in the realm of the planner, and never properly given to the specific project managers and project engineers who actually need to execute the work. Thus, there exist a huge opportunity in the digital world to extract data from P6, and present it to the masses. Planners are penultimate professionals in this. We live for this. We take the resource assignment data from P6 and work our magic in excel creating a suite of reports and graphs (s-curves).

However, all the excel work is customized. The ability to quickly drill into the data from the data table or the graph is just too difficult for management. The need to create custom graphs, takes time. Instead, we can create something quite amazing using PowerBI.

Using PowerBI we can create a simply easy to use dashboard that provide nearly unlimited flexibility to display both schedule and resource information to any user on a project. The below is a walk through to create a simple view with a simple schedule. However, I have run this through a schedule with 5,000 activities and multiple resources including both manhour resources and quantity resources. It is situations where you have data overload that PowerBI shines. Get the data out from the planner, and into the hands of the project management group.

Step 1 – Get resource assignment data

Using the resource assignment tab in P6, remove all the grouping and just display everything. You will want to ensure some key fields are available: Start Data, End Data, Resource Data, Resource ID and Type, Activity Name and ID, plus some WBS and grouping data (ex contract).

RA_2_P6_resources

Step 2 – Copy-Paste Resource Assignment Data into Excel

A simply copy-paste from P6 into excel will suffice. It is really that easy. Sometimes the data from P6 may not contain the right descriptions, so this is an opportunity to use some vlookups (or Index(Match()). The data also needs to be presented in a table. Select All and use CNTL-T to convert to a table.

RA_3_excel_table

Step 3 – Unpivot Data using Power Query

This step can be done directly inside PowerBI Desktop, or in perhaps a more flexible excel environment. I prefer to do as much data handling in excel to reduce the complications once inside PowerBI; however, many options exist.

Inside Excel (ensure you have power query add in), select the columns with the dates, and click “UnPinvot Columns”. Close and Exit.

After running the unpivot, you will see each data column turned into a row. This is an easier data format for use in databases and a trick universal not just for powerBI, but anytime you might be dealing with databases.

RA_5_powerq

Step 4 – Import Into PowerBI

PowerBI has very quick and easy import routines. This post is not meant to be a specific click by click guide. There are easy import routines you can find to import excel files. Again, as indicated above, just make sure your data is converted into a table. You can directly create a datasource using something similar to the below

=Excel.Workbook(File.Contents(“C:\Users\Name\Downloads\P6_resource_Assignments_HP.xlsx”), null, true)

Step 5 – Create your PowerBI Elements : Gantt, Graph, and Slicers

GANTT

The PowerBI file uses a custom visual you can download for free from the marketplace

GANTT by MAQ Software

The parameters you will want to use are roughly as noted below

GRAPH

The PowerBI file use AREA CHART. This is an easy to use built in graph feature of PowerBI. You can use potential extension of line graphs to show a % Complete curve. However, from a base usage, the area chart provides the easiest visual.

A nice trick in the overall dashboard is to align the data range of the GANTT with the GRAPH. I have found some limitations to this exist, but in this example, it worked quite nicely.

 

SLICER

The real power of this visual is through the use of slicers. Slicers allow for immediate filtering based on a variety of selections. This visuals uses a range of filters for Contract, Facility, SubFacility and ResourceID. Depending on the structure and data you extracted from the P6 resource assignment, your options here are unlimited.

 

Conclusion

This example just scratches the surface of what is possible. I found the above to be immediately useful to our project team in clearly visualizing the resources required over time. There is some manual effort to keep the source excel updated after changed to P6 – this is not a live linked datasource. There are numerous possible development extension people can use to customize this.

The biggest critique to the dashboard is the lack of visibility into the Baseline dates and plan curves. For this, we can only hope someone builds a more robust custom visual to accommodate this. In the end, happy planning.

RA_1_Final

Using Powerquery List.Dates to generate time phased Budget and account for holidays

There are plenty of templates on the internet on how to spread a budget using Excel formula, in this blog post we will use the Powerquery function “List.Dates”, and a calendar table to account for holidays and periods with less working hours, for example under extreme weather conditions, the working hours per day will be less than standard working hours.

Obviously Planning software handle this scenario very well, but contrary to popular belief, Excel is still the most widely used planning software among non-planners. (I have yet to see a cost controller or QS using a planning software, even MS Project).

The function is straightforward:

List.Dates(start as date, count as number, step as duration)

In this case, count will be the durations in days, and step will be 1 day

Let say you have a list of tasks, with a start and finish date, and some kind of budget, (cost, revenue, quantities, Effort required etc).

Budget_0

Let’s load the table Task and generate the spread.

  • First we calculate the duration ( Finish-Start)
  • We use the List.Dates to generate the list of dates between the start and finish of every task.
  • Expand the list to become a column.
  • Merge the list of date with the calendar table to get the working hours per day.
  • Now we sum the total working hours per id.
  • The % by day = working hours per day/ Total working hours per task.

Budget_1

Voila, you have % per day.  Now to spread the budget, just multiply the budget by % per day.

Then we load some dimensions table in order to slice and dice the pivot table and WE add a measure to calculate cumulative Budget time. As we are using a calendar table, we can show the budget per day, Month, year or any time period.

Here we got a nice little Star schema

Budget_2

 

 

 

Now a Pivot table with conditional formatting will show a Gantt like chart.

Budget_3

And a pivot chart to show the Budget per Period and the cumulative.

Budget_4

You can download the template here, you need Excel 2013+, if you don’t have it, you can always download PowerBI desktop for free, and import the Excel file, but you need to redo the charts though.

 

Integrated Project Control system using PowerBI

One of the most popular discussion in planning forum is how to have an integrated project control system, every practitioner has a different opinion how it must be done, and of course you get a lot of marketing from people trying to sells their systems

In this blog, we share high level description of a data warehouse built using PowerBI desktop.

Data warehouse are not widespread in the construction industry, because the reporting specifications are different from project to project, and every client has a different systems and tools, and trying to have a standard system works only if you are the main contractor but if you are a subcontractor you have to adopt the client system.

Another reason is; it required a specialised IT skills, we are just business users not programmers, we do understand data very well, but not necessarily having the skills and tools to manage it, The good news is, with the rise of self-service Business intelligence, we have exactly that, Powerful data management tools yet accessible (assuming you want to learn something else than Excel).

So at high level this is how it works.

 Project Controls Data Warehouse

  • The Data warehouse was built using PowerBI desktop, I know it should be called sematic model, (for me data model, data warehouse are fundamentally the same thing), initially it was using Excel PowerPivot but it did not scale well with the increase of the volume of data.
  • As the data is not always in the format we want, PowerQuery is very handy in this case, as virtually it can transform any source of data, example lookup the subsystem using the tag field, trying to do that if you have 8 Million rows using Excel or Access is not feasible.
  • We maintain Master tables to integrate all the different source of data (tags, WBS, subsystems etc)
  • Every week, we get new Export from the source systems (Cobra, proprietary database systems etc), we load the new data and keep the historical records, it took 15 minute to refresh, which is quite impressive, Cobra alone is a folder 60 Excel file, and nearly 2 Giga in size.
  • Usually you publish your reports into PowerBI.com service to end users once your refresh your data model, in our case we can’t use the cloud for privacy reason, instead we use Excel as a reporting tool that pull the data from PowerBI desktop, the advantage of this approach is that we have different reports for different users, Skyline, Gantt chart, Client reports (in their required format), management reports etc.

Some thoughts.

  • As you can see Primavera P6 is used only as a forecasting tool and to calculate the critical path, the earned value calculation is done in the data model, personally I think P6 should not be used as the centre of your project control system, I remember the first time I start learning Primavera P3 ( a long time ago:), we kept asking the trainer how it is possible to track the spent hours at the activity level, the answer is we don’t, actually deciding at which level you track you spent hours it is the most important decision to make when you start a new project.
  • the basic idea here is in order to have an integrated project controls system is stop trying to have one, data will be always in silo, don’t try to change other department how they manage their specific data, it will not work and they will not listen to you anyway, So instead of trying to have one system to rule them all, just use the existing systems and build a data warehouse for reporting and Integration with P6.

How to import Progress to Primavera

It is quick post showing how to import activities status to Primavera using Excel.

Cobra is as an example for a progress measurement system, it can be any other systems.

In P6 always use new template, never trust an old file as things may have changed in the meantime and you end up with discrepancies.

Assuming the schedule is resource loaded, you need first to import

  • The new activities status ( actual start, actual finish, remaining duration )
  • The new resource status ( Budget hours, Earned Hours, at completion hours)

You need to import first the activity status then the hours.

  • Export activities status from P6

Make sure you select Export type, Activities

0

The Activity ID, activity status and WBS code are selected by default, you need to add Actual start, Actual Finish and Remaining duration

1

P6 will export an excel file LIKE THIS

7

Now you can transfer the progress data from  COBRA, using the activity id as a reference, don’t change the WBS code.

In some cases you may let P6 calculate the remaining duration, personally I prefer to do the calculation outside P6, as it is more flexible, for example you can calculate remaining duration based the actual productivity, which is very easy to do in Excel

Import back to Excel, Make sure to save any error message

  • Import the hours

The same approach, make sure you select, resource assignment

2

3

8

Make sure to update only the column (Budget units, Actual unit, At Completion Units),

The activity status cannot be updated from this template that’s why you need first to import the activity status and then the resource status.

Note: if the activity status is completed, make sure actual unit = at Completion units.

P6 can assign multiple resources to the same activity, so make sure you don’t have duplicate resources assigned.

Why you must automate the whole thing ?

As you can see the import to P6 is rather straightforward, probably you are asking why I am blogging about it in the first place?  And the reason is how do you know your import was successful?

Ok for the hours it is easy, if the total hours match, then the import was probably successful, but what if the total hours do not match? Or how do you know the activity status is updated correctly

One approach is to design a system for example in Excel , Modern Excel or Access that do the following.

  • Automatically map P6 activity ID to Cobra.
  • Generate the activity status and compare it to the current P6 status
  • Import the new status to P6
  • Redo step 2 and 3 until the status in P6 match exactly the status in Cobra.

Now if you are spending too much time manually updating P6 ( we all done that), I think it is unfortunate, and honestly it a waste of time better spend in analysis the schedule, ok it is easy said than done, and there are many technical and non technical roadblock to have such a system in place, but I think the most important one, the Planner responsible for the initial preparation of the baseline is more concerned to have his schedule approved than to worry about how the schedule will be maintained later. ( usually it is other planners problems).

but if you stick to this general rules, probably you will do fine

  • Only use, contractual Milestones, Access Milestones and resources loaded activities.
  • Schedule activities should never be more detailed than your Bill of Quantities
  • if you can not measure a task, don’t use it
  • don’t use level of effort just for presentation purpose.

Edit : 17-04-2017, for complex project, we just use one generic resource to load the total hours and earned hours per activity,  trying to load individual resource type is not feasible  and become a nightmare to manage and honestly even getting the “spent Hours” at the activity level is not possible,  that’s why I think Earned valued and productivity analysis should be done outside P6