Forum Discussion
Common Dependent dimensions in data model
You should use the RDL(Region;Department;Leader) as a bridge table between facts and dimension. For all the Region and Department combination, you should have a single row in this table(Leader column value should be unknown/undefined if there isn't any leader entry - but the row must exist)
Short forms:
RDL(Region;Department;Leader); Create a key column combining Region and Department, Lets call it as Bridge_key
R(Region)
D(Department)
Facts - Create a key column combining Region and Department in Sales and Quantity fact, Lets call it as Sales_key in sales fact and Quantity_key in Quantity fact.
Now define the relationships as below..
R->RDL(using Region ; 1 to Many)
D->RDL(Using Department; 1 to Many)
RDL to facts(Bridge_key to Sales_key and Quantity_key)
You should be able to filter by Leader(from RDL) or Region(from R) or department(from D).
If this helps, mark it as a solution. Reply if you have any questions.
Kudos are nice too.
Cheers, this is kind of what I was thinking just wasnt sure if creating a key column inside the facts table was best practice. IE merging the columns together.
Unfornately, you cannot define a relationship with composite key. You have to have a single column.
If this helps, mark it as a solution
Kudos are nice too.