Row level Security SSAS

[Anonymous]

I need some help for an RLS design implemented in the SSAS tabular model.

In our current solution, we have 7 different roles and have defined row level security DAX filters in each of the roles. 

To give more context, for example,these are some roles and corresponding DAX filters and members:

Region role:

= 'Dim Region'[DimRegionID]=LOOKUPVALUE('Sec UserRegion'[Dim_RegionID],
'Sec UserRegion'[UserID], USERNAME(),
'Sec UserRegion'[Dim_RegionID], 'Dim Region'[DimRegionID])
 on Dim Region table

SubRegion role:

='Dim SubRegion'[DimSubRegionID]=LOOKUPVALUE('Sec UserSubRegion'[Dim_SubRegionID],
'Sec UserSubRegion'[UserID], USERNAME(),
'Sec UserSubRegion'[Dim_SubRegionID], 'Dim SubRegion'[DimSubRegionID])
on Dim Sub Region table

Offering and Region role:

='Dim Offering'[OfferingID]=LOOKUPVALUE('Sec UserOfferingRegion'[DimOfferingID],

'Sec UserOfferingRegion'[UserID], USERNAME(),
'Sec UserOfferingRegion'[DimOfferingID], 'Dim Offering'[OfferingID])
on Dim Offering table

='Dim Region'[DimRegionID]=LOOKUPVALUE('Sec UserOfferingRegion'[DimRegionID],
'Sec UserOfferingRegion'[UserID], USERNAME(),
'Sec UserOfferingRegion'[DimRegionID], 'Dim Region'[DimRegionID])

on Dim Region table

And we have the same users AD group that is added to each of these roles as members.

Here is the security model schema:

We have security tables (in red box) that contain the user ID- region or sub region mapping

The security tables are connected the corresponding dimension tables (blue box) which in turn is connected the fact tables (green box)

The design works fine for measures or data within the bottom 5 fact tables (Headcount Sub Region, POR, Sales Pipeline, Billed AR, Financials) i.e. users can see data corresponding to their access.

However, the top 2 tables Headcount Region and Sales Budget that is connected to Dim Region and Sec Region tables don't function as expected. For a user who has say access to only Americas region is able to view global data or all regions data only for these 2 tables. These 2 tables arrive at the highest hierarchy i.e region and other tables arrive at subsequent hierarchies sub region and MHL04 levels. 

The issue we're facing is:

1- For a user who has an entry in the Sec Region table say john@abc.com -> Americas

2- The region dimension gets filtered to Americas

3- Fact data  for Headcount Sub Region, POR, Sales Pipeline, Billed AR, Financials which arrive subsequent hierarchies sub region and MHL04 levels gets filtered correctly

4- Whereas Fact data for Headcount Region and Sales Budget  which arrive highest hierarchy region level doesn't get filtered correctly for Americas and user can see all regions data.

Can you please help with what could be the reason and/ or if this is incorrectly designed?

Appreciate your feedback so we can resolve this security breach we're currently facing. Happy to share more details and answer further questions you may have.

[v-xicai]

Hi @Anonymous  ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Anonymous ,

If you need to set RLS, please see the link Power BI Desktop Dynamic security cheat sheet, which described the detailed steps. Maybe it doesn't work, there are some tips to let it work and test it efficiently. You can also refer to this case .

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.