Dynamic row level security with bidirectional filtering

[cnweke]

Hi all,

I seem to be struggling implemeting dynamic RLS. My use case is simple:

A star schema with a fact surrounded by a dozen dimensions.

All the dimensions have at least one security key. (i.e. security on state level and then on store level)

I have bidrectional filters on at all tables connected to my fact as of now because it makes slicers better.

i.e. If you select state A from a slicer, the fact is filtered. In turn the fact table filters all dimensions so their slicer elements only contain non-null values for the slicer. A user from state A shouldn't see what the people of state B are selling.

Am I wrong in thinking that this filter kind of resembles SQL inner joins?

Now, my security table consists of 2 Keys (State / Store) and 0 or 1 which indicates whether to filter on State or Store level and a local users. The local users have been added to a security group where I've put the following dax filter:

= Tbl[Username] = username()

 From my albeit limited understanding of bidirectional filters I believe I just need to make a relationship between that table, a bridge (to circumvent many-to-many) and then my fact table which will then filter my other tables accordingly.

Another option would be to explicitly make relationships between my bridge and all my dimensions but I rather not if that's possible.

This doesn't seem to be doing a lot, does anyone have any idea what I might be forgetting?

Thanks in advance!

[TeigeGao]

HI @cnweke ,

>>Am I wrong in thinking that this filter kind of resembles SQL inner joins?

Yes, you can think it as an inner join, for example, if I select two value on column A in table a, then the filter on this column will transfer to another table b which has relationship between table a, the result will like:

select * from b
inner join a
on a.A = a.A
where a.A in (value 1, value 2)

bidirectional filters means that the filter can transfer from table a to table b, or table b to table a.

Best Regards,

Teige

[cnweke]

Now, how would you tackle a situation where I need to filter my fact table AND dimensions attributes.

- Put RLS on the fact and have bidirectional filters take care of the dimensions. (connecting my security table to only the fact).

- Putting RLS on all dimensions and then bidirectional filters on the fact. (Connecting the security table to everything but the fact).

Initially I wanted to do the latter but it seems to be giving me ambigious path errors. The former seems like it could be abused.