Create/Implement SCD - Informatica Mapping Wizard

Q) How to create different types of slowly changing dimensions (SCD) in informatica using the mapping wizard?

The Mapping Wizards in informatica provides an easy way to create the different types of SCDs. We will see how to create the SCDs using the mapping wizards in step by step.

The below steps are common for creating the SCD type 1, type 2 and type 3

Open the mapping designer tool, Go to the source analyzer tab and either create or import the source definition. As an example i am using the customer table as the source. The fields in the customer table are listed below.

Customers (Customer_Id, Customer_Name, Location)

Go to the mapping designer tab, in the tool bar click on Mappings, select Wizards and then click on Slowly Changing Dimensions.

Now enter the mapping name and select the SCD mapping type you want to create. This is shown in the below image. Then click on Next.

Select the source table name (Customers in this example) and enter the name for the target table to be created. Then click on next.

Now you have to select the logical key fields and fields to compare for changes. Logical key fields are the fields which the source qualifier and the Lookup will be joined. Fields to compare for changes are the fields which are used to determine whether the values are changed or not. Here i am using customer_id as the logical key field and the location as the field to compare.

As of now we have seen the common steps for creating the SCDs. Now we will see the specific steps for creating each SCD

SCD Type 1 Mapping:

Once you have selected the logical key fields and fields to compare for changes. Then you have to simply click the finish button to create the SCD Type 1 mapping.

SCD Type 2 Mapping:

After selecting the logical fields click on the next button. You will get a window where you can select what type of SCD 2 you want to create. For

• Versioning: select "keep the version number in separate column"
• Flagging: select "Mark the current dimension record with a flag"
• Effective Date: select "Mark the dimension records with their effective date.

Once you have selected the required type, then click on the finish button to create the SCD type 2 mapping.

SCD Type 3 Mapping:

Click on the next button after selecting the logical key fields. You will get window for selecting the optional Effective Date. If you want the effective date to be created in the dimension table, you can check this box or else ignore. Now click on the finish button to create the SCD type 3 mapping.

For more Visit  http://www.quontrasolutions.com/blog/category/informatica/