Use Dynamics CRM Multi-level Management Hierarchy For Security In PowerBI

Throughout our CRM and Power BI career, we all would have faced this requirement where we need to secure access to Power BI report data using the Management hierarchy of Dynamics CRM.

In simple words, if I am a Manager in CRM with 5 reportees, I should be able to see my own data in the Power BI report as well as the data owned by my 5 reportees.

And if my reportees login and view the report, they should be able to see only their own data.

On the surface, it looks like a pretty simple requirement. And although it is simple to achieve in Dynamics CRM. (Using the built-in Management hierarchy feature), there is no direct way to configure this in Power BI security.

Recently when I was faced with the same requirement, initially we just came to a compromise where I would just check the ownership of the record and see if it is my owned record or the records owned by a user(On whose user profile I am specified as the Manager).

But that was a temporary solution which only looks at 2 levels of hierarchy. The manager and one level down. We had to find a proper solution to achieve all levels of hierarchy security. And we had more than 8-9 levels of hierarchy in our Organization.

Finally, through some brainstorming and some blogs exploring, we came up with the following solution:

We flattened the Management hierarchy of Dynamics CRM and stored it in an external database table(FlatUserHierarchyTable). By flattening I mean for each connection there would be one row in the table which will capture the Manager(Direct and Indirect) as well as reportees information (hence the connection).

The table below shows an Org with 4 employees (3 Levels of Hierarchy)

E.g. If Employee1 and Employee2 are employees of my organization, and both of them have the same Manager in CRM and a Senior Manager(who the Manager reports to directly) in CRM. Then the table would have 3 rows each for Employee1 and Employee2:

• One row where Employee1/Employee2 is linked to their direct Manager.
• The second row where Employee1/Employee2 is linked to their senior manager
• The third row where Employee1/Employee2 is linked to himself as the manager (Yes, you read it correctly. This is to satisfy the logic which we are going to use to specify the condition for security role in Power BI. It will be clear once you look at the formula below)

Also, the Manager will have 2 Rows

• One where Manager is linked to the Senior Manager
• The second one where Manager is linked to himself

Senior Manager will have 1 row where he is linked to himself

This is how the hierarchy was flattened out and the table would be imported to the Power BI dataset and linked to the main table using the record OwneruserGuid connection with the ManagerGuid column.

Now, in order to use this table to secure the data, we used the below formula to filter the records based on the record ownership of the main record(e.g. Opportunity) and the flattened hierarchy:

Open the Manage Roles option on the Power BI Desktop and use this formula on the main record(e.g. Opportunity table) to filter it.

[OwninguserGuid] IN Summarize(Filter((FlatUserHierarchyTable),[ManagerEmail]=userprincipalname()), FlatUserHierarchyTable[ManagerGuid])

In this formula, Userprincipalname() is a function of Power BI which fetches the current logged in user’s email id, and we are comparing it with the ManagerEmail to filter down the flat user table. And once filtered we are picking up the Employee Guide and

This formula would make sure that whoever is logged in and viewing the report will have accurate data according to their role/position in the Hierarchy.

One more thing, we wanted to move this table inside CRM(as a custom entity) however, later on, we dropped the idea since we will have to have another job updating this entity every time there is a change in the hierarchy.

But it’s up to you if you want to try and keep it inside CRM so that it is better manageable.

And that’s how we achieved it. Let us know if this helped you or if you have any questions regarding the same.

The post Use Dynamics CRM Multi-level Management Hierarchy For Security In PowerBI appeared first on CRM Software Blog | Dynamics 365.