Automate Roles in Power BI Desktop

Power BI is a great tool however some of the Enterprise Management features dont exist especially when creating numerious 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 an semi-automated way to achieve the same thing with the possibility to take this solution further via a Power Shell script.

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

   Dax Studio https://www.sqlbi.com/tools/dax-studio/

   SQL Management Studio https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

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

4. In the bottom right hand corner of DAX Studio is our backend Power BI Instance(aka Analysis Services), in this instance its localhost:51091
   
   

5. Installed during SQL Management Studio is an app called SQL Profiler, launch this application.
   
   
   

   
   
   
6. 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”
   
   
   
   
   
7. On the Trace Properties screen, select “Events Selection”
   
   

   Within the Events Selection, Tick Command End.
   
   
   
   

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

9. 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 favourite editor (Notepad !), this statement creates an individual role.

10. 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 favourite editor (Notepad !), this contains the Role Filter command

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

     

    
    
    

12. 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 command e.g. <Rename tags, which basically leaves me with the required Create tag as per below
    
    
13.  Lets test our first new Role using this template by updating the tag from <Name>TestGroup</Name>   to  <Name>TestGroupB</Name>
    
    

    Our command so now look like this :
    
    
    
    

    Click RUN to execute the new command.

    Output window should response that the Query Batch Completed
    
    

14. Flick back to Power BI and re-open the Manage Roles Menu and you’ll find the newly created TestGroupB now exists.
     
    
15. You can now repeat Step 13 creating all the required roles you need, the next few steps are a bit more involved.
    
    
16. 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.

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

18. Click ‘Run’ to process the change, confirm the output window returns no errors
    
    
    
    

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