Are You Importing Unwanted RISK TYPES?

Remember our good old friend the POBS Table? Well, we have a new friend in town that is introducing itself to our database in the form of the RISKTYPE table. We have discovered numerous clients are importing XER files to their database that include a large number of Risk Categories, sometimes tens of thousands of them. No one knows where they originated, but they are multiplying and wreaking havoc.

The problem comes when an export file is created with unwanted Risk Types and imported into another database, creating more values in the destination database. The destination database then could share their large number of Risk Types to another database. Each time, the RISKTYPE table is passed along, it grows, spreading and infecting more and more databases.

But is this really a big deal? Yes, it is! There are typically two issues with the Risk Categories: 1. 1. The table contains circular references which cause P6 to crash 2. Gibberish characters appear in huge numbers of Risk Categories and the client has no idea where they came from. The cause of the circular reference, according to the Primavera Knowledge base, occurs when a parent risk_type_id in the RISKTYPE table references a parent_risk_type_id of child Risk_type_id causing a circular reference to itself. When Risk Categories are created each risk category can have an associated child risk (parent/child hierarchy). For Example:

In the database, in the RISKTYPE table, each record has a risk_type_id and parent_risk_type_id where the risk_type_id is a primary key and the parent_risk_type_id is the risk_type_id of the immediate parent. If the parent risk_type_id references a parent_risk_type_id of it's child risk_type_id then this will cause a circular reference to itself and an error will result.

The second issue is caused by an invalid special character (boxes, diamonds and other non-standard characters in the expected language) in the RISKTYPE table. This was the case with our client, who had unknowingly imported many of these characters into their database.

This error is a lot like the POBS issue from a few years ago - an XER may contain tens of thousands of these records. This slows import performance and spreads the gibberish characters to everyone who imports the XER. Oracle has acknowledged that corrupt data can be imported from XER files received from external sources, and over time, this corrupt data can cause performance issues with both export and import of XER files. The creation of a RISKTYPE removal utility is being considered for a future release.

In the meantime, there are several workarounds available for the problem.

1. Delete the invalid data from both the source and destination databases. Re-export the XER file from the now clean database and use the updated XER to import into the destination database. Many clients call this “scrubbing an XER”.
2. Request an XML file and skip the risk categories during the import. Note that the XML files are much larger than the equivalent XER files and take much longer to import, so if you are transferring data on a regular basis, XML is probably not a realistic option.
3. Remove the RISKTYPE data from the XER file with a text editor and resave. This is time consuming when there are thousands of records. Also, the risk to doing this is information other than the RISKTYPE could be inadvertently removed, thus corrupting the XER, in which case, Oracle will not provide support. Our recommendation is DO NOT DO THIS.

All of these workarounds involve either time or risk and may not be practical.

Emerald has created a better solution in the form of a utility called the *XER Cleaner* that will easily and safely remove the RISKTYPE data from the XER, as well as the POBS records. The XER Cleaner is very easy to use. Simply launch the XER cleaner, browse to the XER you want to clean and click Run. The XER will be scrubbed of all POBS and RISKTYPE records and the clean file is saved in the same directory with the original file name appended with “-clean”. We have removed over 80,000 RISKTYPE records from an XER file in some cases. The best part about this utility is that it is free to our clients and the whole P6 community. No warranty is expressed or implied.

Contact us for your free copy. If you like this tool, check out our other P6 add ons.