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?
SUBSCRIBE TO OUR YOUTUBE CHANNEL
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:
- 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
- “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:
- Oracle Database
- Microsoft SQL Server / SQL Express
- P6 Pro Cloud Connect
- 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.
24 September, 2021 @ 17:09
How can you connect Power BI to P6 if the P6 database is cloud connect, which is the database in my P6 installation?
3 October, 2021 @ 20:25
Hi Kade,
thanks for your comment. We recommend you contacting Oracle to find out the best way to connect this database to Power BI. In the meantime, we will try to find out more about it. We are so sorry for not being able to help you this time.
Regards,
14 January, 2022 @ 16:46
Sir,
Do you have by any chance documentation regarding Primavera p6 database tables and their fields in order to create
a project S-Curve?
I appreciated.
Regards,
17 January, 2022 @ 09:39
Hi Rogerio,
thanks for your message. I am currently working on a new post where I show how to connect XER files to POWER BI, using the tables within the XER file. As part of this new post, i will include for free a Power BI file with all these P6 tables and data transformation. You will have only to download your XER file and magic will be automatically shown.
Regards,
29 April, 2022 @ 21:31
Refer to Primavera Schema Documentation
https://docs.oracle.com/cd/F37125_01/helpmain.htm?toc.htm?55497.htm
30 April, 2022 @ 09:29
Thanks for your support on this, Erik. Really appreciate!!
We would like to take advantage of this message to let you all know we released a new post about how to connect XER file from Primavera P6 to Power BI.
14 January, 2022 @ 16:47
Sir,
Do you have by any chance documentation regarding Primavera p6 database tables and their fields in order to create
a project S-Curve in Power BI, I mean.
I appreciated.
Regards,
17 January, 2022 @ 10:02
Dear Rogerio,
thanks for your message. As we informed you in a previous message, we are working on a new post where we explain how to connect the XER file to Power BI. On it, you will find how we create S-Curves in Power BI.
In the meantime, to create an S-Curve in Power BI you can use the following formula:
Physical Progress to date =
CALCULATE (
[Physical Progress to date],
FILTER (
ALL(‘Data Set’),
‘Data Set'[Finish Date]<=TODAY() ) ) where [Physical Progress to date] is a measure in Power BI and equals to "Physical Progress to date = SUM ('Data Set' [Physical Progress])". If you want to be the first one to receive new content and be actively part of our community, you can subscribe for free.
Regards,
Regards,
26 December, 2023 @ 04:02
Would this Power BI cumulative measure work better with an imported time-scaled report from P6?
24 January, 2024 @ 07:28
Hi Konrad,
the more detail our data is the better our reports will be.
Regards,
6 May, 2022 @ 09:38
I followed Step 1 and 2 in your post. But I can not find “SQLite3 ODBC Driver” option in the list as per Step 3. What to do?
6 May, 2022 @ 12:00
Hi, Khaled
Thanks for your message. Please, select “SQLite ODBC Driver” instead. It is weird you are not able to see the “SQLite3 ODBC Driver” option. Did you install the Primavera P6 database as “P6 Pro Standalone (SQLite)”?
Regards,
3 October, 2022 @ 21:58
I managed to connect to P6 database following the instructions on this page. Thank you very much.
Do you have a post explaining how to utilize the data from P6 to create visualizations in Power BI?
25 October, 2022 @ 08:44
Thanks for your message, Khaled
We have a post explaining how to use data from P6 (Excel file) to create visualizations in Power BI. The report we show on the post is for Project Schedule Quality Analysis. We are currently working on new posts to cover all related to SQL-P6 integration, Python for scheduling reportint, and others.
Regards,
20 April, 2023 @ 19:51
Hi, Can you please help me on the following question,
Do you know how to move the data from P6 EPPM and Unifier cloud database to MSSQL local database? I’m planning to maintain database snapshots at local MSSQL databse and from there planning to have PowerBI dashboards.
13 May, 2023 @ 09:21
Yes, we can certainly guide you on moving data from Oracle’s Primavera P6 EPPM and Unifier cloud databases to a local MSSQL database. Please note that the exact procedures can vary depending on the versions and specific setup of the software in use, and you should always make sure to back up any important data before attempting any kind of migration.
Here’s a general step-by-step guide:
1. Data Export from Oracle Cloud Database (P6 EPPM and Unifier): First, you need to export the data from your Oracle Cloud databases. You can use Oracle SQL Developer, which is a free integrated development environment that simplifies the management and development of Oracle Database, to do this. You can create a full database export, or select only the specific tables you need.
2. Data Import to MSSQL: After you have your exported data, you will need to import it into your MSSQL database. If the data was exported in a format like CSV, you can use SQL Server Management Studio (SSMS) to import the data. You can use the Import Flat File wizard for this. If the data was exported in a SQL format, you may need to adjust the syntax to be compatible with MSSQL, as there can be differences between Oracle SQL and MSSQL.
3. Creating Database Snapshots in MSSQL: Once the data is in MSSQL, you can create database snapshots as needed. A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. You can create a database snapshot in SSMS by right-clicking on the database, selecting Tasks, and then Generate Scripts. In the scripting options, you can select to script all objects and data.
4. Connecting to Power BI: Once your data is in MSSQL and you have created your snapshots, you can connect Power BI to your MSSQL database. In Power BI Desktop, you can click on “Get Data”, select “SQL Server”, enter your server and database information, and then load the data you want to use in your reports.
Please note that this is a simplified overview of the process, and the exact steps may vary depending on your specific setup and requirements. You should also keep in mind issues of data security and compliance when moving data from a cloud environment to a local one, particularly if the data contains any sensitive or personally identifiable information. You should ensure that your MSSQL server is secure and that you have taken all necessary steps to protect the data.
Before you begin, it’s a good idea to consult with a database administrator or other IT professional who is familiar with both Oracle and MSSQL databases, as they can provide guidance tailored to your specific situation.
4 June, 2023 @ 10:55
Hi,
when I follow the steps and create DB connection to SQLite database. Some table names does not show up. For example, RESOURCEASSIGNMENTSPREAD table is not selectable when choosing source in Power BI. Does this table not exist in SQLite database or I am doing something wrong.
22 September, 2023 @ 08:32
Hi, Hasan
you are not doing something wrong. Tables related to “Resources” all starting by “RSRC” (RSRC, RSRCANDASH, RSRCANVIEW, RSRCCCURV, RSRCHOUR, etc). I hope this comment helps you.
Regards,
8 December, 2023 @ 22:33
The P6 database I want to connect to is configured as a Microsoft SQL Server / SQL Express. How do I connect Power BI to this database type?
24 January, 2024 @ 07:23
Hi Ryan,
this post is related to the connection to “P6 Pro Standalone (SQLite)”. We will prepare a new post related to “Microsoft SQL Server / SQL Express”. In the meantime, you can connect to “SQL Server” (this is one of the options to get data). Just select the server name and database you want to get data from.
Regards,