Primavera has a very comprehensible security mechanism, based on OBS and EPS and user access, it is very mature and rigorous, but once you have a direct connection to the database’s backend all this security disappear, the connection will give you access to everything in the database.
This blog post is not a comprehensible security introduction to Primavera reporting, but rather a gentle introduction to RLS in PowerBI and how you can leverage it in the simplest form possible, and a reminder why security is a very important consideration, specially when you deal with a portfolio and multiple division in the same company.
if you don’t want to read the whole blog, I think all I want to say is
- Connecting to Primavera database is not hard
- Only Primavera Admin or IT should have access to Primavera Database connection.
- There are multiple solution to implement Projects level access, particularly if you are dealing with multi divisions portfolio
What is row level security?
Is the ability to access a table but read only some rows, for example you want to read the table TASK, which show all the activities for all the projects, obviously you need your user to view only the activities that belong to the projects assign to them.
The Problem
Primavera administrator want to create a companywide reporting system based only on Primavera Database, RLS is implemented in PowerBI everyone see only the data that belongs to him, happy days, planning manager from division X like the reports but wants to use other data sources too, not only Primavera but Ecosys, cobra , progress measurement system etc, still he needs to see only his projects, Planner Y don’t care about PowerBI and wants the raw data to do his own stuff using some obscure VBA Excel, PowerBI as of this writing can not connects to multiple datasets from other reports, and you can’t mix live connection with import from other source.
Solutions
Whatever you do , you need to have only one connection to Primavera Database, don’t give multiple users access to the production database, that’s bad ( probably your DBA, will not do it anyway), I know it is still read only, but it is bad practise, if your write an expensive query against a reporting server, it is annoying, but slowing Primavera server will get you angry planners from everywhere ( we are very famous for being grumpy)
- Reporting Database
Ideal solution, just spin a cheap SQL instance on azure and make sure it is on the same region as your PowerBI ( no egress fees), implements RLS there for the people who want to author reports ( the viewer access will be done in PowerBI),any IT can easily create a small pipeline to copy data from Primavera Database ( doesn’t matter, Oracle or SQL server) and you get your data fresh every morning or whatever schedule you want. ( bonus point, no PowerBI gateway between PowerBI and SQLServer as both are in azure)
- PowerBI Dataflows
If SqlServer is not an option , PowerBI dataflows seems like a perfect solution here, you connect once to the Primavera databased and you can share the results with other user, PowerBI will be hitting the Dataflows instead of the production server, perfect, yes, unfortunately, Dataflows results are just CSV, no RLS, either you read the whole TASK table or not.
- Analyse in Excel
Personally, I think it is the most Powerful feature of PowerBI, when you have access to a report in PowerBI, when you click analyse in Excel you get access to all the data behind the model, not only that you can create a table that fetch the data and bingo, it does honnor RLS, you see only your stuff, so the workflow will be something like this, for how to use analyse in Excel, Please read this excellent blog by Chris Webb
Let’s see how to use RLS in PowerBIRLS is very vast subjects, and has many different implementations and nuance, in real life you need to have something like a hierarchy security like Primavera OBS, and you need to read this excellent series of blogs from Reza, let’s say I want to grant view access to a couple of Projects to two users (Viewer @ projectscontrols and test@ projectscontrols ), those users are not necessary planners they don’t have access to Primavera
I just used Project user defined field and typed the full address, you need the email address as it is the format that PowerBI understand, the good thing about UDF, you can put multiple values separated by a comma, I pulled the following tables from P6 Database (UDFTYPE , UDFVALUE) and just using PowerQuery to split and unpivot, and I go this little tables.
Now I have the Project ID and the username of the users who can access the projects.
Edit the relationship between access table and Project tables
Manage role
Basically, table access get filtered by PowerBI, then it will filter table PROJECT, that will filter table TASK
Publish to PowerBI service to the owner account but not to user Viewer as he should only have access to view and build ( you need build to use the dataset in other reports), RLS works only with viewer role,
Now let’s see what the user viewer will see
Yes, only 7 projects are visible to that user, let’s try analysing in Excel,
Once I select table access, only the viewer gets selected
Now let’s query only the table TASK ( yes DAX is a query language too)
Voila we have the TABLE Task filtered, with only 7 Projects
Now you can add more tables and load those tables into another PowerBI datasets and do your own enhanced reports.
Project Controls blog 