The Filter Transformation in Informatica filters the records based on the specified expression/condition. The Filter condition returns Boolean TRUE or FALSE, which means the integration service will check each row against the specified condition. And if the condition is TRUE, then the Informatica Filter transformation will pass the row. Otherwise, it will skip the current row and check the next one.
Filter Transformation in Informatica is very helpful in real-time. For example, If you want to load the customer data whose sales value is above the minimum threshold etc. In this article, we will perform Filter Transformation on two SQL tables.
For this Informatica Filter Transformation example, we are using the [Dim Products] and [fact Internet Sales] tables from our [Adventure Works DW 2014] database.
Filter Transformation in Informatica Example
Before we start doing anything, First connect to the Informatica repository service with your Admin Console credentials.
Create an Informatica Filter Transformation Source Definition
Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Filter Transformation example, we are using [Dim Products] and [fact Internet Sales] from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition.
Creating a Target Definition for Filter
Please navigate to Target Designer and define the Target. In this Informatica Filter Transformation example, we are using the already created SQL table (Filter Transformation) as our target definition. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition.
Create a Mapping for Filter
In order to create a new mapping for Informatica Filter Transformation, Please navigate to the Mappings menu in the Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below. Here, you have to write a unique name for this mapping (m_Filter_Transformation) and click the OK button.
TIP: Please refer Mapping article to understand the steps involved in creating Mapping.
Next, Drag and drop the [Dim Products] and [fact Internet Sales] source definitions from the Sources folder to the mapping designer. Once you drag the source, PowerCenter Designer will automatically create the default transformation called source qualifier, as shown below.
As we all know the [Dim Products] and [fact Internet Sales] tables have the Primary and Foreign key relationship. So we removed one source qualifier and added the required fields from both tables to a single source qualifier.
Creating a Filter Transformation in Informatica
In order to create a Filter transformation in Informatica, Please navigate to the Transformation menu in the Menu Bar and select the Create.. option.
Once you click on the Create.. option, the Create Transformation window will be opened as shown below. Please select the Informatica Filter Transformation from the drop-down list specify the unique name (fil_SalesAmount) for it and click on the Create button.
Once you click on the Create button, the Informatica filter transformation will be added to the mapping designer. In order to filter the records by an expression, filter Transformation requires some data. So, we have to connect the Source definition with the transformation using the Autolink.. option (or select and Drag the required fields).
Double-click on the Informatica Filter transformation to provide the filter condition. From below screenshot, you can see the list of available properties in the Transformation tab:
- Select Transformation: By default, it will select the transformation you clicked on.
- Rename: This button will help you to rename the filter transformation to a more meaningful name.
- Make Reusable: If you check mark this option, then this transformation will become a reusable transformation.
- Description: Please provide a valid description of this transformation.
The below screenshot will show you the list of available options in the Informatica Filter Transformation Ports tab:
- Port Name: List of available column names. You can add new columns by clicking the New Column button, and by clicking the scissors button, you can delete the unwanted columns.
- I: Columns check-marked under this section are the filter transformation Input columns.
- O: Columns check-marked under this section are the filter transformation Output columns. If you are unchecked any column, then that column will not be available to load in a target table.
The following screenshot will show you the list of available options in the Properties tab. By default, the Filter Condition will be set to TRUE, and here only, we have to provide the condition. In order to specify the condition, click on the Arrow button as shown below.
TIP: If you specify the Filter Condition to FALSE, then the filter condition will not allow a single record.
Once you click on the arrow button, the Expression Editor window will be opened, as shown below. Here you can write custom expressions using the available functions, Ports (Column Names), and Variables.
For this example, we are using the simple expression i.e., SalesAmount > 2000 as shown below. It means Informatica Filter transformation will return the records whose sales amount is greater than 2000
Once you finish configuring the properties, Click OK to close the transformation window.
Next, Drag and drop the target definition from the Targets folder to the mapping designer and connect the Transformation with the target definition. Please use the Autolink.. option to connect them.
Before we close the Mapping, Let us validate the mapping by going to the Mapping Menu bar and selecting the Validate option.
Create an Informatica Filter Transformation Workflow
Once we finish creating the Mapping, we have to create the workflow for it. PowerCenter Workflow Manager provides two approaches to creating a workflow.
In this Informatica Filter Transformation example, we will create the Workflow manually. To create a new Workflow, Please navigate to the Workflows Menu and select the Create option. This will open the Create Workflow window as shown below. Please provide the unique name (wf_Filter_Transformation) and leave the default settings.
Once we have created the workflow, our next step is to create a session task for our mapping.
NOTE: We strictly recommend you refer to the Workflow article to understand the steps involved in creating a Workflow manually.
Creating a Session
There are two types of sessions:
- Non-reusable Session Task: Please refer to Session
- Reusable Session Task: Please refer to Reusable Session
For this Informatica Filter Transformation example, we are going to create a Non-reusable Session. Please navigate to the Tasks Menu and select the Create option to open the Create Task window. Here you have to select the Session as Task type (default) and enter a unique name (S_Filter_Transformation) for the session.
Once you click the Create button, a new Mappings window will be opened. You must select the mapping you want to associate with this session. From the below screenshot, you can observe that we are selecting the mapping (m_Filter_Transformation) that we created earlier (in Step 3).
Double-click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we are explaining only a few properties. We strictly recommend visiting the Session article to understand the remaining properties.
From the below screenshot, you can observe that we assigned the $target variable to Connection Value, changed the Target Load Type option from Bulk to Normal mode, and check-marked the Truncate target table option to truncate the existing data from the destination table.
From the below screenshot, you can observe that the Filter Transformation in the Informatica workflow is a valid one. Now, Let us start the Workflow by navigating to the workflow menu and selecting the Start Workflow option.
Once you select the Start Workflow option, the PowerCenter Workflow monitor will open to monitor the workflow. From the below screenshot, you can observe that our workflow executes without any errors.
Let us open the SQL Server Management Studio to check whether we successfully restricted the records with Sales Amounts less than 2000 using the Informatica Filter Transformation or not.
