SCD Type1

SCD-TYPE1
In Type 1 Slowly Changing Dimension, the new information simply

overwrites the original information. In other words, no history is kept.
In our example, recall we originally have the following table:
Customer Key
Name
State
101
SARADA
New York
After SARADA moved from New York to Los Angeles, the new
information replaces the new record, and we have the following table:
Customer Key
Name
State
101
SARADA
Los Angeles
Advantages
This is the easiest way to handle the Slowly Changing Dimension
problem, since there is no need to keep track of the old information.
Disadvantages
All history is lost. By applying this methodology, it is not possible
to trace back in history. For example, in this case, the company
would not be able to know that SARADA lived in New York before.
Implementation of SCD type 1 by using the customer table as an
example. The source table
CREATE TABLE CUSTOMERS
(
CUSTOMER_ID Number,
CUSTOMER_NAME Varchar2(30),
LOCATION
Varchar2(30)
)
The TARGET table
CREATE TABLE CUST_SCD1
(
CUST_KEY NUMBER PRIMARY_KEY,
CUSTOMER_ID Number,
CUSTOMER_NAME Varchar2(30),
LOCATION
Varchar2(30))
Steps to Create SCD Type 1 Mapping
Create SCD Type 1 mapping in informatica
Create the SOURCE (CUSTOMERS) and TARGET (CUST_SCD1) tables
in the database.
Open the Informatica Designer -> source analyzer and import the
source table from database.
Go to the Target designer and import the Target table.
Go to the mapping designer tab and create new
mapping(m_scd_type1).
Drag the source(CUSTOMERS) into the mapping.
Go to the toolbar, Transformation.
Select the lookup Transformation, enter a name (LKP_SCD1) and click

on create.
Select the CUST_SCD1 table and click on OK.
Edit the LOOKUP transformation, go to the Ports tab, and add a new
port In_CUSTOMER_ID. This new port needs to be connected to the
CUSTOMER_ID port of Source Qualifier transformation.
Go to the condition tab of lkp transformation and enter the lookup

condition as CUSTOMER_ID = IN_CUSTOMER_ID. Then click on OK.
Connect the CUSTOMER_ID port of Source Qualifier transformation

to the IN_CUSTOMER_ID port of LOOKUP Transformation.
Create the EXPRESSION transformation with input ports as
CUST_KEY, CUSTOMER_Name, LOCATION, SRC_CUSTOMER_NAME,
SRC_LOCATION and output ports as NEW_FLAG, CHANGED_FLAG
For the output ports of EXPRESSION transformation enter the below

expressions and click on OK.
NEW_FLAG = IIF(ISNULL(CUST_KEY),1,0)
CHANGED_FLAG
=
IIF(NOT
ISNULL(CUST_KEY)
AND
(CUSTOMER_NAME !=
SRC_CUSTOMER_Name OR
LOCATION != SRC_LOCATION),1, 0 )
Now connect the ports of LKP transformation (CUST_KEY,
CUSTOMER_NAME, LOCATION) to the Expression transformation
ports (CUST_KEY, CUSTOMER_NAME, LOCATION) and ports of
Source Qualifier transformation(CUSTOMER_NAME, LOCATION) to
the Expression transformation ports(SRC_CUSTOMER_NAME,
SRC_LOCATION) respectively.
Now create ROUTER transformation and drag the following attributes from expression to router
transformation.
CUST_KEY,SRC_CUSTOMER_ID,SRC_CUSTOMER_NAME,SRC_LOCATION,NEW_FLAG,CH
ANGED_FLAG.
Create two groups in ROUTER one for INSERT and another one for UPDATE.
In Insert group, set the condition NEW_FLAG=1
Update group, set the condition CHANGED_FLAG=1
Now create an UPDATE STRATEGY transformation (UPD_SCD1) and

connect all the ports of the Router Insert group (except the
CUST_KEY,NEW_FLAG,CHANGED_FLAG ports) to the update
strategy.
Go to the properties tab of update strategy and enter the update

strategy expression as DD_INSERT
Now create an update strategy transformation (UPD_UPDATE)aand

connect all the ports of the Router Update group (CUST_KEY,
CUSTOMER_NAME, Location) to the update strategy.
Go to the properties tab of update strategy and enter the update

strategy expression as DD_Update
Create Sequence Generator Transformation (SEQ_SCD1)

Connect NEXT VAL port to CUST_KEY port of Target
Table(CUST_SCD1).
Now Connect the remaining appropriate ports from update strategy
(UPD_SCD1)to the target definition(CUST_SCD1).
Connect the appropriate ports from update strategy(UPD_UPDATE)

to the target definition(CUST_SCD11).
The complete mapping diagram is shown in the below image.
Create work flow with session (s_m_scd1) for this mapping and assign source and target
relational connections and run the workflow.
Source Data
After 1st Run
the
workflow
the source data
loaded in to
target table
Update
LOCATION
column data
for
CUSTOMER_ID=102 in Source(CUSTOMERS)
THEN Source Data should be displayed as follows
After 2nd Run the workflow the source data loaded in to target table
as follows
After
sarada
moved
from
banglore
to
GNT,
the new
information replaces the new record, and we have the following data.

SCD Type1

Uploaded by

Copyright:

Available Formats

SCD Type1

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SCD Type1

Uploaded by

Copyright:

Available Formats

SCD-TYPE1

In Type 1 Slowly Changing Dimension, the new information simply

Go to the toolbar, Transformation.

Select the lookup Transformation, enter a name (LKP_SCD1) and click

Go to the condition tab of lkp transformation and enter the lookup

Connect the CUSTOMER_ID port of Source Qualifier transformation

For the output ports of EXPRESSION transformation enter the below

Now create an UPDATE STRATEGY transformation (UPD_SCD1) and

Go to the properties tab of update strategy and enter the update

Now create an update strategy transformation (UPD_UPDATE)aand

Go to the properties tab of update strategy and enter the update

Create Sequence Generator Transformation (SEQ_SCD1)

Connect the appropriate ports from update strategy(UPD_UPDATE)

The complete mapping diagram is shown in the below image.

THEN Source Data should be displayed as follows

You might also like