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.

oussamahaimoud's avatar
oussamahaimoud
Icon for Memorable Member rankMemorable Member
3 months ago

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?