A Slowly Changing Dimension (SCD) is
a dimension that stores and manages both current and historical data over time
in a data warehouse. It is considered and implemented as one of the most critical
ETL tasks in tracking the history of dimension records.
There are three types of SCDs and you
can use Warehouse Builder to define, deploy, and load all three types of SCDs.
The three types of SCDs are:
In a Type 1 SCD the new data overwrites the existing data.
Thus the existing data is lost as it is not stored anywhere else. This is the
default type of dimension you create. You do not need to specify any additional
information to create a Type 1 SCD.
A Type 2 SCD retains the full history of values. When the
value of a chosen attribute changes, the current record is closed. A new record
is created with the changed data values and this new record becomes the current
record. Each record contains the effective time and expiration time to identify
the time period between which the record was active.
A Type 3 SCD stores two versions of values for certain selected
level attributes. Each record stores the previous value and the current value
of the selected attribute. When the value of any of the selected attributes
changes, the current value is stored as the old value and the new value becomes
the current value.
No comments:
Post a Comment