u/Workey_Turkey

My org is in the process of building out a data warehouse, and most of the semantic model work is being handled by BI. I’ve been tasked with building out some “datamarts” (not even sure that’s the right term), and I’m trying to make sure I’m structuring things the right way.

Background:

• We’re coming from Tableau, where most data sources are basically wide/flat tables
• No one on my team has much experience with proper semantic modeling yet
• The example I was given for a “datamart” looks very similar to a Tableau-style dataset (80+ columns)

The scenario:

I have an account-level fact table (1 row per account) with financial/sales data.

Each account is associated with 3 different employee roles:

• Sales Rep
• Account Manager
• Support Rep

For each of those roles, we currently have ~5 fields:

• Name
• ID
• Department
• Territory
• etc.

So in total, that’s ~15–18 employee-related columns sitting in the same table.

Instead of keeping all those columns in the account table, I’m thinking:

• Keep only:
  • Sales_Rep_ID
  • Account_Manager_ID
  • Support_Rep_ID
• Then have a separate Employee dimension table with:
  • Employee_ID
  • Name
  • Department
  • Territory
  • etc.

I believe this is called a role-playing dimension setup

Questions:

1. Is this the right approach? (i.e., moving employee attributes out of the fact table into a shared dimension)
2. How does this typically get exposed to end users in Power BI? For example, would I be able to present fields like:
   • Sales Rep Territory
   • Account Manager Territory
   • Support Rep Territory
   • even though they all come from the same underlying employee table?
3. From a usability standpoint, how would you organize this?
   • One big “Employees” folder with fields like:
     • Sales Rep Territory
     • Account Manager Territory
   • Or separate folders:
     • Sales Rep
     • Account Manager
     • Support Rep
     • With separate folders, is it possible to have them as subfolders under "Employees"?

Really trying to strike the right balance between:

• clean modeling
• and a UI that actually makes sense to analysts