The Filter transformation is an active and connected transformation within Informatica Cloud. Its purpose is to selectively remove data from the data flow based on predefined filter condition.
In the Filter transformation, the filter condition is an expression that evaluates to either TRUE or FALSE. By default, if no specific condition is defined, the filter transformation allows all records to pass through it, as the default return value is TRUE.
Likewise, by defining the filter condition as FALSE, the flow of the mapping is logically halted, preventing any records from proceeding further. This feature is useful for troubleshooting mapping logic issues.
2. Different types of Filter Conditions in Informatica Cloud
There are 3 different types of filter conditions in filter Transformation.
- Simple Filter
- Advanced
- completely Parameterized
Filter conditions are sensitive to case. Below are the operators you can utilize within the Filter transformation:
- = (equals)
- < (less than)
- > (greater than)
- < = (less than or equal to)
- > = (greater than or equal to)
- ! = (not equals)
3. Simple Filter Condition
You have the option to create one or multiple simple filter conditions. A simple filter condition comprises a field name, an operator, and a value.
When specifying multiple simple filter conditions, the mapping task evaluates them in the order you define, utilizing the AND logical operator.
Simple filter Condition – IICS
In the example provided, the filter condition will be interpreted as
DEPT_ID=’100′ and age >’25’
4. Advanced Filter Condition
You have the option to utilize an Advanced filter condition to create a complex filtering criteria. With an advanced filter condition, you can integrate multiple conditions using the AND or OR logical operators.
When transitioning from a simple to an advanced filter condition type, the Mapping Designer automatically converts the simple filter conditions into the advanced filter condition.
5. Completely Parametrized Filter condition
In the filter transformation, the filter condition can be fully parameterized by creating an input parameter of type expression. This parameter allows you to pass a value during mapping runtime or from the mapping task.
Moreover, you have the option to parameterize both the field name and its corresponding value, which can then be utilized in both simple and advanced filter conditions.
$DEPT_ID$=$DEPT_VALUES
In the provided example, two input parameters have been generated: one of type Field and the other of type String.
When utilizing parameters in the filter condition, the automatic conversion from a simple filter condition to an advanced filter condition is not supported. In such cases, you must manually input the condition.
6. Performance Tuning of Filter Transformation
- Place the Filter transformation as close to the source as feasible within the mapping. Doing so minimizes the number of rows forwarded to downstream transformations, enhancing efficiency.
- When dealing with relational sources, whenever feasible, integrate the filter condition directly within the source transformation. This approach decreases the volume of rows fetched from the source, optimizing performance.
7. FAQ's
The Filter transformation is used to selectively filter out records from a data flow based on specified conditions.
In the Filter transformation, you can create both simple and advanced filter conditions. Simple filter conditions involve a field name, operator, and value, while advanced filter conditions allow for more complex logic with the use of AND and OR operators.
Yes, you can fully parameterize filter conditions by creating input parameters of various types (e.g., field, string) to be used in defining the filter logic.
Placing the Filter transformation near the source in the mapping reduces the number of rows processed by downstream transformations, thereby improving overall performance and efficiency.
When dealing with relational sources, integrating filter conditions directly within the source transformation reduces the number of rows fetched from the source, leading to better performance and optimized resource utilization.
No, when parameters are used in filter conditions, the automatic conversion from simple to advanced filter conditions is not supported. In such cases, manual entry of the filter condition is required.
Pingback: Router Transformation - Informatica IICS | Syntax Minds