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.

Read More...

Transformation In Informatica and What it Does ?

What Is Transformation in Informatica and What it Does ?

If you just started learning informatica for ETL work then first thing you should know is Transformation.

In Informatica a transformation can be repository object that will generate, modify, or forward data to other sources.

In simple words you can say that it will transform the source data into target data according to the requirements of target system by applying some rules.

 

In Picture Default SQL Join Using Source Qualifier Transformation

Consider the above example , in this screenshot a basic transformation is shown. Here records from two different oracle tables are retrived and then using their department id both tables are joined in source qualifier transformation by applying simple expression. As a result in the target table we will get record whose department is same.

Types Of Transformation In Informatica

• Transformations can be active or passive according to no of records they produce in target table.
• Transformations can be connected to the data flow, or they can be unconnected.

What Is Active Transformation In Informatica ?

Basically An active transformation can Change the number of rows that pass through the transformation.It also Change the transaction boundary.

 For example, if we are applying filter transformation on source then it will pass only those records which follows filter pattern, it simply reject other records. so no of records in target table will be less than source table. Filter transformation is Active transformation .

What Is Passive Transformations In Informatica ?

 Comparing to active transformation, a passive transformation does not change the number of rows that pass through the transformation, maintains the transaction boundary, and maintains the row type.

 For example, if you are applying expression transformation with expression +10, then this transformation will add numeric value 10 to all the record who passes through this port. so no of records in target table will be same as source table . Expression transformation is Passive Transformations.

What is Connected  Transformations In Informatica ?

Connected transformation are those transformation which take part in data flow. Connected transformation has some input ports from which data will input from previous data flow and has some output port, from which data will be passed to next connected data flow. In the Default SQL join example all three objects are connected to each other.

What is Unonnected  Transformations In Informatica ?

An unconnected transformation is not connected to other transformations in the mapping or data flow. An unconnected transformation is called within another transformation as a function call, and returns a value to that transformation.
for example Unconnected lookup is a type on Unonnected Transformation

in the next post i will explain how to create Transformation in Informatica.

Read More...