Why the DCMA 14 Point Checks Matter
Usually when I review a Primavera P6 schedule, I use the excellent reference by Ron Winter, or at least a subset of all those checks. I am probably being biased here, but for the Primavera users community, no one would argue that actual dates in the feature are bogus, or why negative float is a no go, or why constraints are evil and dates should mostly be driven by logic. Usually a Primavera schedule log can tell you a lot about the quality of the schedule.
Then the unexpected has happened. I had to deal with MS Project file for a new assignment. I was quite concerned about the best way to approach a PM that used MS Project exclusively, and tell him, maybe there is a better approach and although MS Project permitted incomplete work in the past, it is still nonsense.
Fortunately there is the DCMA 14 point checks which addressed my exact situation, as a Primavera P6 user, some metrics does not make any sense, Ron again :), has published a review of DCMA 14 from a Primavera expert perspective. For me the metrics themselves are not that relevant, what’s important is to have a common ground where you can talk to someone who has a different approach to planning according to the way their preferred planning software works.
I am quoting here a passage from the DCMA 14 point checks training manual, which better explains the purpose of the metrics.
‘The DCMA 14 Point Schedule Metrics were developed to identify potential problem areas with a contractor’s IMS. These metrics provide the analyst with a framework for asking educated questions and performing follow-up research. The identification of a “red” metric is not in and of itself synonymous with failure but rather an indicator or a catalyst to dig deeper in the analysis for understanding the reason for the situation.”
Fine, but I only use Primavera P6, why should I care ?
Fair enough, why should you care if there are already superior alternatives in the industry ? In my humble opinion, the fact that Primavera includes the metrics in their EPPM product will reinforce DCMA 14 as an industry standard, and it is imperative to understand the deficiency of the standard in case one day you have to deal with an overexcited client representative that may reject your schedule simply because it failed one test. (Again that’s not the purpose of metrics).
Introducing XER Reader
XER-Reader is a free Excel utility that reads a Primavera P6 XER file and dumps the data into Excel to generate:
- Project Statistics
- A Gantt chart
- DCMA 14 metrics for your schedule.
Before you start, please understand that XER Reader is just a personal project, there is no guarantee whatever that the results are correct, or it does even work for your particular windows installation, the utility is free to use and the code is released under an open source license.
You can view the wiki with more technical details here, New revisions will be issued regularly so if you want to report bugs or suggestions, make sure you are using the latest release. For bug reporting, send a screenshot of the error and your system’s description (32 or 64 bit), and I will have a look and try to fix it.
How to use the XER-Reader Spreadsheet
Step 1 – Activate the macro
When you open the excel file, make sure you enable the content, the screenshots were done under excel 2007, so for other versions it make look slightly different. Basically you are allowing Excel to run the code in the macro.
Step 2- Open an XER file
Click on the button “Load an XER file”, select the file you want to analyze.
The macro will generate three new Tabs.
2.1 – Metrics in the “dashboard” Tab
Only the first 10 Metrics of DCMA 14 are included. The rest ( Missed Task, Critical Path test,CPLI, BEI) either require a baseline or should be done manually within the planning software.
The DCMA 14 Metrics count only “Uncompleted Tasks” , so Milestones, Level of Effort, WBS Summary Activities and completed Activities are excluded from the analysis.
I added a separate section for Milestones. Please note that Milestone are not included in the DCMA 14 standard, but in my view excluding the milestones is wrong.
2.2 – A detailed report by Activity in “Details_report” Tab
2.3- A WBS view in “WBS” Tab
2.4 – The list of tables imported from the XER “Table_Summary” tab.
When you click on the table name, the data will be shown.
Step 3- Draw a Bar Chart
When you click on the button “ Draw Bar Chart” , the macro will generate a Gantt Chart and add columns for Labor units, Performance % complete etc…
Step 4- Deleting Data
If you want to delete the XER data, click on the button “ Clear the XER”, the same goes for wanting to delete everything including the reports; delete “Clear the reports”, please note that if you delete the XER data, you can not generate the gantt chart unless you import the XER data again.
Initially the spreadsheet was for MS Project, using only formulas and include extra “ features” that the software permits but make no sense whatsoever (like manual task mode!!!, and constraints for summary Task). Then after a random search I came across this article, although I am quite familiar with MS Access, I never knew it was possible to use the jet database engine with Excel to develop a database application.
Although technically it is much easier to write a macro for MS project, because VBA is already included (that’s one of the strength of MS Project); I was more interested in Primavera (because that’s what I do for living), as I am more familiar with the XER format. A good example on how to load an XER is already provided in the XER parser and builder utility (although I am using a different code, as it is much faster).
Once the tables are loaded into Excel, the rest is a bunch of SQL Queries, so why limit to DCMA metrics? Let’s add a gantt chart and some Earned Value Management features.
Please note that some data is very easy to query from an XER file like budget hours, actual hours, remaining duration in hours, etc; as they are stored in a simple list of records, others not so much. For example, WBS (Parent node, child node), calendar (a blob with xml like structure).
You might ask, why no S-curve? The answer is simple: the spread is not stored in the XER. It has to be calculated, which involves some crazy code (hint: extract list of non working time from the calendar that may or may not be inherited from global calendars and then apply it to start and finish dates of tasks, taking into account resource curves – if they aren’t linear! And don’t forget the suspend and resume dates.
You don’t need to be a professional programmer to use VBA for EXCEL (I am not by the way), If you already use Excel, you are already familiar with the basics. There are plenty of free tutorials and resources in the internet, and the community is very friendly and helpful.
Maybe it is time to start that macro recorder, and don’t be intimidated by some obscure technique, I am still trying to grasp those recursive algorithms.
For now, enjoy using XER-Reader to assess your schedule against the DCMA 14 point checks. I hope it helps you improve your schedules.