Connecting PowerBI to Primavera Database, Part 1

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

USE PMDB

GO

SELECT *

FROM sys.Tables

GO

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

1. 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 ?

what’s next

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

For security implication please read this