Forum Discussion
Post PatronAssistance with Modeling problem - should i use multiple fact tables?
I have a number of related entities, and im unsure of the best way to link them all.
The outcome will be a powerbi report where users can see Plans, PlanSteps, PlanOutcomes & Plan Step Outcomes.
I will need measures showing counts of PlanOutcomes under a plan, subplans under a plan & subplanSteps under a plan and a subplan.
the difficult i am having trouble figuring out is that As each sub entitty may or may not be assigned to a parent entitity, this can leave a lot of nulls in the fact table.
I am trying to use one fact table to store all info, but should i use multiple fact tables, one for unassigned objects, one for only assigned objects?
from a business perspective the model looks like this:
Plan can have 0 or more subplans
Plan can have 0 or more objectives
Subplan can be assigned to 0 or more plans
subplan can have 0 or more plan step objectives.
The below screenshot shows sample data in excel format and a rough ERD is below:
Appreciate expertise and advice on how to proceed.
2 Replies
v-lili6-msftCommunity Support
hi,wilson_smyth
when you modeling, Star model usually has better advantages. So you could merge sub-entity as far as possible.
and then create the relationship with the fact table. This will be easy for you to filter.
Best Regards,
Lin
wilson_smythThanks for the response Lin.
Star schema was the aim but the model is multiple many to many to many relationships so a star wouldnt allow complete flexibility.
I have given up trying to give complete reporting flexibility from one end of the model to the next, instead taking specific reporting requirements and creating smaller individual models. Most will be star schemas tied together by shared dimensions, but the granularity of the facts and the shared dimensions have yet to be agreed.
Ill post an update once its finalized, in hope it helps someone in future.