I am wrapping up this series by finally considering the rarely used type 3 SCD.
( i.e. in most business scenarios)
Unlike the Type 2 SCD, the type 3 SCD tracks only the current value and the original value of a dimension member.
Two attributes are normally used in designing a type 3 SCD, these are the start date and the initial value attributes.
The Start Date refers to the date the current value of the dimension member became active.
The initial value refers to the original value of the attribute.
Let examine a fictitious small company, ABC (see the data table below) that has changed locations on a number of
occasion over a period of time due to say favourable tax regimes.
From the data table, it is clear that the initial location (value) of the company is Leeds while the current location is now London and this change took place on the 15/05/2012. Thus we know what the initial value is and when the current location became active. Again we are unable to track historical changes particularly when the company changes location again.
NB: IsCurrentLocation is a boolean column that indicates which location is the current one; 0 = False, 1 = True
pkID is the primary key for the data table
Hope you found this useful and thanks for reading.