etlcorner: January 2016

Sunday, January 10, 2016

Lookup Transformation in Informatica with Example

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.

Informatica scenarios of Lookup 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.

Types of Lookup Transformation in Informatica:

A. Based on the Source Type:

Flat File Lookup Transformation
Relational Lookup Transformation

Check here for Relational vs flat file lookup transformation in informatica

B. Based on Connection Type:

connected Lookup transformation

Unconnected Lookup Transformation

unconnected lookup transformation part 2

: unconnected lookup transformation part 1

Check here for Difference between connected and unconnected lookup transformation

3. Based on Cached Type

Cached Lookup Transformation
UnCached Lookup Transformation

You can keep Lookup Transformation as uncached by unchecking the Cache option in properties tab.

Check here for Properties of lookup transformation in informatica

Example of Lookup Transformation :

Check here for Connected lookup transformation in informatica

Check here for Unconnected lookup transformation in informatica

Incoming Search Terms :-

lookup transformation properties

lookup transformation in informatica with example
lookup transformation in informatica interview questions
how to implement lookup transformation in informatica
lookup transformation in informatica tutorial
lookup transformation in informatica pdf
dynamic lookup transformation in informatica
lookup transformation in informatica ppt

Normalizer Transformation in Informatica with Example

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) .

: Informatica Scenarios of Normalizer Transformation

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.

Is Normalizer Transformation Active or Passive ?

Normalizer Transformation is a Active transformation as it create multiple row for each input row.

Is Normalizer Transformation Connected or Unconnected ?

Normalizer Transformation is a Connected Transformation.
Check here for filter transformation example in informatica

How to use Normalizer transformation in Informatica:

Use the following procedure to create a Normalizer transformation in Informatica mapping.

Steps to create an Normalizer transformation

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

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

Example of Normalizer Transformation:

Check here for Normalizer transformation example in informatica

Incoming Search Terms

Union Transformation in Informatica with Example

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.

Business scenarios of Union Transformation in Informatica

As name predict , Union transformation is used to merge data from multiple pipelines into single one.

Is Union Transformation Active or Passive ?

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.

Is Union Transformation Connected or Unconnected ?

Union Transformation is a Connected Transformation.

How Union Transformation work ?

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.

Configuring Union Transformation Properties:

Modify the Union Transformation properties by clicking on the Properties tab.

Check here for filter transformation example in informatica

How to use Union transformation in Informatica:

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

Example of Union Transformation:

Check here for Union transformation example in informatica

Incoming search Terms :

example of union transformation in informatica
union transformation properties in informatica
union transformation in informatica is union all

Router Transformation in Informatica with Example

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.

: Router Transformation informatica

Business scenarios of Router Transformation

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.

Is Router Transformation Active or Passive ?

Router Transformation in Informatica is an active transformation a its Routes data into multiple groups based on group conditions

Is Router Transformation Connected or Unconnected ?

Router Transformation is a Connected Transformation.

How to use Router transformation in Informatica

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

Steps to create an Router transformation:

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

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.

Example of Router Transformation

Check example of Router Transformation in informatica

Incoming Search Terms :

rank transformation in informatica
rank transformation in informatica with example
rank transformation example in informatica
how to add rank transformation in informatica mapping
rank transformation groups
example of rank transformation in informatica
router transformation in informatica interview questions
default group in router transformation in informatica
router transformation in informatica is active or passive

Saturday, January 9, 2016

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:

In the Mapping Designer, open a Informatica Mapping.
Click Transformation > Create. Select Joiner transformation.

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.

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

Monday, January 4, 2016

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

Sunday, January 3, 2016

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.