Forum Discussion
Advocate IModelling datawarehouse
Hello NEJO
First of all thanks for sharing this detailed question.
community user will be greatly benefited from such type of discussiona.
here are some of the best practices which I will try to follow in your case :
1.Keep separate Dimensions for Person and Team.
2. Use multiple foreign keys in the Fact for each person‐role or team‐role. Although it means some measures will require inactive‐relationship logic, it avoids blowing up the fact table and keeps the schema simpler.
3. Use Type 2 SCD within Person or Team if you need both “current” and “historical” attributes in the same dimension. That technique is standard in dimensional modeling and does not require bridging.
4. Only use a Bridge if you truly need to associate one fact row with a variable‐length set of persons or teams at once. Otherwise, the row explosion and complexity typically outweigh any gains.In short, most Kimball‐style models would maintain a standard star schema, keep Person and Team dimensions separate, and stamp each relevant foreign key onto the fact table (TimekeeperKey, SupervisorKey, etc.). Although this necessitates some role‐based DAX, it typically remains the most straightforward and performant solution.
Hope this helps.
Thanks
Community SupportHi NEJO ,
Thank you for sharing your detailed scenario. To the existing approach of maintaining multiple foreign keys in the fact table for different roles, you can try these steps to optimize performance in Fabric while ensuring flexibility.
To improve performance by reducing joins and minimizing the need for USERELATIONSHIP, here are a few suggestions:
-
Use Pre-Aggregated Tables for Common Queries:
Instead of running calculations dynamically for each role, pre-aggregated summary tables can store commonly used metrics like TimeWorked for different roles. This reduces the need for complex DAX and improves query efficiency. Fabric's Direct Lake mode can further enhance performance by allowing Power BI to query the data lake without import delays. -
Combine Data for Frequently Used Reports:
If certain reports frequently query both historical and current role-based data, creating a denormalized table that includes both sets of details can reduce joins. This approach avoids excessive relationship switching and speeds up reporting. -
Optimize Role-Based Metrics with Dynamic Selection:
Instead of duplicating measures for each role (e.g., TKWorkedAmount, CurrentTKWorkedAmount), a parameter table can be introduced to let users dynamically select which role-based metric to analyze. A SWITCH() based DAX measure can then return the appropriate result, simplifying the model.
Your current approach remains valid, and these optimizations can help enhance performance while keeping the model flexible.
Please let us know if you'd like more details or assistance in implementing these strategies in Fabric.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.
Best regards,
Vinay.