Forum Discussion
data model for multiple fact tables
Hi i'm trying to put together a dashboard for work however the model now appears to be getting particuarly messsy as there's now 3 fact tables. I've unhidden all the columns for ease of reading.
Is anyone able to suggest a more competent model?
2 Replies
Here's a picture of the model.
Hi, Jameswh91
When you have a many-to-many relationship between dimension-type tables, we provide the following guidance:
- Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
- Add a bridging table to store associated entities
- Create one-to-many relationships between the three tables
- Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
- When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
- Hide the bridging table (unless it contains additional columns or measures required for reporting)
- Hide any ID columns that aren't suitable for reporting (for example, when IDs are surrogate keys)
- If it makes sense to leave an ID column visible, ensure that it's on the "one" slide of the relationship—always hide the "many" side column. It results in the best filter performance.
For more details, please refer to the following documents.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
