Physical Modeling › Tables › Accounting for Slowly Changing Dimensions

Accounting for Slowly Changing Dimensions

In dimensional modeling, dimension tables contain static (unchanging) data. However, information changes over time. For example, customer names and addresses change, company products evolve, and markets realign. Dimensions that change over time are referred to as slowly changing dimensions. In the data warehouse environment, accuracy of historical data can be as important as accuracy of current data.

Dimensional modelers suggest three ways of updating dimension information. Each choice provides a varying degree of information. In the Dimensional tab of the Table Editor, you can flag each dimension or outrigger table as fixed (unchanging) or slowly changing (Types 1, 2, or 3):

Type 1

Overwrites the old data in the record with the new data. When you select this type, you lose the ability to track the old data for the record.

Type 2

Creates an additional dimension record with the new data at the time of the change. When you select this type, you can accurately track history based on the old and new descriptions. However, it requires a generalized key to reference all iterations of the original record.

Type 3

Creates new fields in the record for the new data and the time of the change. When you select this type, you can only track original and current values; intermediate values are lost.

You can set the dimension type for any dimension table, regardless if it is automatically or manually assigned. You cannot select dimension type options if the dimensional modeling role is fact. The default dimension type is Fixed, unchanging.

Note: The Dimensional tab appears in the Table Editor only if you have selected to use Dimensional modeling features in the Model Editor.