Over 600 Roles needed

[Stef_Farr]

Hi,

I am working on a Power BI project. I need to create over 600 roles to give different access of the data to each role. I need to do it, such that, it is not something that I have to keep maintaining with every new recruitment. How can I do this? And should I use roles or something else in Power BI?

Thanks.

[v-jayw-msft · Accepted solution]

Hi @Stef_Farr ,

You could try to set dynamic row-level-security in Power BI.

Please take a look at these blogs.

https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple 

https://blog.pragmaticworks.com/dynamic-row-level-security-in-power-bi 

Best Regards,

Jay

[Stef_Farr]

Thanks so much @v-jayw-msft . This should work. I'll try it out. 

[Stef_Farr]

Hi @Anonymous ,
The different roles need access to the same Power BI report via Power BI Service. Basically I need to create one report connected to a database. Each user will access the same report but needs to see only the data that pertains to that user. 

[Anonymous]

@Stef_Farr The Power BI report will need to know the username of the user viewing the report. I am not familiar with how to do this in Power BI Service. I assume that you want to limit the rows of data that each role can see, not the columns. In which case I would suggest reading up on Row-level security (RLS) in Power BI.

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

How will the report know which data that UserXYZ can see?

[Stef_Farr]

Hi @Anonymous ,

Yes that is what I was asking about. My issue with this and hence my question:
- How to have it as automated as possible? Imagine we have a new recruit, I don't want to have to go through the process to add a new role and upload the pbix to the Power BI service all the time.
- If it allows you to create this many roles?
- Should I be using another method, considering this many roles?

[Anonymous]

@Stef_Farr A few questions to help me understand better:

Can a User have just one Role, or many Roles?

Is it the Role that drives what data can be seen, or the User? i.e. is it possible for there to be two different Users with the same Role(s) but they should see different data? Or would two Users with same Roles see exactly the same data?

How often would a new Role be created?

[Stef_Farr]

Hi @Anonymous , Yes of course, ask me. Really need help with this. I use roles with my reports all the time. But this one with so many roles got me thinking.

A role might have more than one user.
The role will determine what data can be seen.
If there are 2 different users with same role, they will see same data.

A new role can be created often - more than once per week.

[Anonymous]

@Stef_Farr You will need some way to configure in your database which Roles are associated with the relevant data, and also a UserRole table. Can you give an examle of a couple of Roles and what data they can see?

[Stef_Farr]

Hi @Anonymous . No unfortunately at this moment I cannot share data, as I am looking at my options to build the database/report around them.
But what you are telling me sounds interesting. Can you elaborate more please?

[Anonymous]

@Stef_Farr Obviously if your actual data is sensitive then anonymise it before posting. It will be almost impossible to help further without some details about how you have your data, Roles, and Users set up.

What is the data source for your Power BI report? Is it an Analysis Services tabular model, or an SQL Server database, or something else? Knowing this will affect what the solution will be.

When a user with Role XYZ runs the report, what sort of data can they see? Can they see all data in TablesA, TablesB, but no data from TableC? Or can they see only some rows from TableA, TableB? If that is the case, then how do you configure which rows each Role can see? It must be done somewhere? Or is this still to be decided?

[Anonymous]

@Stef_Farr Do the different roles have access to different Power BI reports?

Or do all users have access to all reports, but different roles can only see subsets of the data?

Will this project be Power BI Service, or Power BI Report Server? Or something else?