How to remove Null values using Filter Transformation?

In the Filter Transformation you can filter out rows having null values and spaces by using ISNULL and IS_SPACES functions.

For example , to filter out rows that contain null in EMP_NAME column us the below condition

IIF(ISNULL(EMP_NAME),FALSE,TRUE)

This condition says that if the Employee name is null then discard the row else pass it to the next transfomation

How to improve the performance of an Aggregator transformation?

• Use sorted input: Sorted input reduces the amount of data cached during the session and improves session performance. Hence use Sorter transformation and sort the data and pass to the Aggregator transformation.

• Less number of Input\Output Ports:Use less number of connected input/output or output ports to reduce the amount of data .The Aggregator transformation stores in the data cache.

• User Filter:In case if you are using Filter transformation use it before aggregator.This helps the Aggregator transformation to reduce unnecessary aggregation

Difference between Router and Filter transformation in Informatica

Router	Filter
Router transformation provides us the facility to capture the rows of data that do not meet any of the conditions to a default output group.	A Filter transformation tests data for one condition and drops the rows of data that do not meet the condition.
Router transformation is a single input and multi output group transformation.	Filter is single input and single output group transformation.
In a router transformation, you can specify more than one filter condition.	Filter transformation, you can specify only one filter condition.
The router transformation does not block input rows and those records that failed the filter condition will be passed to the default group	In a filter transformation there is chance that records get blocked
Router transformation acts like IIF condition in informatica or CASE.. WHEN in database.	Filter transformation works as WHERE clause of SQL .

Advantages of Router Transformation over Filter Transformation

• Better Performance; because in mapping, the Router transformation Informatica server processes the input data only once instead of as many times as you have conditions in Filter transformation.

• Less complexity; because we use only one Router transformation instead of multiple Filter transformation.

• Router transformation  is more efficient than the Filter transformation.

For Eg:

We have 3 departments in source now we want to send these records into 3 tables.To achieve this we require only one Router transformation.In case we want to get same result with Filter transformation then we require at least 3 Filter transformations.

Similarity:

A Router and Filter transformation are almost same because both transformations allow you to use a condition to test data.