Pages

Thursday, March 14, 2013

Creating Lookup Transformation - Informatica

The steps to create a lookup transformation are bit different when compared to other transformations. If you want to create a reusable lookup transformation, create it in the Transformation Developer. To create a non-reusable lookup transformation, create it in the Mapping Designer. Follow the below steps to create the lookup transformation.

1. Login to the Power center Designer. Open either Transformation Developer tab or Mapping Designer tab.
Click on the Transformation in the toolbar, and then click on Create.

2. Select the lookup transformation and enter a name for the transformation. Click Create.

3. Now you will get a "Select Lookup Table" dialog box for selecting the lookup source, choosing active or passive option. This is shown in the below image:


4. You can choose one of the below option to import the lookup source definition:
  • Source definition from the repository.
  • Target definition from the repository.
  • Source qualifier in the mapping (applicable only for non-reusable lookup transformation)
  • Import a relational or flat file definition as the lookup source.

5. In the same dialog box, you have an option to choose active or passive lookup transformation. You can see this option in red circle in the above image. To make the lookup transformation as active, check the option "Return All Values on Multiple Match". Do not check this when creating a passive lookup transformation. If you have created an active lookup transformation, the value of the property "Lookup policy on multiple match" will be "Use All Values". You cannot change an active lookup transformation back to a passive lookup transformation.

6. Click OK or Click Skip if you want to manually add ports to lookup transformation.

7. For connected lookup transformation, add input and output ports.

8. For unconnected lookup transformation, create a return port for the value you want to return from the lookup.

9. Go to the properties and configure the lookup transformation properties.

10. For dynamic lookup transformation, you have to associate an input port, output port or sequence Id with each lookup port.

11. Go the condition tab and add the lookup condition.

No comments:

Post a Comment