FILTER TRANSFORMATION IN INFORMATICA

Filter transformation is an active, connected transformation. The filter transformation is used to filter out rows in a mapping. As the filter transformation is an active transformation, it may change the number of rows passed through it. You have to specify a filter condition in the filter transformation. The rows that meet the specified filter condition are passed to other transformations. The rows that do not meet the filter condition are dropped.

Creating Filter Transformation

Follow the below steps to create a filter transformation

1. In the mapping designer, open a mapping or create a new mapping.
2. Go to the toolbar->click on Transformation->Create->Select the filter transformation
3. Enter a name->Click on create and then click on done.
4. You can add ports either by dragging from other transformations or manually creating the ports within the transformation.

Specifying Filter Condition

To configure the filter condition, go to the properties tab and in the filter condition section open the expression editor. Enter the filter condition you want to apply. Click on validate button to verify the syntax and then click OK.

Components of Filter Transformation

The filter transformation has the following components.

• Transformation: You can enter the name and description of the transformation.
• Ports: Create new ports and configure them
• Properties: You can specify the filter condition to filter the rows. You can also configure the tracing levels.
• Metadata Extensions: Specify the metadata details like name, datatype etc.

Configuring Filter Transformation

The following properties needs to be configured on the ports tab in filter transformation

• Port name: Enter the name of the ports created.
• Datatype, precision, and scale: Configure the data type and set the precision and scale for each port.
• Port type: All the ports in filter transformation are input/output.

Performance Tuning Tips

• Use the filter transformation as close as possible to the sources in the mapping. This will reduce the number of rows to be processed in the downstream transformations.
• In case of relational sources, if possible use the source qualifier transformation to filter the rows. This will reduce the number of rows to be read from the source.

Note: The input ports to the filter transformation mush come from a single transformation. You cannot connect ports from more than one transformation to the filter.

Filter Transformation examples

Specify the filter conditions for the following examples

1. Create a mapping to load the employees from department 50 into the target?

department_id=50

2. Create a mapping to load the employees whose salary is in the range of 10000 to 50000?

salary >=10000 AND salary <= 50000

3. Create a mapping to load the employees who earn commission (commission should not be null)? 

IIF(ISNULL(commission),FALSE,TRUE)