Data Integrity and Primavera P6: Detecting Problems Before They Cause Damage

Quality Assurance is a group of methods and processes ensuring that the quality of any work or creation matches established standards. In the project management world, many of such norms exist and are implemented on a daily basis.

But enforcing standards for schedules is only one part of the problem. While at Emerald we released a tool known as the P6-QA Tool to drastically increase the quality of P6 schedules, we also looked into a lower level aspect of the problem and came up with conclusions and solutions.

What is it worth to have the best schedules if the data is corrupt and inconsistent for technical reasons? Yes, it happens.

I’ll discuss here how back-end specialists can detect and fix these problems before the end-user phone call: “Hey, do you know what this error is?”

Prevent External Data From Corrupting Your Nice and Neat P6 Database

One thing that makes collaborative work with P6 rather convenient is the XER import/export feature. In a few clicks, this functionality allows transferring projects activities, resources, etc. from one database to another. But it may also contain hidden inconsistencies causing issues once imported.

Issue 1: Huge Amount of POBS Data in the XER

We discussed this already here. POBS data is evil. Fix the XER before it gets in (with our POBS cleaner, for example), and you’ll save yourself some hassle. Implementing a standard procedure enforcing automatic cleanup is what we recommend.

Issue 2: Broken Nulls/References in the XER

For this issue, Oracle came up a while ago with an XER parser utility to diagnose and fix some of these problems. After the XER is clear from any POBS data, we recommend downloading it and using it to make sure the XER is safe to be imported.

Issue 3: Broken WBS in the XER

For this particular – and serious - problem, we came up with our own solution. In some specific scenarios the WBS hierarchy of P6 projects is broken, and once imported, the projects are unusable and must be deleted. To diagnose – and fix - this issue, we integrated a detection algorithm to an advanced version of our POBS cleaner. It recently allowed us to move on with one of our clients and save a tremendous amount of their time when working with dozens of corrupt XER files.

Detecting Issues in Your Own P6 Database

When these issues happen directly in your own database, it is very important that they are fixed as soon as possible to prevent any general failure. Make sure you run the following checks on a regular basis:

Oracle’s validate.bat Tool

This tool comes with p6 (it is located in the “database” installation folder) and runs surface checks to make sure that, for example, the database can be migrated to the next version. It is a quick test and a very good idea to run it on a regular basis.

Oracle’s Data Integrity Queries

Oracle released 21 queries to run against your database, which check for advanced and more serious issues. Each of them comes with a matching counterpart query to fix issues previously detected. It is however a bit tedious as every single query must be run manually. This is partially why we came up with a more elegant solution…

Emerald’s P6 Data Integrity Tool

This tool not only includes the XER integrity checker I was talking about in part 1.c, but also runs all Oracle queries (see point 2.b above) and a few additional exclusive Emerald checks to make sure your database is as neat as possible. This tool not only provides the advantage of doing it all at once (and helps in fixing issues), it is also constantly updated with new findings, and with it, your corporation benefits from the latest p6 data quality checks.

Summary

As usual, detecting problems before they cause damage is the scenario we all wish for. With this set of quick steps, you can make sure your schedulers are working on a reliable database and avoid disastrous losses.