Forum Discussion

SamyAbdul's avatar
SamyAbdul
Frequent Visitor
3 months ago
Solved

DimUserAccess Optimal Design

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 ...
governance
one lake
security
  • oussamahaimoud's avatar
    oussamahaimoud
    3 months ago

    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's avatar
SamyAbdul
Frequent Visitor
3 months ago

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.