Filter Transformation in Informatica is an active transformation, which is used to filter (or) removing the unwanted data on condition that we created using Ports, Operator and operand elements.
Filter Transformation in Informatica
Filter Transformation returns ‘True’ or ‘False’. True indicates that an Input record meets the given condition and the record is delivered for ‘loading’. False indicates, that an input records does not meet the condition and record is delivered for ‘Reject’.
Filter Transformation with Example
In this filter transformation example, we are going to create Filter Employees who’s Salary greater than 2000. Bases on the source data, we can filter the data. If source is a relational, we can filter the rows using source qualifier filer. If source is a flat file we use filer transformation.
- Creating Mapping.
- Creating Transformation.
- Adding filter condition.
- Creating workflow.
- Creating session.
- Validating the session.
- Run workflow.
- Monitor workflow.
Creating Filter Transformation Mapping
To create Mapping for any transformation, source and target must be selected. Here our source is EMP and Target is T_EMP.
Drag and drop source table and target table from repository navigator window to workspace window. When a source table is inserted in the workspace, automatically SQ_EMP is inserted. SQ means source qualifier.
Creating Filter Transformation
Navigate to transformation tab to create filter transformation. A new window will be opened with two options, select transformation type as Filter and enter the name for transformation.
Adding filter condition.
In this example, our main purpose is to filter employees whose salary >= 2000. Select the required fields from SQ_EMP and drop those fields into Filter transformation.
Now Double click on the filter transformation header and click on properties tab to add filter condition using expression editor. In this expression editor, select ports tabs, double click on SAL and enter condition as SAL <= 2000 as shown below.
- Click on validate to check the condition is valid or not and finally apply the condition.
Adding Port from Filter Transformation to Target Table
Already we have added target table from navigator to workspace window, select required ports from the filter transformation and drop the ports into target table (T_EMP) as shown below.
- Go to repository menu to Save.
Creating Workflow and Session for Filter Transformation
Now we are going to create filter workflow for our transformation. To create workflow in Informatica PowerCenter, open Informatica PowerCenter Workflow manager.
- Navigate to tools | Workflow designer | Create and enter the name for workflow.
Now our main step is to create a session for our transformation. Session in Informatica is a set of instruction which defines how and when to move the data from Source definition to target definition. To create a session, go to tasks | click on create.
Enter a name for your session and connect with workflow using link task a shown below.Now we are going to configure mapping for our session by editing session. Double click on session and navigate to Mappings tab.
- Click on Connection and select the value as shown below.
- Now select database connection for source and targets. In target, change target load type form build to normal and finally click on Ok button.
- Go to workflow and click on validate to check our session is valid or not.
- Save the workflow settings.
Run Filter Transformation Workflow
To run a workflow in Informatica PowerCenter, navigate to Workflow and click on start workflow. Now our workflow will be executed and the status will be monitored in Workflow monitor. Open Informatica PowerCenter Workflow monitor to check workflow is executed successfully or not.
As shown above, in source table we 14 rows, after applying filter transformation with condition SAL >= 2000, 6 rows has been loaded successfully into target table and no row is rejected.
Check Filter Transformation Output in SQL Developer
Open SQL developer in your local machine and open target table as shown below.
