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
Super UserHello 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
NEJOThanks for the quick response nilendraFabric
I think what you're suggesting - is pretty much how I intially modelled it in the SSAS Tabular model. As I look to migrate to Fabric - was hoping there was a more optimal way.
If I look at my scenarion for 3 profiles (I think we have 6) (Timekeeper, Supervisor, RelationshipManager), and one metric - I'll end up with something like this.
The fact table would need to contain..
- TimekeeperKey
- CurrentTimekeperKey
- SupervisorKey
- CurrentSupervisorKey
- RelationshipKey
- CurrentRelationshipKey
- TimekeeperTeamKey
- CurrentTimekeperTeamKey
- SupervisorTeamKey
- CurrentSupervisorTeamKey
- RelationshipTeamKey
- CurrentRelationshipTeamKey
One metric - say TimeWorked
I'll need the ability to report out the total TimeWorked on any given profile, but also will need to report out against the person and team details at the point of the transaction, but also grouped up to the current person and team details depending on the requirement for the output - so the join to person and team requires a different key for current.
This seems to create the need to have the TimeWorked metric duplicated for all profiles
TKWorkedAmount
CurrentTKWorkedAMount
etc.
Needs must, and I'm happy with that approach if it really is the most optimal. I suppose i'm trying to increase the performance as much as possible here for the end user, reducing joins, reducing the need for USERELATIOSHIPS (which I dont think are the most optimimal when it come to performance), whilist also trying to allow for as many scenarios to be catered for.
Thanks