Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Dynamic row level security with bidirectional filtering

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!

Need Help

2 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    HI Anonymous ,

    >>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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.