Common Dependent dimensions in data model

[billuran09]

Hello,

I have multiple fact tables.

Region, Department, Quantity

Region, Department, Sales

Have shared dimension tables for region and department

My issue i have a new mapping table that assigns further attributes to the regiona and department

Region, Department, Leader (of region department combo)

How can build Leader or other attricbutes to the fact tables for slicing and dicing and if I filter leader is shows me the measure.

1 obvious solution but i dont think it is right is to merge region department into 1 column for all tables and make a key off that, but i dont think thats best practice correct?

How can build Leader to the fact tables for slicing and dicing and if I filter leader is shows me the meausre.

[VasTg · Accepted solution]

@billuran09 

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.

[billuran09]

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.

[VasTg]

@billuran09 

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.