Question on Row Level Security in Power BI for large user base

[ranbeermakin]

My customer scenario

Power BI P1 Premium license
16 million records data stored in mysql

Users have access to specific accounts and specific Power BI Reports only.
Users will access to Power BI reports using Power BI Embedded.

Example:

User 1 to User 20 have access to Power BI report 1
User 20 to User 100 have access to Power BI report 2
User 100 to User 4000 have access to Power BI report 3

User 1 has access to 10 accounts, user 2 has access to 5 accounts, and so on.

The customer wants to apply row level security. If User 1 logs in he/she should be seeing only his/her 10 accounts. Our custom application will take care of redirecting him to Power BI Report 1.

Here's what I'm thinking how will we tackle this, along with some questions:

1. I assume there is not direct connection to mysql. So we need to import that much data. We also need to create a user-account mapping in Power BI model for applying RLS in power bi.

2. We need to configure roles in Power BI Desktop and publish to powerbi.com
3. Add users to these roles in Power BI service for this data set.

We have 4000 users should we add all these in Power BI service? I think it is limited by 1000 users only.
I suspect we have to create groups?
But groups should be at what level? Since our security is based on user level. Not manager or region level.

What groups are these Office 365 groups? AD groups?

Can I leave those roles in Power BI service and do not add any users? And configure my role in Desktop using USERNAME() DAX FUNCTION?

4. Create new Power BI Reports (2 and 3) from this data set by using GetData->"Power BI datasets"

Publish these reports in PowerBI.com

Do I need to setup roles in this report as well? I assume yes.
What is the alternative to not setup roles?

Since RLS is already configured on this dataset I assume the security on new reports will automatically work.

Who accesses which report is governed by our custom application.

Can somebody in the community let me know answers to my questions, and correct my understanding?

Alternatively, do you recommend us to go with Analysis Services which takes care of such type of security automatically? Like I don't need to add 4000 users in my roles.

Can this be tackled by SQL directly? I pass user name to my direct query and it loads only specific dataset?

Appreciate some help on this.

Thanks,
Ranbeer Makin

[ranbeermakin · Accepted solution]

I have answers to some of these questions

1. You need O365 security group. I cannot seem to add distribution group.

2. We need not define roles in all the reports when pulling data using GetData->Power BI data sets (PowerBI will not allow you to add the roles, it will be disabled)

3. We however, need to put O365 security group when configuring roles in Power bi service for that dataset.

When configured like this, it works like a charm.

Best,

Ranbeer

[v-yuezhe-msft · Accepted solution]

@ranbeermakin,

I would recommend you import data from MySQL to SSAS, then implement dynamic row level with Analysis Service tabular model following  the guide in the article below.

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular

Regards,
Lydia

[ranbeermakin · Accepted solution]

I have answers to some of these questions

1. You need O365 security group. I cannot seem to add distribution group.

2. We need not define roles in all the reports when pulling data using GetData->Power BI data sets (PowerBI will not allow you to add the roles, it will be disabled)

3. We however, need to put O365 security group when configuring roles in Power bi service for that dataset.

When configured like this, it works like a charm.

Best,

Ranbeer