To filter out rows containing null values or spaces, use the ISNULL and IS_SPACES functions to test the value of the port. For example, if we want to filter out rows that contain NULLs in the FIRST_NAME port, use the following condition:
IIF(ISNULL(FIRST_NAME),FALSE,TRUE)
This condition states that if the FIRST_NAME port is NULL, the return value is FALSE and the row should be discarded. Otherwise, the row passes through to the next transformation.
Rank Transformation in Informatica , is a connected and active /Passive transformation which select top/bottom rows of input. It is something similar to Rank analytical data function or oracle. Only difference is that, it also filter out the remaining rows (which are not a part of top/bottom threshold).
Lookup Transformation in Informatica , is a connected/Unconnected and Passive (post 9 Active and Passive transformation) which let you look up data flat file/relation tables ,views or synonym.The Integration Service queries the lookup source based on the lookup ports in the transformation and a lookup condition. Lookup transformation returns the result of the lookup to the target or another transformation.
You can use lookup Transformation in mapping for below type of tasks:
Getting related value: Retrieve value from the lookup table based on a value in the source. For example, the source has an employee ID. Retrieve the employee name from the lookup table.
Perform a calculation: Retrieve a value from a lookup table and use it in a calculation. For example, retrieve a sales tax percentage, calculate a tax, and return the tax to a target.
Update slowly changing dimension tables: Determine whether rows exist in a target and accordingly you can create a new record or update the existing one.
Normalizer Transformation in Informatica , is a connected and active transformation which let you to normalize your data by receiving a row with information scatter in multiple columns to multiple row a for each instance of column data.For example a student have score for each subject scattered in 5 columns ,with the help of normalizer transformation you can create multiple rows for each subject (Normalization of Database) .
Normalizer Transformation help in normalizing your data , making it for flexible by eliminating redundancy and inconsistent dependency. It help you representing data in more better manner.
You can also select Transformation by clicking function button on Informatica Designer
Enter a name and click Done.
You can create ports in Normalizer transformation by selecting and dragging it from input transformation to Normalizer transformation.You can manually create input ports on the Normalizer Ports tab by double clicking on it.
Normalizer Transformation step 5
Click the Transformation tab and configure transformation properties. ( change Tracing Level as per your need)
Click OK.
Connect output ports of Normalizer Transformation to other transformation or target
Union Transformation in Informatica , is a connected and active transformation which let you to merge data from multiple pipelines or pipeline branches into one pipeline branch. Union Transformation merges data of similar source based on UNION ALL SQL statement.
Union Transformation is a Active transformation as it combines two or more data streams into one. Although no of rows from multiple input and Output is same, but order in which these row are coming to output can change.
The Integration Service processes all input groups in parallel. It reads sources connected to the Union transformation and pushes blocks of data into the input groups of the transformation. The Union transformation processes the blocks of data based on the order it receives the blocks from the Integration Service irrespective.
Use the following procedure to create an Union transformation in Informatica mapping.
Steps to create an Union transformation:
In the Mapping Designer, open a Mapping.
Click Transformation > Create. Select Union transformation.
You can also select Transformation by clicking function button on Informatica Designer
Enter a name and click Done.
You can create ports in Union transformation by selecting and dragging it from input transformation to Union transformation.You can manually create input ports on the Group Ports tab by double clicking on it.
Union Transformation step 3
Double-click the title bar of the Union transformation to open the transformation
Click the Groups tab, and then click the Add button to create a user-defined group.The Designer assigns a default name for each group but they can be renamed
Click the Transformation tab and configure transformation properties. ( change Tracing Level as per your need)
Click OK.
Connect group output ports of Union Transformation to other transformation or target
Router Transformation in Informatica , is a connected and active transformation which let you to transfer data to multiple target depending upon the different condition. A Filter transformation rejects the row which don’t satisfy its condition whereas Router Transformation allows you to pass the failed data to some default target as well.
Unlike Filter Transformation , Router Transformation provide you the flexibility of populating multiple target depending upon the different type of conditions. It also allow you to add multiple condition for each target .Failed records can also be extracted from the default group.
You can also select Transformation by clicking function button on Informatica Designer
Enter a name and click Done.
You can create ports in Router transformation by selecting and dragging it from input transformation to router transformation.You can manually create input ports on the Ports tab by double clicking on it.
Double-click the title bar of the Router to open the transformation
Click the Groups tab, and then click the Add button to create a user-defined group.
To add Group
Click the Groups tab.
Click the Add button.
Enter a name for the new group in the Group Name section.
Click the Group Filter Condition field and open the Expression Editor.
Enter the group filter condition.
Click Validate to check the syntax of the condition.
Click OK.
Router Transformation step 4
The Designer creates the default group when you create the first user-defined group
Click the Transformation tab and configure transformation properties. ( change Tracing Level as per your need)
Click OK.
Connect group output ports of router to other transformations or targets
Note: After you make the transformation reusable, you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation.
Joiner Transformation in Informatica , is a connected and active transformation which let you join data from two heterogeneous source (same source system or different source system). The Joiner transformation joins sources with at least one matching column.
As the name predict itself , the Joiner transformation is use to join data from two heterogeneous sources or data from the same source. The Joiner transformation uses a condition that matches one or more pairs of columns between the two sources. Out of two input , one is consider as master pipeline ( it is loaded first in the data cache) , then the other one is Detail pipeline which is joined with the Master data.
Components of Joiner Transformation in Informatica
Joiner data cache: Joiner always caches the MASTER table. We cannot disable caching. It builds Index cache and Data Cache based on MASTER table.
Join Type in informatica : With the help of Join Type , we can control the output of the join condition. We will discuss it in more detail.
Master/Detail source port: While adding source ports into joiner , we need to defined master and detail source by clicking on the Master check box. To make one source as master , we can check any port of the source on the “M” check box, it will automatically mark the check for remaining port as well
Sorted input in joiner transformation : Select this option to improve session performance. To use sorted input, you must pass data to the Joiner transformation sorted by joining port, in ascending or descending order.
You can configure the Joiner transformation components and options on the Properties and Ports tab.
Normal Join in joiner transformation
A normal join will allow only those records which satisfy the joiner condition for both sources. So remaining records, who don’t match the condition get discarded
Master Outer Join in joiner transformation
A master outer join will keeps all rows of data from the detail source and the matching rows from the master source. If any of master records don’t satisfy the condition , those rows get discarded.
Detail Outer Join in joiner transformation
Just opposite to Master Outer join , it keep all rows of data from Master Source and the matching rows from Details Source. If any of detail records don’t satisfy the condition , those rows get discarded.
You can also select Transformation by clicking function button on Informatica Designer
Enter a name and click Done.
Drag all the input/output ports from the first source into the Joiner transformation. The Designer creates input/output ports for the source fields in the Joiner transformation as detail fields by default. You can edit this property later .
Select and drag all the input/output ports from the second source into the Joiner
transformation.The Designer configures the second set of source fields and master fields by default.
Double-click the title bar of the Joiner transformation to open the transformation
Click any box in the M column to switch the master/detail relationship for the sources.
Tip: To improve performance for an unsorted Joiner transformation, use the source with fewer rows as the master source. To improve performance for a sorted Joiner transformation, use the source with fewer duplicate key values as the master.
Click the Add button to add a condition. You can add multiple conditions. The master and detail ports must have matching datatypes.
Joiner transformation only supports equivalent (=) joins.
Click the Properties tab and configure properties for the transformation (as Mentioned above)
Connect the output ports to a downstream transformation or target.
Aggregator Transformation in Informatica , is a connected Active transformation which let you performs aggregate calculations, such as averages and sums on the group of data. Aggregator transformation is differ from Expression transformation , as you use Aggregator transformation to perform calculations on groups . The Expression transformation permits you to perform calculations on a row-by-row basis only.
Aggregator Transformation is a Active transformation as it also enable you to use conditional clauses to filter rows.
Is Aggregator Transformation Connected or Unconnected ?
Aggregator Transformation is a connected Transformation.
Components of Aggregator Transformation:
Aggregate cache: The Integration Service stores data in the aggregate cache until it completes aggregate calculations. It stores group values in an index cache and row data in the data cache.
Aggregate expression: Enter an expression in an output port. The expression can include non-aggregate expressions and conditional clauses.
Group by port: Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.
Sorted input: Select this option to improve session performance. To use sorted input, you must pass data to the Aggregator transformation sorted by group by port, in ascending or descending order.
You can configure the Aggregator transformation components and options on the Properties and Ports tab.
Configuring Aggregator Transformation Ports:
You can configure the following components on the ports tab
Port name: Add the name of the port.
Datatype, precision, and scale: Configure the datatype and set the precision and scale for each port.
Port type : A port can be input, output, input/output, or variable. The input ports receive data and output ports pass data. The output ports can pass aggregated data (use Aggregate function in Expression ). Variable ports store data temporarily and can store values across the rows.
Expression: Use the Expression Editor to enter expressions. Expressions use the transformation language, which includes SQL-like functions, to perform calculations. Example ( sum , Max,average)
GroupBy: Indicate how to create groups. The port can be any input, input/output, output, or variable port. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified
You can also select Transformation by clicking function button on Informatica Designer
Enter a name and click Done.
Select and drag the ports from the source qualifier or other transformations to add to the Aggregate transformation. You can also open the transformation and create ports manually.
Double-click on the title bar and click on Ports tab. You can create output and variable ports within the transformation.
Select the Ports tab.
Aggregator Transformation Step 4
Click the group by option for each column you want the Aggregator to use in creating groups. Optionally, enter a default value to replace null groups.
Click Add to add an expression port. The expression port must be an output port. Make the port an output port by clearing Input (I).
Optionally, add default values for specific ports.If the target database does not handle null values and certain ports are likely to contain null values, specify a default value.
Aggregator Transformation Step 5
Configure properties on the Properties tab.
Aggregator Transformation Step 6
To enhance the performance of Aggregator , it is recommended to provide Sorted Data to it (Via SQ query or by adding Sorter Transformation before it)
In case Sorted Input data is coming to Aggregator, check the “Sorted Input” option under the properties Tab.
Click OK.
Connect the output ports to a downstream transformation or target.
Expression Transformation in Informatica , is a connected and passive transformation (number of input and output rows is the same), which let you modify individual ports of a single row , or add or suppress them. For example: calculating annual Salary, concatenation. In below article we will go through the properties of Expression Transformation.
The Expression transformation in informatica is use to perform non-aggregate calculations for each data. Data can be modified using logical and numeric operators or built-in functions . Sample transformations handled by the expression transformer are :
Data Manipulation : concatenation( CONCAT or || ) , Case change (UPPER,LOWER) truncation, InitCap (INITCAP)
You can also select Transformation by clicking function button on Informatica Designer
Enter a name and click Done.
Select and drag the ports from the source qualifier or other transformations to add to the Expression transformation. You can also open the transformation and create ports manually.
Double-click on the title bar and click on Ports tab. You can create output and variable ports within the transformation.
In the Expression section of an output or variable port, open the Expression Editor.
Enter an expression. Click Validate to verify the expression syntax.
Click OK.
Assign the port datatype, precision, and scale to match the expression return value.
To make it reusable ,check the reusable option in the edit properties.
Configure the tracing level on the Properties tab.
Click OK.
Connect the output ports to a downstream transformation or target.
After you make the transformation reusable, you cannot copy ports from the source qualifier or other transformations. You can create ports manually within the transformation
Expression Transformation Components or Tabs
An Expression transformation contains the following tabs:
Transformation: Enter the name and description of the transformation. The naming convention for an Expression transformation is EXP_TransformationName. You can also make the transformation reusable.
Ports: Create port and configure them.
Properties: Configure the tracing level to determine the amount of transaction detail reported in the session log file.
Metadata Extensions: Specify the extension name, datatype, precision, and value. You can also create reusable metadata extensions.
Configuring Ports:
You can configure the following components on the ports tab
Port name: Add the name of the port.
Datatype, precision, and scale: Configure the datatype and set the precision and scale for each port.
Port type : A port can be input, output, input/output, or variable. The input ports receive data and output ports pass data. The input/output ports pass data unchanged. Variable ports store data temporarily and can store values across the rows.
Expression: Use the Expression Editor to enter expressions. Expressions use the transformation language, which includes SQL-like functions, to perform calculations.
Default values and description: Set default value for ports and add description
Filter Transformation can be added to the current by clicking on Transformation – > Create
Selecting the Filter Transformation and the create
Then we need to give some name , add the required input/output ports.
In the property tab , we need to add the filter condition
Filter Transformation
Note : Please note that for one filter we can generate only one type of target . To connect to multiple target with multiple condition we need to use Router Transformation
etlcornerblog 