Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Duplicate dimensions for different facts?

Duplicate dimensions for different facts?

Duplicate dimensions for different facts?


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.



RE: Duplicate dimensions for different facts?

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.

RE: Duplicate dimensions for different facts?

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.


RE: Duplicate dimensions for different facts?

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.

RE: Duplicate dimensions for different facts?

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.

RE: Duplicate dimensions for different facts?


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,


RE: Duplicate dimensions for different facts?

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close