Connect the Primavera P6 XER file to Power BI

Daniel Blázquez 19

Having trouble connecting your Primavera P6 database with Power BI? Are you tired of connecting excel sheets with Power BI and wish you could save time? In this post, we are going to show you how to connect the Primavera P6 XER file to Power BI. To do this, you first need to understand what the XER file is, how we can open it thanks to a notepad or the Excel tool, the information these files give us, and in what format. Are you ready?

SUBSCRIBE TO OUR YOUTUBE CHANNEL

Why connect the Primavera P6 XER file with Power BI

The era of dynamic reports based on Business Intelligence and the massive crossing of different databases has opened a new path for us in the world of project planning and project control reports. Generating reports thanks to the data obtained from the Primavera P6 planning and project controls tool has never been an easier task. In the post we released about how to connect Primavera P6 with Power BI, we already taught you how to create reports in Power BI thanks to the connection with Excel sheets.

JOIN OUR COMMUNITY

The use of excel spreadsheets is a process that, today, is certainly friendly for most users. However, it can become a process that generates errors more easily or is not 100% automatic. That’s why we think that working directly with XER files as data sources will save us a lot of manual work in the long run and greatly reduce the risks that come with working with manually created spreadsheets.

Before showing you how to connect the Primavera P6 XER file to Power BI, let’s see what’s behind the native XER file of this powerful project planning and control tool that is Primavera P6.

What to find in a Primavera P6 XER file

How is data stored in the Primavera P6 database

The Primavera P6 project planning tool database stores all data in more than 170 tables. Each of these tables contains different fields which in turn contain records. These tables are referenced to each other through relationships between a Primary Key and a Foreign Key. Despite the large number of tables that the Primavera P6 database contains, we should be more familiar with only 20 of them.

It should be noted that calculated fields are not stored in the Primavera database, since they are calculated based on other fields in the database.

Example of where data is written to the Primavera database

Next, we are going to see an example that shows how the data that we enter in P6 is stored within its database. To do this, we will base ourselves on the Activity Code called Project 2080.

The data is stored in the tables, fields, and records of the database, as we mentioned before. In this example, the Activity Code that we have called “Project 2080” is stored in the table (%T) “ACTVTYPE“, inside the field (%F) “actv_code_type_id“, with a unique code as a record (%R) of value “1986“. With the help of Excel, this is what it would look like:

As for the different values within the Activity Code “Projet 2080“, these are stored within the table (%T) “ACTVCODE“, as shown in the following screenshot:

Note that the Primary Key in the ACTVTYPE table (1986) becomes the Foreign Key in the ACTVCODE table. This will make both tables related to each other, something that will help us when relating tables in Power BI.

What is Primavera P6 XER file

Primavera P6 is capable of working with different types of formats, such as XML, XLSX (Spread Sheet), or MPX (Microsoft Project) to exchange data related to project scheduling. However, the file that characterizes Primavera P6 is the XER file. The XER file is a CVS-type file, with tab delimiters (TAB). In other words, the XER file is a text file with tabulated data that you can open and modify in any text editor or other software that reads CVS files. Before moving on to work with the Primavera XER file in Power BI, let’s show you how you can open and view this file in Excel.

How to open and view an XER file in Excel

As it is a text file, the XER file can be opened using a note editor and even using the Excel tool, as we will see below, through the “From Text / CSV” option:

Once we have clicked on this option, we select the XER file that we want to import and a new window called “Power Query Editor” will open. Our file will appear in it, but still without format. To do this, we will select the file and press the right button of our mouse so that a drop-down appears where we will select the “CSV” option:

At this time, only two columns will appear. In order to see all the columns, we will use the property of the XER files, which we know are tabulated text files. We will go to the option to “split columns“, by delimiter:

In the window that appears, we will select the “Tab” option as the delimiter, as shown below:

In the following image we can already see how all the columns that are part of the XER file that we export from Primavera P6 are shown:

We can now click the Close and Load the file button in the upper left corner to be able to see the XER file as a single table in our Excel tool:

Before going on to see how to connect the Primavera P6 XER file to Power BI, we want to mention that this file always has the same structure, regardless of whether it is a file with data from one, two, or more projects. The structure is always the same and there are certain attributes of the table that are important to know before moving on to the next point.

If we look at the first column of the table that we have obtained when opening the XER file in Excel, we see that there are four attributes:

%T: to refer to tables
%F: to refer to fields
%R: when referring to registers
%E: refers to the last row of the XER file of P6

Therefore, if we want to know the list of tables that our native Primavera P6 file contains, we just have to filter the first column by “%T“:

How to connect Primavera P6 XER file to Power BI in 4 steps

If you’ve reached this point, we’re sure you already have a much deeper understanding of Primavera P6 XER files than you did before coming to this post. It’s time to put these insights into practice and start connecting your Primavera P6 XER files with Power BI. The objective is none other than to fully automate your reporting system and save as much time as possible month after month.

Step 1: Open Power BI

First, we will open Power BI and save the file with the appropriate name. In our case, we have called this file “connect Primavera P6 XER file with Power BI”.

Step 2: Export your Primavera P6 projects to XER format

Next, already in Primavera P6, we will select and open those projects that interest us to generate our reports in Power BI. Once opened, we will export them in XER format. It is worth mentioning that there are up to three types of data to export: projects, only resources, or only roles. Selecting one or the other data set will only influence the number of tables that make up the XER file. For this article, we have chosen the option to export project data.

Step 3: Get the data from the XER file in Power BI

Back in Power BI, let’s start working with the XER file. We will import the data from the XER file into Power BI and work with it in the query editor. To do this, the first step is to import the XER text file. Click on “Get Data” and select the “Text or CVS” option. As they are files with a .XER extension, we must select the option to see “all files” and we will select the XER file that we have previously created.

The “Power Query Editor” will have opened, just as it happened before when we imported the file into Excel. We will follow exactly the same steps that we saw above when we talked about how to open and view an XER file in Excel (let’s refresh our memory: we must select the icon inside the “Power Query Editor” window, and with the right mouse button select the “CSV” option. At this time it will be transformed into a table with two columns. Next, we will select the first column (“Column 1”), and select HOME-Split Column-By Delimiter. We will select the “Tab” delimiter since the XER files are CSV-type files with TAB, and split at “each occurrence of the delimiter”). However, in this case we won’t hit the “Close and apply” button yet.

The second step consists of making a series of adjustments in this query. First, we will add a conditional column. The condition will be that if a %T appears in column 1.1, then it returns the name of the table that comes in Column 1.2:

Once this new column is created, we will select it and go to Transform – Fill Down:

Next, we’ll move the column between Column 1.1 and Column 1.2 and name it “Table”:

Lastly, we will filter Column 1.1, selecting everything except ERMHDR, %E, and %T:

Step 4: extract the tables from the XER file

In this step, we are going to start extracting tables, along with their particular fields and records. Since we don’t want this post to take longer than necessary, we are only going to extract the ACTVCODE table as an example. Once we know how to extract a table, the rest of the tables would be extracted in the same way, always using the same table from the XER file that we have exported from Primavera P6 as the data source.

We’ll start by creating a reference to the XER file query we just created in the previous steps. To do this, we will select the original query and with the right mouse button we will select “Reference“. We will create as many references as tables of the XER files that we want to have at our disposal.

Once we rename this new query by the name of the table that we are going to create (in this case it would be ACTVCODE), we will filter the column that we call “Table” by the name of our table and we will select Transform – Use the first row as a header:

In order to have a table as clean as possible, we will eliminate those columns that we do not need, as well as those that are empty or have null values in them.

Finally, we will change the format of the columns as appropriate, paying more attention to date formats and decimal numbers for those fields that refer to quantities.

TIP OF THE DAY: in certain formats, the commas and periods may vary when we fetch information from the XER files. Our recommendation is that you select all the columns where there is a decimal delimiter that refers to amounts and in Transform – Replace values you change the points by commas. In this way, when changing the data type to a decimal number you will not lose the decimals.

And that’s all. Now you can Close and apply to be able to see all the changes in both the Report view, Data view, and Model view.

IN PROJECT 2080 WE WOULD LIKE YOU TO REMEMBER

You have just taken a big step as a project planner and project manager. Perhaps you are not yet aware of the great change that is coming in your professional life, but from now on the reports you were preparing will take on a new dimension. The universe of dynamic reports welcomes you. And all this without the need to link the Primavera P6 database with Power BI. Now it’s just a matter of knowing which XER file tables you’ll need to generate those reports that your peers will envy. And you know what? That we will help you with it.