Connecting PowerBI to Primavera, Implement Row Level security

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)  

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s