DimUserAccess Optimal Design

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?

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.

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.