Database Reference
In-Depth Information
new column (
PM_CURRENT_FLAG
) by maintaining the flag in the table to track
the changes. We use a new
PRIMARY_KEY
column to maintain the history.
Note
Use SCD2 mapping when you want to keep the full history of dimension data and
track the progression of changes using a flag.
•
Type 2 Dimension/Effective date range mapping
: This keeps current as well as
historical data in the table. SCD2 allows you to insert new records and changed
records using two new columns (
PM_BEGIN_DATE
and
PM_END_DATE
) by
maintaining the date range in the table to track the changes. We use a new
PRIMARY_KEY
column to maintain the history.
Note
Use SCD2 mapping when you want to keep the full history of dimension data and
track the progression of changes using
Start Date
and
End Date
.
•
Type 3 Dimension mapping
: This keeps the current as well as historical data in
the table. We maintain only partial history by adding a new
PM_PREV_COLUMN_NAME
column; that is, we do not maintain full history.
Note
Use SCD3 mapping when you wish to maintain only partial history.
Let's take an example to understand the different SCDs.
Consider that there is a
LOCATION
column in the
EMPLOYEE
table and you wish to track
the changes in the location of the employees. Consider a record for the
1001
employee
ID that is present in your
EMPLOYEE
dimension table.
STEVE
was initially working in
India and then was shifted to USA. We want to maintain the history in the
LOCATION
field.
EMPLOYEE_ID
NAME
LOCATION
1001
STEVE INDIA
