How To Create a Transformation in Informatica ?

In previous post i already explain what is transformation in Informatica and what it does. Now let us learn how to create them.

We can create a transformation in informatica using several designer tools and they are -

1. Mapping Designer- By using Mapping Designer we can Create transformations that connect  sources to targets. During creation of transformation if it is not configured as reusuable transformation then it can not be used in other mappings. Such tranformation which can be used in other mappings are called Reusuable transformation.


2.  Transformation Developer- By using transformation developer we can create individual transformations and configure them, they are called reusable transformations and can be used in multiple mappings.


3.  Mapplet Designer - By using mapplet designer we can create and configure a set of transformations simultaneously, and these are called Mapplets. Transformation developed using mapplet designer can be used in multiple mappings.

Step to create a transformation in the Mapping Designer, Transformation Developer, and Mapplet Designer

To create a transformation:

1. Open the appropriate Designer tool from three.
2. In the Mapping Designer click on open or create a Mapping. In the Mapplet Designer click on open or create a Mapplet.
3. Click Transformation then Create and select the type of transformation you want to create.
4. Drag across the portion of the mapping where you want to place the transformation.

After following above steps the new transformation will appear in the workspace. Next, you need to configure the transformation by adding any new ports to it and setting other properties.

In Picture Edit Transformation Window

How to Configure Transformations in Informatica

After you create a transformation the next step is to configure it. Every transformation contains the following common tabs:

•  Transformation - Enter desired name for the transformation or add a description.
•  Port - Add and configure input, output ports.
• Properties - Configure properties that are unique to the transformation.

Some transformations might include other tabs, such as the Condition tab, where you enter conditions in a Joiner or Normalizer transformation. When you configure transformations, you might complete the following tasks:

• Add ports - Define the columns of data that move into and out of the transformation that is input or output port
• Add groups - In some transformations, define input or output groups that define a row of data entering or leaving the transformation.
• Enter expressions -  Enter SQL-like expressions in some transformations that transform the data as in expression transformation
• Define local variables -  Define local variables in some transformations that temporarily store data and can be used in evaluating expression.
• Override default values - Configure default values for ports to handle input nulls and output transformation errors.
• Enter tracing levels - Choose the amount of detail the Integration Service writes in the session log about a transformation.

 How to configure Ports in Informatica

After you create a transformation the next step is configure ports on the Ports tab.

Steps to Create a port:

• Drag a port from another transformation. When you drag a port from another transformation the Designer creates a port with the same properties, and it links the two ports.
• Click Layout then Copy Columns to enable copying ports.
• Click the Add button on the Ports tab. The Designer creates an empty port you can configure.

Steps to Configure the properties on the Ports tab:

• Port name. The name of the port. Use the following conventions while naming ports
• Begin with a single- or double-byte letter or single- or double-byte underscore (_).
• Port names can contain any of the following single- or double-byte characters: a letter, number, underscore (_), $, #, or @.
• Datatype, precision, and scale. If you plan to enter an expression or condition, make sure the datatype matches the return value of the expression.
• Port type. Transformations may contain a combination of input, output, input/output, and variable port types.
• Default value. The Designer assigns default values to handle null values and output transformation errors. You can override the default value in some ports.
• Description. A description of the port.
• Other properties. Some transformations have properties specific to that transformation, such as expressions or group by properties.

Important Note: The Designer creates some transformations with configured ports. For example, the Designer creates a Lookup transformation with an output port for each column in the table or view used for the lookup. You need to create a port representing a value used to perform a lookup.

Linking Ports 

After you add and configure a transformation in a mapping, you link it to targets and other transformations. You link mapping objects through the ports. Data passes into and out of a mapping through the following ports:

• Input ports - Receive data.
• Output ports - Pass data.
• Input/output ports - Receive data and pass it unchanged.

To link ports, drag between ports in different mapping objects. The Designer validates the link and creates the link only when the link meets validation requirements.