Database Reference
In-Depth Information
Chapter 3. Implementing SCD - Using
Designer Screen Wizards
Slowly Changing Dimensions
(
SCD
), as the name suggests, allows you to maintain
changes in the
Dimension
table in
Datawarehouse
. Before you read this chapter,
make sure that you have a complete understanding of data warehousing concepts, espe-
cially SCD. Also, make sure you know the SCD1, SCD2, and SCD3 types. For your refer-
ence, we have described each SCD in detail in this chapter. For more details, refer to
The
Data Warehouse Toolkit
,
Ralph Kimball
,
Wiley India Private Limited
. Before we move
ahead with the implementation of the SCD in Informatica Power Center, let's discuss the
different types of SCDs.
Note that we are talking about the general SCDs in our discussion, that is, SCD1, SCD2,
and SCD3. Apart from these, there will always be Hybrid SCDs, which you will come
across as well. Hybrid SCDs are nothing but a combination of multiple SCDs that serve
your complex business requirements.
The various types of SCDs are described as follows:
•
Type 1 Dimension mapping (SCD1)
: This keeps only the current data and does
not maintain historical data.
Note
Use SCD1 mapping when you do not want to keep the history of the previous data.
•
Type 2 Dimension/Version Number mapping (SCD2)
: This keeps current as well
as historical data in the table. SCD2 allows you to insert new records and changed
records using a new column (
PM_VERSION_NUMBER
) by maintaining the version
number in the table to track the changes. We use a new
PM_PRIMARYKEY
column
to maintain the history.
Note
Use SCD2 mapping when you want to keep the full history of the dimension data
and track the progression of changes using a version number.
•
Type 2 Dimension/Flag mapping
: This keeps the current as well as historical data
in the table. SCD2 allows you to insert new records and changed records using a
