Filter Transformation Overview:
1. What is a filter transformation?
A filter transformation is used to filter out the rows in mapping. The filter transformation allows the rows that meet the filter condition to pass through and drops the rows that do not meet the condition. Filter transformation is an active transformation.
2. Can we specify more than one filter condition in a filter transformation?
We can only specify one condition in the filter transformation. To specify more than one condition, we have to use router transformation?
3. In which case a filter transformation acts as passive transformation?
If the filter condition is set to TRUE, then it passes all the rows without filtering any data. In this case, the filter transformation acts as passive transformation.
4. Can we concatenate ports from more than one transformation into the filter transformation?
No. The input ports for the filter must come from a single transformation.
5. How to filter the null values and spaces?
Use the ISNULL and IS_SPACES functions
Example: IIF(ISNULL(commission),FALSE,TRUE)
6. How session performance can be improved by using filter transformation?
Keep the filter transformation as close as possible to the sources in the mapping. This allows the unwanted data to be discarded and the integration service processes only the required rows. If the source is relational source, use the source qualifier to filter the rows.
A filter transformation is used to filter out the rows in mapping. The filter transformation allows the rows that meet the filter condition to pass through and drops the rows that do not meet the condition. Filter transformation is an active transformation.
2. Can we specify more than one filter condition in a filter transformation?
We can only specify one condition in the filter transformation. To specify more than one condition, we have to use router transformation?
3. In which case a filter transformation acts as passive transformation?
If the filter condition is set to TRUE, then it passes all the rows without filtering any data. In this case, the filter transformation acts as passive transformation.
4. Can we concatenate ports from more than one transformation into the filter transformation?
No. The input ports for the filter must come from a single transformation.
5. How to filter the null values and spaces?
Use the ISNULL and IS_SPACES functions
Example: IIF(ISNULL(commission),FALSE,TRUE)
6. How session performance can be improved by using filter transformation?
Keep the filter transformation as close as possible to the sources in the mapping. This allows the unwanted data to be discarded and the integration service processes only the required rows. If the source is relational source, use the source qualifier to filter the rows.
7. What is the Difference between source qualifier filter
and Filter Transformation?
Source qualifier filter only used for
relation source where as filter used any kind of source like Relational table
and flat file
Source qualifier filters data while
reading where as filter before loading into the Target.
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.
Subscribe to:
Post Comments (Atom)
Post a Comment