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:
- 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)
- 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.
I hope I could explain the situation well. I am eager to know what you think about this technique as well ;)