Connect Primavera P6 ❰❰SQL DATABASE❱❱ to Power BI

We already showed you how to connect Primavera P6 to Power BI in three different ways. In that post, we focused on the option of exporting the data from Primavera P6 to an Excel spreadsheet and then linking that spreadsheet with Power BI. We obtained a dashboard that dynamically showed the quality assessment of a project schedule, taking into account the 14 points on which the “Defense Contract Management Agency (DCMA)” is based as a criterion. In this post, we will focus on linking the Primavera P6 database to Power BI. Are you ready?

Primavera P6 Database architecture types

First of all, we are going to see the two types of architectures under which the Oracle Primavera P6 tool works:

1. Client-Server Architecture: this is an architecture that covers a larger user size, as well as large volumes of information. In this type of architecture, the Primavera P6 software depends on a process started on a server. The six features that the client-server architecture offers us are:
   • higher scalability
   • greater performance
   • higher availability
   • greater security
   • greater integration
   • higher operability
2. “Standalone” Architecture: it is an architecture designed to work with an “express” database, since it works locally and without the need for a network connection. The great advantage of this architecture is that they are free versions and that you can download them from the Oracle website. In contrast, the size of the database does not exceed 10 Gb. In this case, Primavera P6 works as a separate and independent process, and not as part of an existing process.

Once you know the two types of architecture with which the Oracle tool works, you must assess what size, type and version of primavera p6 database your organization needs, as a previous and necessary point before installing the Primavera P6 software. You should also know that the Oracle Primavera P6 tool is capable of working with the following databases:

1. Oracle Database
2. Microsoft SQL Server / SQL Express
3. P6 Pro Cloud Connect
4. P6 Pro Standalone (SQLite)

Due to the educational and informative nature of this post, we will focus on the database installed as “P6 Pro Standalone (SQLite)“. This is the Primavera P6 Database type that any user who wants to learn how to use the Primavera P6 software from Oracle can install on their home computer, completely free of charge.

How to connect Primavera P6 Database to Power BI

First of all, what do you need to connect the Primavera P6 Database?

Step 1: Primavera P6 Database configuration

First of all, having configured the P6 Pro Standalone (SQLite) DB when installing the Primavera P6 scheduling tool on your computer.

Step 2: SQLite ODBC Driver (64 bit) installation

Second, you must have the SQLite ODBC Driver (64 bit) on your computer. This is necessary since the connection of the Primavera P6 Database to Power BI will be done through the “ODBC” option, as shown below:

Within the driver download website you will have to download the highlighted file shown below and install, selecting all features:

Once the drivers are installed, you will have to download the following installer for the Windows version “w64” and install all the components:

Step 3: ODBC Data Source configuration

To do this, go to the start menu, search for “ODBC Data Source (64-bit)” and open it.

Once open, in the “User DSN” tab, highlight the option “dBASE Files” and click on the “Add …” button:

In the window that opens, we select the option “SQLite3 ODBC Driver” and we finish:

Then the last window will open where we must enter the name of the data source with which we want our Primavera P6 DB to be displayed, in addition to selecting the corresponding Primavera P6 Database file on our laptop:

Once this is done, we click OK, and OK again. We already have our Primavera P6 SQL Database configured as ODBC. Let’s move on to Power BI to connect our Primavera P6 database and view all the tables included in it.

Step 4: get and transform data in Power BI through ODBC

We have already done the most complicated thing, which is to configure our Primavera P6 Database so that it can be read by Power BI as an ODBC data source. Now we have to open Power BI Desktop and go to the “Transform Data” query option:

Once inside the Power Query Editor, we will select ODBC as the new origin and we will connect:

A new window will open where you can select your Primavera P6 Database as configured above. In our case, the Primavera P6 SQL Database is called “Project 2080”:

Once we accept, the work is done: we will have access to all the tables in our Primavera P6 Database. From here on, we can select those tables that most interest us, depending on the type of reports or dashboards that we want to develop:

IN PROJECT 2080 WE WOULD LIKE YOU TO REMEMBER

Remember that the Oracle Primavera P6 software tables are normalized and there are more than 170 different ones. Each of these tables contains different fields and it is where the data is stored. Since both the name of the tables and the fields within each table can be confusing, we want to give you access to the Oracle mapping website where you will find the meaning of each of the elements of these tables that make up the base of data from Primavera P6. Also, note that the calculated fields are not stored in the Primavera P6 Database.