Sunday, 6 November 2016

Filter Transformation in Informatica - Explanation

Filter Transformation:


  • It is Active Transformation and Connected Transformation.
  • It is used to filter the input data or to remove the unwanted data based on the given condition.

  • Filter Transformation returns 2 values internally. i.e., True or False
  • It returns true for a record if it satisfies the condition. It returns false for a record which does not satisfies the given condition.
  • Filter Transformation receives record by record as input, applies the specific condition. It allows the record which satisfies the condition otherwise it rejects them.




Important notes on Filter Transformation:
  1. Condition in the filter can be defined with multiple ports.
  2. In Informatica Level, we call the columns as 'Ports'.
  3. To improve the performance while designing the mappings, use this transformation very close to the source because it will filter the unnecessary records in the starting itself.
The third point is very important for the people who attend the interviews on Informatica as it is asked in many interviews in various companies. 

Filter Transformation Real Time Scenarios with example:


Design a mapping to load only the employees whose salary is greater than 300.

Solution:
1. Create a target with the name EMP_FILTER with the same structure as EMP table using target designer.
2. Create a mapping with the name m_filter
3. From the repository drag the source(emp) and drag the target(emp_filter)
4. From the transformations menu, select create and then select the transformation type as filter from the drop down list.
5. Enter a new name for this transformation as Fil_Sal. Create and Done.
6. Connect all the ports of SQ to filter transformation.
Select the properties tab. Set the following property as shown below:
In the transformation attribute, you will find the filter condition and write the condition as 'sal>3000'
7. Apply Ok.
8. Connect the ports of filter transformation to the target and save the mapping.
9. Create a session and workflow and execute it.

No comments:

Post a Comment