etlcorner: Informatica transformation component

Showing posts with label Informatica transformation component. Show all posts

Sunday, January 10, 2016

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

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.

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

Steps to create an Expression transformation:

In the Mapping Designer, open a Mapping.
Click Transformation > Create. Select Expression 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 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

Check here example of Expression Transformation

etlcorner