DATA WAREHOUSING FUNDAMENTALS: A Comprehensive Guide for IT Professionals

11.2. UPDATES TO THE DIMENSION TABLES

Going back to Figure 10-4 of the previous chapter, you see the STAR schema for automaker sales. The fact table Auto Sales contains the measurements or metrics such as Actual Sale Price, Options Price, and so on. Over time, what happens to the fact table? Every day as more and more sales take place, more and more rows get added to the fact table. The fact table continues to grow in the number of rows over time. Very rarely are the rows in a fact table updated with changes. Even when there are adjustments to the prior numbers, these are also processed as additional adjustment rows and added to the fact table.

Now consider the dimension tables. Compared to the fact table, the dimension tables are more stable and less volatile. However, unlike the fact table, which changes through the increase in the number of rows, a dimension table does not change just through the increase in the number of rows, but also through changes to the attributes themselves.

Look at the product dimension table. Every year, rows are added as new models become available. But what about the attributes within the product dimension table? If a particular product is moved to a different product category, then the corresponding values must be changed in the product dimension table. Let us examine the types of changes that affect dimension tables and discuss the ways for dealing with these types.

11.2.1. Slowly Changing Dimensions

In the above example, we have mentioned a change ...