✍ Kashif M
Trends

Monday, 9 September 2013


SCD Type 2 in Informatica


By on September 09, 2013



SCD -2 Implementiation in informatica


1. We have a flat file as shown below


SCD Type 2  in Informatica
SCD Type 2  in Informatica












SCD Type 2  in Informatica
SCD Type 2  in Informatica


2. Pull the source (emp.txt) to Source Analyzer

3. Develop the Mapping

Go to Mappings > Wizards > Slowly Changing Dimensions

Enter the New Mapping Name as M_EMP_SCD_TYPE2 and 

choose 2nd Option Type 2 Dimension – Keep a full history of changes in the target click next

SCD Type 2  in Informatica
SCD Type 2  in Informatica

Select the Source file (FlatFile:emp) & enter the New Target Table Name as EMP_SCD_T



SCD Type 2  in Informatica
SCD Type 2  in Informatica



Click Next

Move the emp_id to Logical Key Fields

Move the Other columns to Field to compare for changes

SCD Type 2  in Informatica
SCD Type 2  in Informatica



Click Next



Choose the Third Option – Mark the dimension records with their effective date range

SCD Type 2  in Informatica
SCD Type 2  in Informatica



Click Finish

The Mapping will be created as below 

SCD Type 2  in Informatica
SCD Type 2  in Informatica

Give the Connection Information to lookup table here (LKP_GETDATA)



4. Generate and Execute the target table (EMP_SCD_T) in Target Analyzer 

5. Save the Record

6. Generate the Session and Workflow for this mapping

7. Run the Workflow

We got Output like



SCD Type 2  in Informatica
SCD Type 2  in Informatica



Now in Flatfile change the location for emp_id 5003

SCD Type 2  in Informatica
SCD Type 2  in Informatica

Run the Workflow, 

Output

SCD Type 2  in Informatica
SCD Type 2  in Informatica

Here new primary key is assigned for new record (5003) and also end date is set for old record



Kashif
mkashu: SCD Type 2 in Informatica
Review : Kashif | Kashif
Update: September 09, 2013 | Rating: 4.5

Comment for "SCD Type 2 in Informatica"

0 comments

Post a Comment

Blog Archive