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

How to Build a Level 5 schedule using PowerPivot

Download the file Here, keep reading, if you want to see how it works

There is no industrial standard that define rigorously the levels of schedules, every company has it is own definition, but for the purpose of this post, the level 5 is at the tag level.

From working in different projects, I reach the conclusion that regardless of how much you details your schedule, you will have always some stakeholders that wants to see a more detailed schedule, or wants a different view, some examples:

  • The commissioning guys wants to see the construction schedule by subsystem.
  • The field supervisor, want his own scope by tag with physical quantity to install.
  • PM wants a level 2 by SP and phase ( construction, Precom & Com )

a planning software can easily accommodate those requirement using activities code, you can always make new layout and groups by activity code, but some request are impossible to satisfy unless you start breaking down your activities to the lowest level possible, which I think is counterproductive and may lower the schedule quality if not done properly, and put a lot of pressure on the planners that need to maintain this level of details.

Goal

Taking the “pressure” from P6, but produce multiple Gantt chart report with different granularity to satisfy our customers 😦 Management, client, the guy in the field etc.)

Solution  

the solution involve using Excel to mashup the data from Primavera P6 and Cobra and generate Gantt chart and S curve by any grouping possible, notice here how the conversation shifted from ” it will take a lot of work in P6″ to ” tell me how do you want your Gantt chart”.

P6 and Cobra are used as an example, it can be used with any other tools, the data transfer is using Excel files, so anything works as long the proper codes are used, as a matter of fact I don’t have access to cobra at all,all I need is an export.

architectures

How it works

Export Resource spread from P6

Export the remaining labor units from P6 per day, the reason to use days, so can group later using different calendar ( week, financial period, calendar month etc.).

export_p6

Make the export database friendly

P6 spread the data horizontally by default, this format cannot be used as a source for a database, we need to make the date as list.

export_p6_unpivot

Now the date are “vertical”, next step generate Forecast_%

Forecast_% = (remaining labor units by day)/ (total remaining labor units)

Here we go

export_p6_unpivot_normalized

Notice here, the sum of all the forecast % per activity id is always 100%

(1.2 means the field’s data type is decimal)

Now we have a proper Table that we can load to the data model, I used to do a lot of crazy tricks to have the same results using the tradition Excel approach (VBA, formula) but now with powerquery it is one click J

Import the MTO from COBRA

You need the detailed breakdown, no aggregation, with all the dimensions (tags, pay code, subsystems, separable portion, PCWBS L1,L2,L3, weight, etc.), the point is:  the MTO will be always more details than P6 and that’s the way it should be. Don’t try to replicate the same level of details in P6, it does not work. (at least that was my experience),

Here is a sample format (dummy data for presentation only, in big project, it can be millions of lines)

mto

Mapping between COBRA and P6

Ok that’s the hardest part, and will not be covered in this post, in Cobra for every line item you need to assign a P6 activity ID, no compromise here, it has to be 100 % done.

Link the MTO to P6 Spread

We use P6 activity ID as the lookup code between the two tables, and again with one click in PowerQuery it is done (try doing a vlookup with a 300 000 line item MTO)

merge_1

merge_2

We have now a table that have the level of details of the MTO and the spread from P6

mto_spread_tables

Unfortunately many people still think it is Excel, they miss the fact we are talking about a real database working behind the scene, here an example from an actual project

mto_spread

Generate the dimensions tables

The dimension table is how you want to slice your pivot table (BI people will cry at this explanation), in our case we want a Gantt by tag, subsystem, wbs, drawing etc.

It is trivial to get a dimension table in Powerquery, select a column, remove the rest, and remove duplicate, the key should be unique

Here an example for tag, the rest is the same method.

 dimension

Link the data in the PowerPivot Data model

Load everything into the data model, and link using the common keys

A simple straightforward star schema

data_model

Add measures

Let’s add two basic measures here, forecast qty and forecast hours

measure_rem_qty

The beauty of the measure is that they automatically change, when you add new dimensions to the pivot table, in this case, the formula here is the same as using sum product in Excel (please google the difference between sum and sumx)

Generate the chart In Excel

The Gantt chart will be generated In Excel using Pivot Tables, with conditional formatting to simulate a Gantt chart, the end users can slice and dice as they wish, you can add many combination as you want, and trust me, people will ask for some combination that you never thought about before, the way a field supervisor want to see his Gantt, is not necessary the same as the planner expect a schedule to be organized,

A supervisor want to filter his Gantt by drawing number, go do that in P6?

gantt_chart

Just for fun, Construction Gantt chart by subsystem

gantt_chart_subsystem

Conclusion

Using PowerPivot as a reporting tool can offload a lot of pressure from P6, and keep the different stakeholders engaged with the schedule as they get customized views.

Keep P6 for what P6 is good at, critical path, date, spend that time on making the logic sounds, the duration that reflect reality and let reporting be done in a better suited tools.

Edit 27-04-2017 : if you don’t have the spread from P6 and all you have is activities start and finish, you can use this approach