Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BMassey
Frequent Visitor

Row Level Security Question

Hello!  Need some RLS guidance.  Here is the scenario.

 

We have two projects, both pointing to the same dataset.

  • Project 1 - Contains reports that show high-level, summarized sales results.  For example, sales rankings for both individuals and teams, across the entire sales org.
  • Project 2 - Allows a sales leader to drill into their direct reports sales data.

We'd like to give all sales leaders full access (no filtering) within Project 1, so they can see where their Team and their team members rank across the sales org.  However, we'd like to filter out results in Project 2 so that sales leaders can only drill into the sales details for their direct reports.  

 

It appears that the security roles are applied at the Dataset level.  Therefore, I can't find a way to give John Doe full access to Project 1 and filter John Doe's results in Project 2, if both projects are using the same Dataset.  Is there a viable way around this or am I missing something?

 

We've thought about creating pre-aggregated Rankings tables that we would add to the Dataset.  These tables would not have a relationship to the filtered tables and Project 1 would leverage these tables.  However, this seems very messy and I think we should avoid doing this, if at all possible.

1 ACCEPTED SOLUTION

Hi @BMassey,

 

Check the similar thread below:

https://community.powerbi.com/t5/Desktop/Ignore-RLS-security-on-one-page-of-the-report/td-p/1168389

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

10 REPLIES 10
SidSingh
Frequent Visitor

Hi Good Morning Friends,

I have a similar senerio, where I am stuck in RLS for my dashboard, we have developed a Organization HR dashboard, which has employee data for all Business Units , Functions , Locations, Job Families, Area etc. The RLS requirement is at a very granular level, where a person can have access to all the data for a particular project, then he can also have access to another project only for a particular Location or a particular job family.

So, for these main 5-6 dimensions which we need to consider for security can have any combinations. Also in ' in the security data, for example where we have a employee with access to all projects, it is marked like "ALL Projects" in data, so when I can creating security relation it is have many combinations and data volume is increasing a lot, which is creating memory issue with Power BI and it is giving out of memory error..

Below is the Securiy sample data which I am using. Any help or advice to crack out this issue will be a great help.

Thank You in advance.

Capture.PNG

UserName ID Project Discipline Area Organization JobFamily Locations
EMP 1 1111 ALL Projects ALL Disciplines ALL AREAS ALL Organizations ALL Job Family New Delhi,Vadodara
EMP 1 1111 Project A ALL Disciplines ALL AREAS Shipping ALL Job Family All Locations
EMP 2 1112 Project B ALL Disciplines ALL AREAS Power Planning All Locations
EMP3 1113 Project C ALL Disciplines ALL AREAS Energy ALL Job Family New York
EMP 4 1114 ALL Projects HR ALL AREAS Automobiles Marketing All Locations

Sumanth_23
Memorable Member
Memorable Member

hi @BMassey - If I understand correctly then you do not need any security setup for Project 1. 

Also is the data for Project 1 & Project 2 residing in the same table?

I am thinking maybe creating a role which has access to Project 1 and access to Project 2 is limited to employees where reporting manager = person who has logged in. 

I have just created a dummy role with access to State "AL" and in CA state has only access to Sales Territory "Far West"

 

Sumanth_23_0-1601323518150.png

 

You can also refer to the steps as per the Microsoft documentation to create and assign members to specific roles for RLS to work as expected.
https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

Also RLS is only applied to folks with "Viewer" access to reports / workspace; RLS will not apply to users with other levels of access.


Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



Hi @Sumanth_23!  Thanks for the info.  

 

Yes, Project 1 and Project 2 read from the same tables.  I believe that's the root of the issue we're running into.  We want Sales Leader John Doe to have full visibiliity in Project 1 but only see his team in Project 2.  Both projects are using the same dataset and the same tables are used to build out the reports in both projects.  So, it seems like we would need to assign John Doe a different role for each project but it doesn't appear that's possible when they are both using the same dataset.

 

I'll take a look at the links you provided and see if that helps us with overcoming this issue.

 

 

hi @BMassey - As per the screenshot from my previous post, you can try and create 1 role where the user has access to All records of Project 1 OR access to records from Project 2 where supervisor name is = person logged into the report. 

That role should ideally provide the security setup you are looking for. 

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



Hi @Sumanth_23!  Thanks for your responses.  Here is some additional context regarding our scenario.

 

This is a high level summary of the star schema design.

BMassey_0-1601389887201.png

This schema is used for both projects.  We've applied the filter below on the Dim_SalesHierarchy table.  We've also created a "No Filtering" role, which has no filters applied.  

BMassey_2-1601389975843.png

 

Both Project 1 and Project 2 are using the same Dataset.  When applying the "Filter Based on SalesHierarchy" role to one of the sales leaders, it's impacting their visibility into reports on both projects.  We want to allow them to see the high-level, aggregated numbers from Project 1 (without filtering) but filter their detailed reports in Project 2.  Currently, the results on both reports are being filtered.

 

If nothing else, duplicate the dataset in Power BI and apply RLS only to one of them.

Thanks @TCavins!  Yes, we've discussed that as an option and would really prefer to avoid doing that, if at all possible.  However, we're struggling finding another viable option that doesn't include duplicating the dataset.

Hi @BMassey,

 

Check the similar thread below:

https://community.powerbi.com/t5/Desktop/Ignore-RLS-security-on-one-page-of-the-report/td-p/1168389

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Thanks @v-kelly-msft! I appreciate you pointing that post out to me.  

 

We've been talking about having a seperate dataset, which we really don't want to do.  This option, while not ideal, would at least allow us to keep everything in a single dataset.  I'll bring this option back to the team and discuss it with them to get their feedback.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors