Forum Discussion
Enterprise Data modelling and RLS best practices
Hi. I'm looking data modelling best practices for an enterprise. I have a data model with enterprise common master data tables (green), transactional tables (blue), enterprise structure table (Dim_Company) and Local dimension tables (Dim_Warehouse). The need is to apply RLS on Company level so that company user would see only relevant products, customers and warehouses. My current data model looks like this, but for Warehouse I cannot build both direction relation so all users see all warehouses.
Any suggestions for better modelling?
You only need to create a bridge for Fact Sales > <Bridge> > Dim Warehouse enable 2 way filtering.
1-2-many Fact to Bridge
1-to-many Bridge to Dim
Your RLS will filter Dim Company which will filter Fact Sales which will filter warehouse bridge which will filter Dim Warehouse.
3 Replies
Are you not able to filter both directions on Dim_Warehouse due to many 2 many relationship?
If so you can create a bridging table to enable that functionality.
Yes that exactly the case.
Should I create bridging tables for all dimensions or only Warehouse one? Fact table has 10 mio records, can double sided relations have impact on performance?
You only need to create a bridge for Fact Sales > <Bridge> > Dim Warehouse enable 2 way filtering.
1-2-many Fact to Bridge
1-to-many Bridge to Dim
Your RLS will filter Dim Company which will filter Fact Sales which will filter warehouse bridge which will filter Dim Warehouse.
