Regarding RLS setup

[Kiruthiga]

Hello Team,

Appreciate your time!! I am looking for some help on RLS setup. My fact table has view_dimension column with values 1 and 0. I am using a key called pg_key to connect from fact to dimension. I need to set up an RLS in my dimension table like when  view_dimension column is 1 then load the record for dimension if its 0 then dont load. Typically the PG key in dimension will be loaded for all the records in fact where view_dimension column in fact = 1. I have a fact table, dimension table, RLStable with selling_Vendor 

technically  when I login as an internal user I will see everything. when I login as a selling_Vendor for all the rows view_dimension =0 in fact, they shouldnt not see the records in the dimension table.,

appreciate your thoughts!!

Thank you!

Best,

Kiruthiga

[v-venuppu]

Hi @Kiruthiga ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @Kiruthiga ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @Kiruthiga ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu · Accepted solution]

Hi @Kiruthiga ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ray_aramburo for the prompt response.

Here are few steps to follow:

1.Ensure that the relationship between the fact table and the dimension table is set up correctly using pg_key.

2.Create a role in Power BI for selling vendors. This role will filter the dimension table based on the view_dimension column.

• Role = USERPRINCIPALNAME()

3.Define the DAX expression to filter the dimension table.

FILTER(
    DimensionTable,
    DimensionTable[pg_key] IN
    CALCULATETABLE(
        VALUES(FactTable[pg_key]),
        FactTable[view_dimension] = 1
    )
)

4.Apply the RLS role to the dimension table to ensure that only records with view_dimension = 1 are visible to selling vendors.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

 

[ray_aramburo]

Hi @Kiruthiga , if you already have a relationship between the RLStable with the fact table through a vendor ID, you can just create a role in the RLSTable like this: 

selling_Vendor = USERPRINCIPALNAME()

I'm assuming that the rows with view_dimension value = 0 would have a vendor ID in the RLStable. 

Just ensure that the relationship between RLStable and fact table has a one to many relationship, cross-filtering set to both and select Apply security filter in both directions.

Note: if the selling_Vendor is not an email column then use the column which has the email of the vendor in order for the USEPRINCIPALNAME() function to work.

[Kiruthiga]

Thank you Ray, Appreciate your time!! I am not exactly following you. I have vendor_num in both RLS and Fact.

Yes I am using the UserPrincipleName() to filter the vendor. 

So when I login as a test vendor, I will see say 100 records of that vendor in fact table.

In Fact I have view_dimension value = 0 /1 for every row.The fact  connect to dimension table using pg_key.

Now I need to populate all the 100 rows in fact and set pg_key to zero for dimension value = 0 in fact so it wont get values from dimension table or alternatively just load all the pg_key in dimension which exists in fact with  dimension value = 1 for this vendor

So basically when I login as vendor my fact will automatically reduce based on the vend_num in RLS which connects  to fact and now the dimension table need another extra filter  to load only values where dimension value = 1 in  the reduced set of fact for that vendor.

Fact Internal 1000 rows, Dimension Internal 1000 rows(eg)

Vendor1 Fact (100, with 75 rows with  view_dimension value = 1 and 25 rows with  view_dimension value = 0) now my vendor1 dimension should load only the 75 rows with view_dimension value = 1 in fact.,

Thanks a lot!

Kiruthiga

[ray_aramburo]

As far as my knowledge goes, Row Level Security doesn't work with query parameters, only with loaded data. So you necessarily need the 100 rows you are using as an example.  Filtering (even if transparent) happens after the data is loaded. 

[Kiruthiga]

Thank you , can't I use a filter in the  RLS DAx expression for dimension table some thing like 

below?

FILTER(
FACT,
FACT[VENDOR_KEY_PG_VIEW] = 1 && 

FACT[PG_KEY] == DIMENSION[PG_KEY])))>0 

something like that to filter the dimension table? 

Appreciate it and thank you!!

Kiruthiga