Joining Dimensions in a data warehouse - is it possible?

santoshkumar · Post by **santoshkumar** » Tue Jan 02, 2007 3:06 pm

Hi All,

I have a situation wherein I need to look into probability of Joining 2 dimension in a data mart which we are designing.

I am exploring the possibility of joining two dimension using joiner table (this I am doing coz I need to show many- to- many relationship between these dimensions). i.e. this joiner table becomes a kind of fact table but not exactly one.

This joiner table can then be linked to the fact table as well.

Any one out here has tried doing it? What are challenges? Can it be done? What are limitations?

Suggestions if any would be highly appreciated.

Regards,
Santosh.

kumar_s · Post by **kumar_s** » Tue Jan 02, 2007 4:36 pm

If I understand properly, the joiner table will have the set of keys the links both dimension table. But let me know, why do you want to do this, if you can join the dimension tables directly using Fact tables. After all the dimensions can be purposefully used only after joining with fact table.

narasimha · Post by **narasimha** » Tue Jan 02, 2007 4:46 pm

If you have to, then there is no problem in joining the data between the two dimensions.
But you need to be careful here, because, you have an additional task to keep this new 'joiner table' up to date
with the changes to either of the dimensions.
You need to figure out a way to keep this new table updated.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 02, 2007 5:36 pm

Some authorities refer to this link table as a "factless fact table" - its purpose is to register the many-to-many relationship and nothing else.

Subsequently you may add some kind of measure to this table, but it doesn't need one.

But, as narasimha pointed out, the link table has to be kept up to date.