Forum Discussion
DimUserAccess Optimal Design
The solution is about keeping the single wide table because your cardinality (1k–10k rows), DAX RLS pattern, SOX atomicity requirement, and Direct Lake fallback risk all point in the same direction. The bridge adds complexity with zero benefit at your current scale. Revisit only if scope types multiply significantly.
Thank you . Please find the details :
Column categories 26
Identity 8,scope 6 life cycle flags 4 and Audit and Pipeline 8
Purpose : Dual -RSL and CLS enforcement at semantic model layer plus SOX audit trail, DAX resolves the authenticated user by USERPRINCIPALNAME
Cardinality :Low thousands , estimated steady state 1k to 10k rows
Many to Many intentional one user can have multiple rows, one for AccessScopeType they hold
Semantic Model Mode : Not locked in yet. Thank again and I appreciate your help.
The solution is about keeping the single wide table because your cardinality (1k–10k rows), DAX RLS pattern, SOX atomicity requirement, and Direct Lake fallback risk all point in the same direction. The bridge adds complexity with zero benefit at your current scale. Revisit only if scope types multiply significantly.