Dynamic RLS : directly on FACT table or not?

[denpries]

Dear all,

I have a dynamic RLS that does a lookup on email addresses. Basically, something like this in the roles model. 

fact[BU]
            = LOOKUPVALUE (
                access[BU],
                access[Email], USERPRINCIPALNAME (),
                access[BU], fact[BU]
            )

In this example, only rows of FACT are shown for each BU that is connected to the email address that corresponds with USERPRINCIPALNAME().

However, my fact table is 500k rows long. I dont notice any performance degradation, but i would like to know if i could better have the RLS on an in between table that contains all BU's (constructed from FACT table), and then have a relation from FACT to INBETWEEN while the security RLS limits the 

I see and know that it works, but i dont have a clue what is the better option and why

[v-yulgu-msft]

Hi @denpries,

I'm not very clear about what you said "have the RLS on an in between table that contains all BU's (constructed from FACT table)". Here is a blog provides a solution that create RLS via relationship.

Regards,

Yuliana Gu

[denpries]

What i mean is the following.

Either you have the RLS function on the FACT table. (500k rows). No relation table.

OR

you have the RLS function on a small table (20 rows) that contains all unique Business Unit items.

This 'in between' table has a relation with the FACT table Business unit field, so that if RLS says that only 10 out of 20 rows are valid to be seen, this travels down to the fact table using the rlation.