Filter Transformation In Informatica

Filter Transformation In Informatica 

Type: Active & Connected.

#Filter Transformation is used to filter rows that are passed through mapping. Specify the filter condition in properties tab of filter transformation and only rows that are satisfying the condition are passed through mapping to next transformation/target and rest are discarded.

#We can apply any condition that evaluates to either true or false
For ex: if you want to reject all the values whole department_id column contains null then apply below condition;

IIF(ISNULL(department_id),false,true)
Condition states that if department_id is null then return value should be set as false and row should be discarded.

 

#For better performance of mapping, apply filter transformation as nearer to Source Qualifier as it will filter the unwanted rows and hence will pass less records to another transformation.

#Also, you can apply filter condition in Source Qualifier transformation itself if it is a relational source. And its always better to apply filter condition by using SQL standard syntax as filter at database level is always faster than filter using transformation (Provided db & informatica have same configuration in terms of speed, processor & resources).

#But somewhere its not easy/possible to filter rows using normal SQL condition then you need use Filter Transformation.

#Important point to be noted is that, input ports coming to Filter transformation must come from single transformation. You can not connect port to it which are coming from multiple transformations.

Thanks for reading.
-Nitesh. 

 

 

Expression Transformation In Informatica

Expression Transformation In Informatica 

Type: Passive & Connected.

Expression transformation usually used to perform row-by-row processing from source to target/another transformation.

 It calculates value in single row that we can also store it in a variable.

It performs non-aggregate calculations such as Addition, Multiplication, UpperCase function etc..

Can also be used for conditional check for each row like IF, DECODE etc.

Complex expression can be handled efficiently by creating variables and then assigning value to another variable.

Ports:
Input
Output
Variable: Used to perform temporary calculations which will be stored in variable

Examples: Concatenating first_name & last_name,
                Average salary for each employee,
                Complex expression like calculating percentage etc..

Thanks for reading..

 
-Nitesh.