I think one of the most asked question when some talk about Primavera and PowerBI, is how to connect to the database, ok, the good news is, the connection itself is easy, the bad news, extracting useful information is a bit of work.
Just to show how it work, I am using a temporary installation in my personal laptop, as obviously I don’t have access to my production database.
I am using a developer edition of SQL Server 2006, and an evaluation copy of EPPM, oracle allow the use the evaluation of most of its software for the first 45 days, you can download a copy from here, you need SSMS too
For the purpose of this blog, we will query the “normal” Primvera tables, for the extended schema, which is a groups of tables and views design specifically for reporting, but those extra tables are empty per default and you need to configure publishing service ( will discuss it in a future blog), please note I already blogged about how to connect when using Sqlite in the case of standalone P6 professional
Connect to SQL server using SSMS
When you install Primavera, you get to define 4 user account
- sa : the database admin account (not the admin for primavera application).
- Privuser, pubuser : used to connect Primavera app to the database
- Pxrptuser : user account for reporting
We will use sa to connect to the database
When you click on connect you get this
The database itself has 320 tables; you can check that by running this SQL script
Create a read only user
Connecting using the admin account is just very bad practise, and I don’t want to mess with the existing account, so instead we will create a read only user account
- Create a New Login
- Create password
- Map the user the PMDB
- Assign a new role
Instead of having access to the 320 tables, we create a new role (read_only) and we just assign the 3 most important table in the database, you can add later more tables, we granted select only, so no read access
Connect PowerBI to SQL using read only user
and Voila our Tables are now visible in PowerBI
so the answer to how to connect to Primavera Database from PowerBI is you need a user name, password and the server name, the challenge is how to extract meaningful reports from those tables ?
at this stage, you need to get yourself familiar with Primavera Schema, yes it is 320 tables, but the basic one are three, and usually for my reporting I use around 10, I wrote an introduction to Primavera schema 6 years ago, I hope it is still relevant
Part 2 is published here