Slowly Changing Dimensions – Type One Models

Type One – Overwrite the Record

Type one modeling of slowly changing dimensions is very simple and effectively handles updates to incorrect and/or outdated values within the dimension table. Type one models do not retain a history of changes and do not store previous values in any way. With this modeling technique, the data field that has changed is simply updated to reflect the most current value.  Since this technique does not preserve any previous value, it should only be used when there is no requirement to retain historical data.

Type one models makes sense when correcting data issues, but not when the system requires retrieval of historical values. Without a doubt, type one is the simplest method for handling slowly changing dimensions. However, best practice is to utilize this technique on a very limited basis. Moreover, it should only be implemented when there is no need to track the history of changes.

Example of Type One

First an electronics company loads its list of products within its data warehouse. Later the company discovers that a television which was originally manufactured in the USA is now manufactured in China, and the company has in fact never received a shipment of the older American-made televisions. The product description column in the table is simply updated, and the date and time of the update is entered in the “Last Update” column.

To correct the product description, the existing description is simply overwritten with the correct value, and the “Last Update” field is revised with the current date. Moreover, the product description is updated because the item is no longer manufactured in the United States. Utilizing the type one method of managing slowly changing dimensions, the record on the product dimension table is updated in-place in the following manner…

–  The product description is updated in-place to the new description.

–  The last update is updated to the date and time of the change.