Joiner transformation in Informatica

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.

Joiner Transformation in Informatica

Informatica Scenarios of Joiner Transformation

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.

Is Joiner Transformation Active or Passive ?

Joiner Transformation is an active transformation as no of output rows changes  based on join condition and join type

Is Joiner Transformation Connected or Unconnected

Joiner Transformation is a Connected Transformation.

Properties of Joiner Transformation in Informatica

Must read : Joiner vs union transformation in informatica

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.

Must read : Joiner vs lookup transformation in informatica

Join Type of Joiner Transformation

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.

Full Outer Join  in joiner transformation
A full outer join keeps all rows of data from both the master and detail sources.
Must Read : Learn more about Expression Transformation

How to use Joiner transformation in Informatica ?

Use the following procedure to create an Joiner transformation in Informatica mapping.

Steps to create an Joiner transformation:

1. In the Mapping Designer, open a Informatica Mapping.
2. Click Transformation > Create. Select Joiner transformation.
    
   You can also select Transformation by clicking  function  button on Informatica Designer
    
3. Enter a name and click Done.
    
4. 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 .
5. 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.
6. Double-click the title bar of the Joiner transformation to open the transformation
7. Click any box in the M column to switch the master/detail relationship for the sources.
8. 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.
    
9. 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.

10. Click the Properties tab and configure properties for the transformation (as Mentioned above)
     
11. Connect the output ports to a downstream transformation or target.

Example  of Joiner Transformation:

Check  example of Joiner Transformation in informatica

Incoming Search Terms :-
joiner transformation in informatica interview questions
joiner transformation informatica
joiner transformation performance tuning
joiner transformation example
joiner transformation in informatica example
joiner transformation scenarios in informatica
joiner transformation input is not sorted
joiner transformation in informatica master and detail

Aggregator Transformation in Informatica with example

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 in informatica

Aggregator transformation in informatica scenarios
The Aggregator transformation is use to perform aggregate calculations for each data. Data can be modified using built-in functions . Sample calculation peformed by the Aggregate transformer are :

• AVG, COUNT, MAX, MIN, SUM
• FIRST, LAST
• MEDIAN, PERCENTILE, STDDEV, VARIANCE

Is Aggregator Transformation Active or Passive ?

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

Check here for expression transformation in informatica

Steps to create an Aggregator transformation

• In the Mapping Designer, open a Mapping.
• Click Transformation > Create. Select Aggregator transformation.

• 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[/caption] 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. [caption id="attachment_158" align="alignnone" width="300"]
• 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.

Example of Aggregator Transformation:

Check here for Example of Aggregator transformation in informatica

Incoming Search terms :

aggregator transformation in informatica scenarios

aggregator transformation in informatica pdf

aggregator transformation in informatica interview questions

aggregator transformation informatica

aggregator transformation in informatica without group by

aggregator transformation without group by

aggregator transformation in informatica example

aggregator transformation properties

Expression Transformation in informatica

Expression Transformation , 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.

Check here for filter transformation example in informatica

Scenarios of Expression Transformation in informatica

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)
• Datatype conversion :  (TO_DECIMAL, TO_CHAR, TO_DATE)
• Data cleansing - check nulls (ISNULL) , replace chars, test for spaces (REPLACESTR) , test for number

Why expression transformation is passive transformation ?

Expression Transformation is a Passive transformation as it only modifies the incoming port data , but it does'n effect the number of rows processed.

Expression Transformation  is Connected or Unconnected ?

Expression Transformation is a Connected Transformation.

steps to add Expression transformation in Informatica

Use the following procedure to create an Expression transformation.

Steps to create an Expression transformation:

1. In the Mapping Designer, open a Mapping.
2. Click Transformation > Create. Select Expression transformation.
3. You can also select Transformation by clicking  function  button on Informatica Designer
4. Enter a name and click Done.
5. 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.
6. Double-click on the title bar and click on Ports tab. You can create output and variable ports within the transformation.
7. In the Expression section of an output or variable port, open the Expression Editor.
8. Enter an expression. Click Validate to verify the expression syntax.
9. Click OK.
10. Assign the port datatype, precision, and scale to match the expression return value.
11. To make it reusable ,check the reusable  option in the edit properties.
12. Configure the tracing level on the Properties tab.
13. Click OK.
14. 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

Check here example of Expression Transformation

For more details check below
  Informatica expression transformation
  Example of expression transformation in informatica
  Informatica performance tuning for expression transformation

Incoming search terms :

expression transformation
Informatica expression transformation
Expression transformation in informatica
Expression transformation in informatica with example
example of expression transformation
How to add expression transformation in informatica mapping
How to use expression transformation in informatica mapping