As you might know Power BI offers a very nice role management that allows filtering the data based on role(s) associated with the user.
A basic use of this feature is presented in https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/.
A more dynamic use of this feature is very well explained in http://radacad.com/dynamic-row-level-security-with-power-bi-made-simple .
But this latest option still requires to maintain a table of some sort where we associate users and their roles.
Most organizations already have associated roles to users in Active Directory and this posts aims at demonstrating how to leverage this.
It explains:
- How to connect to AD
- How to select the data we need in AD
- How to transform the data in a way that makes it usable in PBI
- How to leverage the data in PBI
How to connect to AD
This is easy because Active Directory is one the listed Data Sources
How to select the data we need in AD
After connecting to your domain, you will be presented with dozens of table. Only one of those is interesting for use here: User
How to transform the data in a way that makes it usable in PBI
What we need for every user is their email address (because it’s the user ID in PBI) and the groups they are associated with.
- Email address
Expand the user column and look only for “mail”
- Groups
Groups are in the distinguishedName column but it requires a bit of work to be usable
First split the column on the “,” delimiter
Then select all the distinguishedName.* columns and unnpivot them
Then split the new “Value” column on the “=” sign
If you wish you can then rename the colums. I think those are called nodes in AD so I called them
NodeID, NodeType and NodeName.
NB: if, like me, you wondered what CN, OU, DC stand for, here it is
- CN = Common Name
- OU = Organizational Unit
- DC = Domain Component
Tadam you are all set!
For any user you can get the group (Nodes) they belong to
And more important you can get all the users that belong to a given group
How to leverage the data in PBI
Now you are simply back to the role creation issue, where you can filter the data based on the group (NodeName) of the user.
To illustrate this let’s take this financial sample workbook: http://go.microsoft.com/fwlink/?LinkID=521962
Let’s say people from the GPO group should only see data from Canada.
We need to create a table that stores all those associations.
So sure we still have a table to maintain, but this is still much more dynamic because we don’t have to maintain the group/user association. Everytime a user joins or leave a group in AD, the data get filtered accordingly.
Now we need to link this table to both the user table on the group and to the data table on the country
The last piece is the association of the current user to the user.mail at run time. This is achieved by creating a role
In the user table add the following table filter DAX expression:
[user.mail] = username()
The last step is to get the role to be applied at run time by applying the role to all users.
Once you application has been published in the cloud open the security menu of the dataset.
And add the role to all users.
The application of the role at runtime will cause
- the username to be used to filter the data only on matching [user.mail],
- which will in turn filter on the associated NodeName/group,
- which will in turn filter on the authorized countries for the user.
----------------
APPENDIX
In real life you will probably have several countries visible for a given group and several groups accessing the same countries.
In other words you will end-up with many-to-many relationships that you will need to tackle by creating relationship tables.
You can easily create those tables by referencing the existing tables
Keeping only the column you are interested in, in our case the NodeName column
Then the final step is to remove duplicates
For the relationship to work you might need to make sure there is no empty row in the list.
Please note that Power Query and PowerBI currently don’t have the same definition of duplicates (https://community.powerbi.com/t5/Desktop/Power-Query-and-PowerBI-currently-don-t-have-the-same-defin...) so be careful
You can now do the same to get a list of all unique countries
Which will lead you to this new data model
But for the relationships to work as expected you will need to edit the relationships
And set the Cross fitler direction to Both
Hi there,
Love the idea of using the AD for permissions. I have a question though.
If a user changes group or region for an example. How easy would it be to update this solution to reflect that change and show the user the right data. Would the process have to be done all over since AD isn't a source that has data refresh.
Cheers. 