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!

Advertisements

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.

Major Milestone Tracking – By Darrin Kinney

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”

Progress Visual using D3, JSON, APIs – by Darrin Kinney

When we look at our project management world, we have to assume that each project will have its own website. These project websites can be leveraged for so much more, specifically displaying progress information. Continue reading “Progress Visual using D3, JSON, APIs – by Darrin Kinney”

Using Or Conditions between Slicers to filter Primavera Schedule using DAX

The Interactive report is published here,  you can download the PBIX here.

In PowerBI or Tableau or any BI solution by defaults when you have multiple slicers, the filter conditions is always, AND, in this particular case, the user want to filter a schedule based on a start date  OR the end date OR the activity status, basically how to reproduce the following Primavera Filter using DAX

Primavera

 

 

Using the normal slicers will not work as it will simply show the activities where all the conditions met, instead we will use some DAX techniques (disconnected slicers and filter using the result of a measure) to get a new behavior.

 

Let’s build a simple data mode.

1-The main table is task: it contains Activity ID, Activity Status, Start, Finish

2- add a date Table, Start_Date, don’t link it to any table, use it in a slicer , the table is generated using Powerquery,

3- add another Date Table, Mstdate, don’t link it either, use it in a slicer

model

 

4- add Activity status Table, don’t link it, , use it in a slicer

5- add this measure

Filter_measure = CALCULATE(COUNTROWS(task),

FILTER(task, ([Start] <= LASTDATE(Start_Date[Date])

&& [Start]>= FIRSTDATE(Start_Date[Date]))

||

([finish] <= LASTDATE(Finish_Date[Date])

&& [finish]>= FIRSTDATE(Finish_Date[Date]))

||

IF(NOT(ISFILTERED(‘Activity status'[Activity status])),BLANK(),’task'[Activity status]IN ALLSELECTED(‘Activity Status'[Activity Status]))))

 

Add this measure to the matrix visual as a filter only, and make it not equal null, and voila

result