Author: mim

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.

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

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.

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)

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)

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

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

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.

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

Add measures

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

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?

Just for fun, Construction Gantt chart by 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

Skyline charts are very popular in the precommissioning and commissioning phase, as in one page, you can see the remaining subsystems to be done, and how much ITRS to be completed, here is an example.

Excel does not support this chart natively, even power bi,( you can vote for this idea) , some people manually draw this chart in Excel, which is really time consuming or using complex vlookup formula, in this blog post i will share with you a template i built using powerquery and powerpivot, as you would expect that chart is generated with a simple refresh.

what data source you need

for convenience, the data source are included in the same file, i used a dummy data for obvious reason.

Export from your completion system :

Export from your Planning Software :

A Master Table for Dates:

a master table is included as you needed to aggregate the date, there is no point in showing a skyline per day, here i am using week, but you can change the formula to be per month.

refresh

go to Powerquery, show pane, and refresh

some consideration

Although I personally use P6, any Planning software is fine, basically we need only 1 forecast date by subsystem per phase, if the subsystem is spread in multiple PCWBS, then use the Maximum date

P6 here is used only for the forecast date, the actual data ( ITR and completion date) are from the completion system , in some cases, i overwrite P6 forecast date if it is earlier then the completion subsystem cut off date, if P6 does not provide a date for a particular subsystem, I use the latest date from the equivalent system.

in the slicer Phase, select either Precom or Commissioning, if you select both, th skyline will not work properly.

Modern Excel is a term first coined to distinguish between Excel 2007, and Excel 2010 that saw the introduction of PowerPivot and PowerQuery, Excel has become a first class enterprise Business intelligence solution, Millions of row can be loaded, using PowerQuery , data can be linked using PowerPivot interactive, reports are easy to generate using PivotCharts and Cube Formula, and your audience will have more confidence in your reports.

Basically excel become a personal datawarehouse that store and analyze millions of records from multiple data sources.

A sample dashboard In Modern Excel

A “traditional” excel dashboard has no real distinction between the data source and the report itself, everything is mixed together with the results generated by a mixtures of vlookup and sumproduct, and maybe some VBA code, if the boss ask for a different report, you have to start from scratch, but most importantly, it is error prone, if you move one cell, you may change the results without noticing, and there is no obvious way to audit the results.

The Dashboard was first built in Excel using PowerPivot and Powerquery then imported into PowerBI desktop then published to the web.

I could have simply distribute the excel file with the reports but I wanted to emphasis it is not simply another Excel spreadsheet with zillion of cryptic formulas that no one can understand except the author but a proper database solution based on columns not cells.

How it works

Anyone who used MS Access will find modern Excel very familiar, so let see how the steps needed to generate the sample dashboard, it is only a very high level description, not a detailed tutorial.

Data Source: most of SQL Servers, excel, text files, web, ms access, basically any data source you may think of

Data Preparation: PowerQuery is a Powerful ETL Tool for cleansing data and prepare it to be loaded in the data Model, for example if your export the spread of hours from P6, the data will be spread horizontally, in a couple of clicks you can unpivot the data and make it as a table

Data Model: is where the relationship between the table are defined, here is a screenshot from the sample dashboard

Dax: the language to query the data model and define your measures, it is not that different from sql queries

Report and Charts: Pivot table, Pivot chart and cube formula that read from the data model

Here is an example of a pivot table that query from the data model, notice how are all the tables are visible, a classical pivot table will show only one table.

Powerpivot vs PowerBI

It can be a little confusing when you hear all those terms but basically The core technology in Microsoft Business intelligence stack is in an in-memory analytic engine called Vertipaq, the same technology is present in different products intendent to service different market segment

PowerBI : the cloud offer from Microsoft to provide business intelligence for enterprise, it mainly compete with the like of tableau and Qlik, PowerBI come in two editions free and professional.

PowerPivot : in Excel 2010 and up for self-service Business Intelligence

Wrap Up

Self service business intelligence is changing how to deal with data, microsoft has manage to bring a powerful technology to the end users, previously a word like datawarehouse was exclusively a heavy IT investment, now with PowerPivot and PowerQuery you can do it by yourself.