4

I'm trying to understand how SCD Type 5,6 & 7 work.

I read this article of Kimball Group and stack overflow answer on Type 6.

I could understand Type 6 concept, how it works and when to use it.

However, I'm still unable to understand how type 5 & 7 work and when to use them. Explanation of type 5 & 7 with examples is highly appreciated.

Thanks in Advance.

Community
  • 1
  • 1
Ash
  • 1,180
  • 3
  • 22
  • 36

1 Answers1

6

I wouldn't worry too much- all the types above Type 3 have been called Type 6 at various times. Basically there are a range of techniques to deal with more complex history tracking, and it is up to you to pick the mix that works for your situation. Having said that, I'll have a go at giving more of an idea of Type 5 and 7 from this article:

Design Tip #152 Slowly Changing Dimension Types 0, 4, 5, 6 and 7

  • Type 5 is a variation on a 'Mini Dimension', whereby some of the attributes of a large dimension are subject to change but you don't want to do type 2 because the dimension has millions of rows. You break out those attributes into a dimension that is built like a junk dimension, and you can use the key of that table in the fact to track history. In the Type 5 variation, you include the new key in the dimension itself as a type 1 attribute, allowing you to query the dimension itself at any one time to find out the value of those attributes, without having to go via the Fact. For more info, google "mini dimension kimball".

  • Type 7 is a different way of achieving the same thing as Type 6, where you maintain the Type 1 version of things separately from the Type 2 version of things. Often the Type 1 version of things is created by using a view of the Type 2 version. By having both keys in the fact you can query how things were at the time of the fact and also how things were based on current versions of dimensions. It avoids the need to update the old values with the current state.

Rich
  • 2,207
  • 1
  • 23
  • 27
  • Thank you Rich. Will it be possible to get an example of Type 5 and Type 7? similar to https://www.packtpub.com/books/content/slowly-changing-dimension-scd-type-6 – Ash Apr 05 '17 at 01:17
  • The article itself does give brief examples. I don't have the time to go to 'book' length, although here's an old blog post of mine that covers type 7 https://richbackbi.wordpress.com/2010/09/29/making-your-dimensions-more-flexible-with-type-6/ , although confusingly I called it type 6 at the time. It might be easier for people to give you an answer if you can explain what parts you don't understand and need help with. – Rich Apr 05 '17 at 14:18