Database Reference
In-Depth Information
PM_PRIMARYKEY to handle the issues of duplicate records in the primary key in the
EMPLOYEE_ID column and PM_BEGIN_DATE and PM_END_DATE to understand the
versions in the data.
The advantage of SCD2 is that you have the complete history of the data, which is a must
for data warehouses, whereas the disadvantage of SCD2 is that it consumes a lot of space.
Take a look at the following SCD3 table:
PM_PRIMARYKEY
EMPLOYEE_ID
NAME
LOCATION
PM_PREV_LOCATION
100
1001
STEVE USA
INDIA
As you can see, we are maintaining the history by adding a new column to maintain the
history. An optional PM_PRIMARYKEY column can be added to maintain the primary key
constraints. We add a new PM_PREV_LOCATION column in the table to store the
changes in the data. As you can see, we added a new column to store data as against
SCD2, where we added rows to maintain the history.
If STEVE is now shifted to JAPAN , the data changes to:
PM_PRIMARYKEY
EMPLOYEE_ID
NAME
LOCATION
PM_PREV_LOCATION
100
1001
STEVE JAPAN
USA
As you can see, we lost INDIA from the data warehouse, and that is why we say we are
maintaining partial history.
Note
To implement SCD3, decide how many versions of a particular column you wish to main-
tain. Based on this, the columns will be added in the table.
SCD3 is best when you are not interested in maintaining the complete history but are in-
terested in maintaining only partial history. The drawback of SCD3 is that it doesn't store
the full history.
Search WWH ::




Custom Search