Power BI Multiple Audiences were released a few months ago and gave the opportunity to have one Power BI app with content dependent on the user. Before this you had to create multiple app workspaces and apps if different user groups need different reports, but now you can one app/app workspace for a subject are with multiple audiences. We are now using Data Factory and Active Directory groups to manage members of these audiences and I will explain why and how.
We have a membership benefit for purchasing goods where members get a kick-back when buying from certain suppliers. We are now creating a new Power BI app for reports on this benefit that will include reports for suppliers on the data they have reported, reports for members to check that all purchases are reported (across different suppliers) and reports for us to monitor everything. So we will have different reports with different row level security for different users (including external users with Azure B2B authentication). But all about one membership benefit so this will be one Power BI workspace with three different audiences. To set this up in Power BI we will create three different Azure AD security groups, one for each audience. But how to manage the members in the groups?
Our architecture uses Azure Data Factory for orchestration and Azure Databricks for data transformation. We have now set up a new (generic) Data Factory pipeline to manage Azure Active Directory group membership. This can handle both internal users and external guest users. We are using the Microsoft Graph API with Azure AD and Managed Service Identity for authentication. The Data Factory needs two permissions that must be set up by an Azure AD admin; User.Read.All and User.Invite.All (this to invite external guest users, so if you might not need this).
The pipeline uses the group id, group name (just in order to copy this to a folder with group name in data lake) and name of the Databricks notebook to set access permissions as input parameters. The steps in the pipeline are;
1. Copy group members
This connects to Microsoft graph as a REST data source to copy the current users. We have created a generic dataset with “https://graph.microsoft.com/v1.0/” as base URL and the relative URL for the source is “@concat(‘groups/’,pipeline().parameters.groupId,’/members’)”. This is stored in a json file in our Azure Data Lake Storage. Remember that the results can be paginated so set pagination rules to absolute url and as body “[‘@odata.nextLink’]”
2. Set access permissions
This is an Azure Databricks activity that calls a notebook to set user permissions. This notebook needs some inputs but we have chosen to not include this in the pipeline so that it can be reusable. This pipeline will be run by one of our control pipelines so the required data will be loaded there. The notebook can vary from just setting group permission but could also create tables for Row Level Security. The notebook creates a json formatted output that includes three arrays; deleteUsers, newUsers and newExternalUsers, using the dbutils.notebook.exit command. This output can then be processed by the ForEach loops.
3. Delete users
We always check that there are no users in the access group that is not in the access list from Databricks. This could ensure that if a member leaves us their access to our Power BI reports will also be removed. From the copy group members (step 1) we will have the Azure Active Directory ID of the user so we can just call the API to remove it.
4. Add new users
The next ForEach is to add new users, and here limited to internal users already in AAD. We don’t know the AAD id for the user, so we will do a call to find the user first and then add it to the group. This is why we need the User.Read.All permission.
5. Add new external users
The final step is to add new external users. Here we won’t know if the user exists in AAD to so we first check if it’s there and then either adds it to the group, or invite it to then add it. We do the invite without sending an invitation email so the user will be added without getting a seperate email from AAD about it. After sending the invite it takes some time before the user is created, so we have a 15 second wait before looking it up a second time.
One thing to note here is the user id when handling external users. If you have a user with email@domain.com as email address this will be shown differently in the various steps in our pipeline:
- When getting group members it will have the address email_domain.com#EXT#@<id>.onmicrosoft.com, so you must check this when considering if the user should be removed.
- When trying to find it you have to replace # in the url so “find user external” must use email_domain.com%23EXT%23@<id>.onmicorosft.com
- When inviting the user you must use the actual email address
We are solving this with the replace function in Azure Data Factory. I recommend to test this to check that your setup is correct.
And that’s it! This is a way to manage a Active Directory Groups with Azure Data Factory and have it ready for Power BI audiences and row level security. We are typically running this as a step after preparing our fact and dimension tables and before triggering Power BI refresh from Azure Data Factory.