In this second part of Primavera P6 databases and data cleansing series, we will discuss the data structure of an XER file, and how the XER File Parser & Builder works. As the XER File Parser is a manual process (it does not honour P6 business rules), it is handy for the user to have a working knowledge of the P6 database. That’s why in this P6 tutorial we will show a general overview of P6’s data structure, and basic concepts in P6’s database management ( tables, fields , primary key).
1) A Quick Understanding of Primavera’s P6 Data Structure
1.1) How is Data stored in the P6 PMDB Database?
Primavera P6 stores data in a more then 170 tables, every tables contains fields, and every field stores records.
The tables are referenced to each other by relations between Primary key and foreign Key,
For a description of all the tables check this Primavera documentation , unzip the file, then read the file called PMSchema.html.
Fortunately, you don’t need to understand the relations between all 170 tables (whew!). In reality there are only around 20 most used tables that you need really to be familiar with.
Calculated Fields are not stored in Primavera’s Database as they are calculated on the fly based on other stored fields. For example, resource spread data are not stored in the database at all.
For illustration only, here an example of a P6 database diagrams (showing only some tables) that illustrates the many interconnections.
NB: Generated by PL/SQL Developer (using a demo version)
1.2) An Example – Where Data is Written in the P6 DB
We will use a simple example to show how Data you input into P6 client are stored in the database.
Let’s take project codes as an example.
Let’s say you define a new Project Code in P6. You then define values for your new Project Code.
Now let’s see how P6 stores the data in the database with the help of Excel.
The Project Code definitions are stored in a Table called PCATTYPE. Here’s what that Table looks like.
The Project Code values are stored in a Table called PCATVAL. Here’s what the PCATVAL Table looks like.
The Foreign Key 93 references these values to the same Primary key in the Table PCATTYPE, which essentially links the Project Code values to their Project Code across tables.
Now if in the XER File Parser you delete the Primary key 93 and you leave it in the table PCATVAL, you will create an orphaned record, and your XER file will be corrupted and cannot be imported. But more on that later.
2) How Primavera P6 Exchanges Data
XER is the most used format to exchange data with other P6 databases, as XML had some limitations ( limited to one project, less data is exported comparing to XER). In version 8.3, those limitation are removed and there are option to change global data attributes including
- Move global codes and calendars to project level.
- Map codes, UDFs, resources and calendars to existing value.
2.1) What is an XER file?
The XER file is one of the formats used by Primavera to exchange schedule data. It is a CVS type file with TAB as a delimiter. In simple English, it is just a text file with tabular data that you can open and modify in any text editor or program that read a CVS file.
2.2) How to view an XER file using Excel
You can view the content of an XER file using the notepad editor, or better using Excel as it you give a better overview of the data structure.
- Open a new sheet, go to data then get external data from Text, select your xer file then accept all default.
The first row stores the revision of P6 in which the XER was created (7), the date of the export, and the name of the user.
You will notice that the column A has 4 different attributes:
- %T : Table
- %F : Field
- %R : Record.
- %E: last row in the XER file.
Now when you filter only %T you will have the list of tables:
2.3) What Data is exported in an XER file?
When you export a project in a XER file, the data is mapped up to 66 Tables, usually not all the features are used in a P6 a schedule – generally speaking an “average” XER file has less than 20 Tables. Only the data present in the “Modify Import Configuration” can be exported.
3) The XER File Parser Utility
3.1) What is the XER File Parser?
The first part of this tutorial was to show that an XER file can be opened and modified by any editor, but it is not the best solution, as the data is combined in one sheet, and it is very easy to make a mistake thus corrupting your xer. Instead Primavera has created a versatile utility (though not well known).
The XER File Parser can be used for many purposes
- To correct a corrupted file, the best known example is an XER file with orphan records, as it can be imported to P6
- Clean an XER file
- Other uses that probably was not intended by the developer (like importing a WBS from Excel)
3.2- How does it work?
The XER Parser is a VBA macro for Excel that reads an XER file and, based on the values of the first column (%T,%R,%F,%E), will either;
- Generate a summary sheet with all the tables.
- Write every table to a different sheet. Then eventual after you made any modification, you can build a new xer file. Notice that the parser will not overwrite the original file.
The other functionally is diagnostic; it performs three tests and generates a report with the result
- Check for null ( the Primary Key should not be Null)
- Check for duplicates ( the Primary Key should be unique)
- Cross check for FK ( check the reference PK exist)
Wrap Up
In this part we had a general view of how P6 data store data and how the XER parser & Builder works, in the next series we will show you how to use the parser to clean your XER files (global code to project codes, and how to map UDF etc).
Thing for you to try
Just read the Primavera P6 database schema and you will see that the data in an XER file is not that complicated to comprehend – you don’t need to be a database administrator! But you should at least understand the terminology.
