Automate Roles in Power BI Desktop

Power BI is a great tool; however, some of the Enterprise Management features don't exist, especially when creating numerous roles for row-level security.

The interface can be a bit clunky when repeating the same thing over and over again, which can lead to human error. The below steps support a semi-automated way to achieve the same thing with the possibility of taking this solution further via a PowerShell script.

Steps to Automate Role Creation:

  1. First, download and install DaxStudio and SQL Management Studio.

  2. Load Power BI Desktop and select the required PBXI file to apply the security roles.

  3. Load DaxStudio and connect to the internal PBI/SSDT Model option; this should list your Power BI file you already have open.

In this example, our Power BI Desktop file is called ShowcaseV3_0.

  1. In the bottom right-hand corner of DAX Studio is our backend Power BI Instance (aka Analysis Services); in this instance, it's localhost:51091.

  2. Installed during SQL Management Studio is an app called SQL Profiler; launch this application.

  3. Select ‘File’ and ‘New Trace’ and select the Server type as Analysis Services. Replace Server name with the hostname retrieved from DAX Studio and click “Connect.”

  4. On the Trace Properties screen, select “Events Selection.”

Within the Events Selection, tick Command End.

  1. Switch back to Power BI Desktop and create your first Role; this will become your template for later roles.

After clicking “Save,” switch back to SQL Profiler.

  1. Within the Profiler, you will see several lines; we need to extract two important commands. One is the Role name and the other the Role Table Permissions. Search for the Command End which contains an Element called <Roles> similar to the screenshot below.

Copy the full text to your favorite editor (Notepad!), this statement creates an individual role.

  1. Search for the Command End which contains an Element called <TablePermissions> similar to the screenshot below.

You will notice it contains the filter information that was entered in Power BI Desktop, for example, [ProductCode] = "Value".

Copy the full text to your favorite editor (Notepad!), this contains the Role Filter command.

  1. Copy the Role Name command (Step 9) into Dax Studio and click Run from the toolbar.

It should return an error that the Role already exists.

  1. You can now tidy up the command and remove some elements; first, remove all of the lines between the <Annotations> and </Annotations> include these tags as well.

Also, remove any other commands e.g. <Rename> tags, which basically leaves me with the required Create tag as per below.

  1. Let’s test our first new Role using this template by updating the tag from <Name>TestGroup</Name> to <Name>TestGroupB</Name>.

Our command now looks like this:

Click RUN to execute the new command.

The output window should respond that the Query Batch Completed.

  1. Flick back to Power BI and re-open the Manage Roles Menu, and you’ll find the newly created TestGroupB now exists.

  2. You can now repeat Step 13 creating all the required roles you need; the next few steps are a bit more involved.

  3. Within Dax Studio, enter the following command select * from $SYSTEM.TMSCHEMA_ROLES and execute to return the following.

The important column here is the “ID” value as this is required for the filters.

  1. Copy the Command obtained from Step 10 into Dax Studio; you will notice that an element named <RoleID> references the ID obtained from the previous step.

Update the RoleID to the next filter ID (Step 16), for example, ID 19793 and apply the new filter value for TestGroupB against the FilterExpression.

  1. Click ‘Run’ to process the change; confirm the output window returns no errors.

  2. Flick back to Power BI to confirm that our TestGroupB role now contains the new filter expression (Note: always reopen the Manage roles window when making changes in the backend).

Now repeat for your remaining new roles. It is possible to automate the above steps within a Power Shell script using the ADOMD driver.

Have fun!