Wednesday, November 11, 2009

Predictable Changes with Multiple Version Overlays

There is a technique in Kimball's "Data Warehouse toolkit 2nd" book that is suggested for Predictable Changes with Multiple Version Overlays situations. It was a bit intangible for me what Kimball means for that solution, but I could understand the technique when I give another example to myself ;)

Imagine that we offer variety of products with different categories. Each product could just be belong to one category, and the category of products could be changed at the beginning of each year. However, the category of products could be changed by CEO over the time. It is ok if we want to use Slowly Changing Dimension 2 (SCD2) for this example, but the business requirements that I mention as the following make it impossible to consider this dimension as SCD2.

Imagine we need to answer these questions:
  1. What is the sale of a category over a period of time? (This is not the case that makes it impossible to consider the dimension as SCD2)
  2. What is the sale of the selected category in question one on the next two years? (This is a real trouble)

It may be a bit vague, so I am going to explain second question. Imagine the sales of category 1 in 2002 is X. Analysts may be interested to know what is the sale of category 1, with the products at the time of question 1, in another period of time. We should consider this fact that some category of products is changed over time; hence, how could it be possible to reflect this information.


This is the situation that we must follow what Kimball says. Consider a column for category of each year in product dimension table.



Product D
------------------

ProductID

Category 2001
Category 2002
Category 2003


I hope I could explain the situation well. I am eager to know what you think about this technique as well ;)