Speaking plainly about slowly changing dimensions.

Steven Ensslen
November 22, 2012

All too often experts over complicate problems and express them in obscure language.
As an expert in my field, I regularly use technical jargon because I need to be precise. There are circumstances when the difference between an indexed view and an un-indexed view is important. But there are more circumstances when the added detail only clutters the message.
One example that we continually encounter as business intelligence consultants, are the cryptic words that are used to describe slowly changing dimensions. One of the early experts in analytical processing determined the reasonable solutions to tracking updates to data. Unfortunately, he wasn’t good at communicating, so he numbered rather than named the solutions. So to this day, so-called experts inflate their egos and exclude their peers by discussing real and important business decisions with the words “type two” or “type three” slowly changing dimensions.
For those who haven’t learned the code, here is an explanation:

  1. Point in time
    Usually called “type 1” this idea is to keep no history of changes. The data that is currently correct is used, even when reporting on events from before the change.
  2. Full history
    Usually called “type 2” this idea is to keep all data, with date stamps that mark when each record was correct.
  3. Before and After
    Usually called “type 3” this idea is to keep one change per attribute and only for important attributes. So the data warehouse would have the original and most recent values, or the second most recent and most recent values, but any other historical values would be lost.
  4. Separate History
    Usually called “type 4” this idea is a hybrid of Point-in-time and Full-history. The main data is only what is current. The old values are kept in a separate but still online and accessible history.
  5. There is no type 5. Don’t blame the messenger.
  6. Hybrid
    There is a rarely used idea to combine full-history with before-and-after that is sometimes called “type 6”. In this idea there is a complete history of changes with date stamps and the currently correct record also contains some historical values within it.

At OptimalBI, we believe in making information visible. That starts by speaking clearly in a language that everyone can understand.

Copyright © 2019 OptimalBI LTD.