Roles creation/management in Power BI Services

[Sakshi]

Hello All,

Currently, we require creating a role in Power BI desktop and then publishing that on Power BI Services. Then only, the role will be visible on Services and we will be able to add a user to it.

We are trying to implement row level security with the help of roles in Power BI. So, in a single report, for each client, there will be a single role which will further filter all the tables available based on data for the client. 

Here, we have around 15-20 tables in each report and have a lot of reports(let's suppose 50) initially created with a single client role. Now we have to onboard many clients(around 100) into all of our reports.

So, currently, the process to onboard a new client is manual and we have to open our reports in desktop and create a new role and republish it again each time we want to onboard a client.

Is there a way to automate the process? Such as to create the role in power BI Services itself for each report published there through some API or by writing any script?

We are trying to achieve as much automation as possible for the process on onboarding a new client.

Thanks & Regards

[micatio]

Hi All,

It is actually possible to automate this process to some degree using PowerBI Desktop.

If you've got 100's of roles to create then the GUI is going to be a nightmare however you can script these using the XMLA language. 

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

2. Load PowerBI Desktop and open your pbxi file. 

3. Load DaxStudio and connect to the internal PBI/SSDT Model option. 

    Now your connected to the backend PowerBI Desktop SSAS cube.

4. Within DaxStudio on the bottom right corner you will see localhost:<port number> 

    You need the internal port number which is visible , e.g. locahost:55246

5. Load SQL Mangement Studio and select "Tools", "SQL Server Profiler" 

6.  Select the Server Type as Analysis Services and Server Name as localhost:<port number>    replace <port number> with the values you obtained earlier.

7. Within "SQL Server Profile" you are prompted with the trace properties window, select the "Events Selection" tab  and pick the group  "Command Events" and select "Command Begin" 

    This will capture all commands sent to your Power BI Cube and including any role commands. 

8. Switch back to Power BI Desktop and attempt to create a new role within the managed roles....we will use this as a template. 

9. Switch back to the SQL Server Profile window. 

10.  Look for the command the contains something as follows (they may be several command listed now)

              <Batch Transaction="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
              <Create xmlns="http://schemas.microsoft.com/analysisservices/2014/engine">
             <DatabaseID>removed ! </DatabaseID>
             <Roles>

                   ..............

11. Copy this command into clipboard.

12. Close down SQL Profile window and switch back to SQL Management Studio. 

13. Within SQL Management Studio, Select "File" and "Connect...." and again select the Server type as "Analysis Service" and enter the localhost:<port number>    (again replace <port number> with your values). 

14.  Select "File", 'New" and pick "Analysis Services XMLA Query" 

15. Within the new window paste the clipboard contents hence you should have the following lines visible including all the other lines :

             <Batch Transaction="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
            <Create xmlns="http://schemas.microsoft.com/analysisservices/2014/engine">

             .........

16. Scroll down the botton of the query and remove the whole of the PropertyList block :

                 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
                 <LocaleIdentifier>3081</LocaleIdentifier>

                  .....removed 
                 </PropertyList>

17. Now if you execute this command (F5) it will throw an error back that the role already exists ....

      <return....

     <Error ErrorCode="3239313410" Description="The Role with the name of 'some role' already exists in the 'Model' Model....

18. Now the easy bit is to scroll up and replace the rolename and filter values with your new values and repeat the process until you've created all the requires roles, note there are several bits you need to replace.

19. Switch back to Power BI Desktop and you'll notice the new roles now appear in there as well. 

20. Note....there are two commands in creating roles, first is the role itself, the second is the permission which contains a reference to the roleId reference. I'll cover this in a second topic. 

20. Save the pbix file and these new roles will now appear in the file. 

5/7/2020 - Updated Instructions at https://micatio.com/blog/automate-roles-in-power-bi-desktop/ 

Have Fun.

Ron 
ronald@micatio.com

[v-yulgu-msft]

Hi @Sakshi,

Currently, it is not supported to create Roles and add clients to corresponding Roles automatically/programmatically. Someone has submitted this feature quest that allows REST API to manage RLS, please click to vote it up here. If it is mentioned by many users, PG might consider adding it to later release.

Best regards,
Yuliana Gu