Database Reference
In-Depth Information
Your
datawarehouse
table should reflect the current status of
STEVE
. To implement
this, we have different types of SCDs.
Take a look at the following table of type SCD1:
PM_PRIMARY_KEY
EMPLOYEE_ID
NAME
LOCATION
100
1001
STEVE USA
As you can see,
INDIA
will be replaced with
USA
, so we end up having only current data,
and we lose historical data. Now, if
STEVE
is again shifted to
JAPAN
, the
LOCATION
data will be replaced from
USA
to
JAPAN
, as follows:
PM_PRIMARY_KEY
EMPLOYEE_ID
NAME
LOCATION
100
1001
STEVE JAPAN
The advantage of SCD1 is that we do not consume a lot of space to maintain the data; the
disadvantage is we don't have the historical data.
Take a look at the following table of type SCD2, where we have added the version num-
ber:
PM_PRIMARYKEY
EMPLOYEE_ID
NAME
LOCATION
PM_VERSION_NUMBER
100
1001
STEVE INDIA
0
101
1001
STEVE USA
1
102
1001
STEVE JAPAN
2
200
1002
MIKE UK
0
As you can see, we are maintaining the full history by adding new records to maintain the
history of the previous records. We add two new columns in the table, that is,
PM_PRIMARYKEY
to handle the issues of duplicate records in the primary key in the
