SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Slowing Changing Dimension (SCD) demystified – Part 3

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.

Ziek's SQL Home

Ezekiel is an IT professional with over ten years experience in building database applications and BI solutions. He holds a BSc Computer Science degree from KNUST, Ghana and a MSc Business IT degree from Kingston Business School, London. He has delivered solutions for the Insurance, Banking, Telecom and Housing Industries and his key expertise include developing Microsoft BI solutions using the full stack and C#, Business Process Improvement and Project Risk Management. He is a Microsoft SQL Server Certified Professional.


Leave a comment on the original post [learnsqlwithezekiel.wordpress.com, opens in a new window]

Loading comments...