Duplicate dimensions for different facts? 2

[Goodie666]

Hi,

I was wondering something but I can't find it in Kimball's books... If I have two facts that make use of the same dimension 'product', would I have one dimension 'product' or one dimension per fact table so that I can maintain them separately? I can see how this can be a pain in ETL to have to deal with another dimension but at the same time, if I only need to store 1 attribute for one fact table vs. 5 for another one it would (sort of) make sense no?

I'm curious to see how people handle this type of design questions.

Thanks,

Greg

 

[MDXer]

One Product Dimension that would be shared among your fact tables. In your dimension you would store all the attributes you need for analysis across all of your fact tables.

 

[Goodie666]

I see. Thanks, I was just wondering if some attributes would be Type 1 or 2 depending on the fact you are looking at, hence commanding for more than one dimension table.

greg

 

[MDXer]

The SCD Types do need to be a consideration when building your solution out but you should be able to implement SCD Logic into your solution.

 

[Hans63]

No, when looking at the SCD types, I think that SCD type 2 is the more general. So if you have a SCD type 2 dimension built, you should be able to use that on a different fact table where you would have need of a SCD type 1 only.

Typinhg this I wonder whether this should be dependend on teh fact tabel at all. Look at it from a conceptual point of view. What are the information needs for the attributes of your dimension. What does the business need? There should come your choice for SCD type 1 or 2 or 3.

 

[Goodie666]

Hi,

I understand that the business need drives this. My point was just about the fact that different facts may commend different SCD since they represent different business needs. Therefore I was wondering if that would be a potential issue. But I see how a Type 2 helps handling either case. Thanks,

Greg

 

[bootsminimus]

Hi Greg,

I'm coming in very late into this thread but you might like to look at the design tip #87 on the Kimball Group web site. See

http://www.rkimball.com/html/07dt/KU87CombiningSCDTechniques.pdf.

It explains here how it might be possible to combine SCD1 and SCD2 on the same dimension and when it might be appropriate to split them off as two separate dimensions.

Personally, I feel creating a SCD2 dimension that is up to the front end reporting to navigate appropriately when wanting an SCD1 attribute (as the above article explains) is complex.

Cheers,
Nigel