Filter transformation in Informatica
Filter transformation in Informatica >> How to Set Up a Filter Condition >> Tips for Creating Effective Filter Conditions
Filter transformation is used to filter rows in a mapping based on a specified condition. Its active Transformation. Only the rows that meet the condition pass through the transformation to the next stage in the data flow. Rows that do not meet the condition are discarded or deleted and that rows are not maintained or stored anywhere.
Here are the key components and features of the Filter transformation:
1. Condition: A filter condition is specified using an expression that returns either TRUE or FALSE. The condition can use one or more fields from the input rows and can include various operators and functions to define complex standards.
2. Transformation Type: It is an active transformation, meaning it can change the number of rows that pass through it. or the number of rows coming from the transformation is not equal to the number of rows going out of the transformation called active transformation.
3. Ports: The Filter transformation contains only input and output ports. It does not have any separate filter ports; the condition is applied to the input data directly.
4. Performance: Applying filters early in the data flow can improve performance by reducing the number of rows that subsequent transformations need to process.
5. Use Case: Common use cases for the Filter transformation include data validation, data cleansing and extracting specific subsets of data based on business rules.
Example
Suppose you have a dataset of customer orders and you want to filter out only those orders with a total amount greater than Rs 10000. You would use a Filter transformation with a condition like:
###################
ORDER_TOTAL > 10000
###################
In a Informatica mapping, you would:
A. Add the Filter transformation to your mapping.
b. Define the filter condition in the Filter transformation.
c. Connect the input ports from the source to the Filter transformation.
d. Connect the output ports from the Filter transformation to the next transformation or target.
This way, only the orders with a total amount greater than Rs 10000 would be passed to the next stage in the data flow.
How to Set Up a Filter Condition in Informatica
A filter condition in Informatica specifies the criteria used by the Filter transformation to determine which rows to pass through and which to discard. The condition is defined using an expression that evaluates to either TRUE or FALSE for each row. Rows that evaluate to TRUE are passed through, while those that evaluate to FALSE are deleted.
1. Add a Filter Transformation:
– In the mapping designer, add a Filter transformation to your mapping.
2. Define the Filter Condition:
– Open the properties of the Filter transformation.
– In the “Filter Condition” property, enter the expression that defines your criteria.
Syntax and Examples
The expression syntax for filter conditions in Informatica is similar to SQL and supports a wide range of operators, functions, and expressions.
Example 1: Simple Numeric Comparison
To filter rows where the ‘SALARY’ column is greater than 5000:
######################
SALARY > 5000
######################
Example 2: String Comparison
To filter rows where the `STATUS` column is equal to ‘Active’:
#####################
STATUS = ‘Active’
#####################
Example 3: Combining Conditions with AND/OR
To filter rows where ‘SALARY’ is greater than 50000 and ‘DEPARTMENT’ is ‘Sales’:
#########################
SALARY > 5000 AND DEPARTMENT = ‘Sales’
#########################
Example 4: Using Functions
To filter rows where the ‘JOIN_DATE’ is after May 5, 2024:
##################################
TO_DATE(JOIN_DATE, ‘MM/DD/YYYY’) > TO_DATE(’05/05/2024′, ‘MM/DD/YYYY’)
##################################
Example 5: Checking for NULL Values
To filter rows where the ‘SALARY’ is not NULL:
################
ISNULL(SALARY) = FALSE
################
Steps to Define a Filter Condition in Informatica
1. Open Informatica PowerCenter Designer** and create or open the mapping you want to modify.
2. Add the Filter Transformation**:
– Drag the Filter transformation icon to the workspace.
– Connect the transformation from the preceding transformation or source qualifier.
3. Configure the Filter Transformation**:
– Double-click on the Filter transformation to open its properties.
– Go to the “Properties” tab.
– In the “Filter Condition” field, enter the condition expression.
4. Connect Output Ports**:
– Connect the output ports of the Filter transformation to the next transformation or target.
Tips for Creating Effective Filter Conditions
– Optimize Conditions: Place the most restrictive conditions first to reduce the number of rows processed.
– Use Functions Wisely: Avoid using complex functions if simpler conditions can achieve the same result, as functions can add overhead.
– Validate Expressions: Ensure that the expression syntax is correct and that all referenced columns and functions are valid.
– Test Thoroughly: Validate the Filter transformation by running the mapping and verifying that the output meets the expected criteria.
Using Filter transformations effectively improves the performance and accuracy of your data integration workflows in Informatica.
Filter transformation in Informatica Filter transformation in Informatica
Also Read :
What is a Synchronization task in Informatica?
What is a Runtime environment in Informatica?
What is Check In Check Out in Informatica powercenter (Versioning)