Slowly changing dimension

Common examples of SCDs include geographical locations, customer details, or product attributes.

Each type offers a trade-off between historical accuracy, data complexity, and system performance, catering to different analytical and reporting needs.

If a salesperson is transferred to a new office, historical sales reports need to reflect their previous assignment without breaking the relationships between the fact and dimension tables.

If the supplier relocates the headquarters to Illinois the record would be overwritten: The disadvantage of the Type 1 method is that there is no history in the data warehouse.

For example, if the supplier relocates to Illinois the version numbers will be incremented sequentially: Another method is to add 'effective date' columns.

Transactions that reference a particular surrogate key (Supplier_Key) are then permanently bound to the time slices defined by that row of the slowly changing dimension table.

To reference the entity via the natural key, it is necessary to remove the unique constraint making referential integrity by DBMS (DataBase Management System) impossible.

This can be an expensive database operation, so Type 2 SCDs are not a good choice if the dimensional model is subject to frequent change.

In the following example, an additional column has been added to the table to record the supplier's original state - only the previous history is stored.

Logically, we typically represent the base dimension and current mini-dimension profile outrigger as a single table in the presentation layer.

If the outrigger approach does not deliver satisfactory query performance, then the mini-dimension attributes could be physically embedded (and updated) in the base dimension.

One possible explanation of the origin of the term was that it was coined by Ralph Kimball during a conversation with Stephen Pace from Kalido[citation needed].

Ralph Kimball calls this method "Unpredictable Changes with Single-Version Overlay" in The Data Warehouse Toolkit.

For example, if the supplier were to relocate again, we would add another record to the Supplier dimension, and we would overwrite the contents of the Current_State column: In many Type 2 and Type 6 SCD implementations, the surrogate key from the dimension is put into the fact table in place of the natural key when the fact data is loaded into the data repository.

While more complex, there are a number of advantages of this approach, including: The following example shows how a specific date such as '2012-01-01T00:00:00' (which could be the current datetime) can be used.