Dimensions that change over time are called Slowly Changing Dimensions. For instance, a product price changes over time; People change their names for some reason; Country and State names may change over time. These are a few examples of Slowly Changing Dimensions since some changes are happening to them over a period of time
While handling Slowly changing Dimension, Dimesion schema might required to change. It depends on Business Requirement.
E.g Dimension Table Product has Product ID and Price. If Price changes , if we update the Price in the Dimension we might end up in loosing History Data. In this case we can Add One Column as Dateof Change. So if Price Changes for the given date, 1 Record gets added in to Dimension while keeping the history intact.
The definition of slowly changing dimension is in its name only. The dimension which changes slowly with time. A customer dimension table represents customer. When creating a customer, normal assumption is it is independent of time. But what if address of customer changes or name of customer changes?
This change can be implemented in three ways...
Type I: Replace the old record with a new record with updated data, there by we lose the history. But data warehouse has a responsibility to track the history effectively, where Type I implementation fails.
Type II: Create a new additional dimension table record with new value. By this way we can keep the history. We can determine which dimension is current by adding a current record flag or by time stamp on the dimensional row.
Type III: In this type of implementation we create a new field in the dimension table, which stores the old value of the dimension. When an attribute of the dimension changes then, we push the updated value to the current field and old value to the old field.
