DimUserAccess Optimal Design

[SamyAbdul]

Hi Experts, I have designed the DimUserAccess dimension which consists of 26 columns, it could be fair to assume that it is  wide table. Now, I have design quandry such as leave it as a single table or is it better to split it into two tables (DimUserAccess + UserAccessScope bridge table) and normalize. Please help me what approach would be in this scenerio. what are the pros and cons of each approach. Any other key considerations I should be mindful of. Thank a lot in advance.

[SamyAbdul]

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.

[oussamahaimoud · Accepted solution]

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.

[SamyAbdul]

Thank you ,happy to share the details:

1. Column categories (26 total)

• Identity (8): UserAccessKey, AccessGrantType, AccessSubjectType, UserPrincipalName, GroupObjectId, EmployeeId, DisplayName, PersonaRole
• Scope (6): AccessScopeType, PermittedSegmentCode, PermittedBranchRegionCode, PermittedSiteCode, PermittedBusinessUnitCode, SecurityTier
• Lifecycle flags (4): IsSOXUser, EffectiveFromDate, EffectiveToDate, IsActive
• Audit / Pipeline (8): ApprovalReference, SourceSystem, CreatedDateTime, CreatedBy, LastModifiedDateTime, LastModifiedBy, RecordHash, BatchId

1. Purpose

Dual - RLS + CLS enforcement at the semantic model layer, plus SOX audit trail. DAX resolves the authenticated user via USERPRINCIPALNAME(), looks up active entitlements at query time, filters rows by scope (RLS) and columns by SecurityTier (CLS via OLS). Not a reporting dimension - lives in a restricted security workspace, never exposed to consumers.

1. Cardinality

Low thousands - driven by active users × avg scope rows per user (typically 1–3). Estimated steady state 1k–10k rows. Well within CALCULATETABLE + IN safe range; TREATAS/INTERSECT documented as the upgrade path if that threshold is crossed.

1. Many-to-many - yes, intentional

One user can have multiple rows - one per AccessScopeType they hold (e.g. SEGMENT + BRANCH_REGION simultaneously). Each DAX pattern independently filters its scope type from _activeEntitlements and the union governs total visibility. A normalised bridge table approach (DimUserAccess + UserAccessScope) is flagged as the Phase 1 option if scope combinations grow complex.

1. Semantic model mode

Not locked in yet - DAX patterns are mode-agnostic. F32 dedicated capacity so Direct Lake is the target. Main thing to validate: Direct Lake → DirectQuery fallback behaviour under RLS, which needs explicit testing before go-live.

I appreciate your help alot.

[oussamahaimoud]

Can you share:

• What are the 26 columns? (or at least categorize them like : user identity attributes, permission flags, scope/hierarchy columns, date columns, etc.)
• What is this dimension used for? RLS enforcement? Reporting on access rights? Both?
• What is the cardinality? How many rows roughly, hundreds, thousands, millions?
• Is there a many-to-many relationship involved? For example, can one user have multiple scopes/roles, or is it strictly one row per user?
• What is your semantic model type? Import, DirectQuery, or Direct Lake?