Informatica – How to create a Mapping.

Hi Guys, Ankit is back with a new article. In this article we will discuss about Informatica basics and steps of creating mappings in Informatica.

Before we plunge into practical let’s get around some theory. I know it is very boring but we need to as we believe in having strong foundations and theory will let us achieve that.

Informatica:
Informatica is a powerful ETL tool from Informatica Corporation, a leading provider of enterprise data integration software and ETL softwares. Some of the other popular ETL tools available in market are

  • Data Stage IBM
  • Oracle Warehouse Builder Oracle Corporation
  • Microsoft SQL Server Integration Microsoft
  • DT/Studio Embarcadero Technologies
  • Transformation Manager ETL Solutions

Why Informatica?

Because it provides many Siebel vanilla tables within the product

What is a “Mapping”?
Mappings represent the data flow between sources and targets. When the Informatica Server executes a session, it uses the instructions configured in the mapping to read, transform, and write data.

Every mapping must contain the following components:

Source definition: It describes the characteristics of a source table or file.
Transformation: How to modify data before writing it to targets. Use different transformation objects to on the source data and obtain different results.
Target definition: It defines the target table or flat file.
Connectors: Connect sources, targets, and transformations so the Informatica Server can move the data as it transforms it.

What is Transformation?
Transformation is the core or the most important function of ETL process it determines what to do with the source data before sending to destination.

Now, we are done with the theory hence lets get our hands on practical. Step by step procedure given below has been tested on Informatica version 6.2.2

How to Create a Mapping?

  • Open the Mapping Designer.
  • Choose Mappings-Create, or drag a repository object into the workspace.
  • Enter a name for the new mapping and click OK
  • Select source tables from the Source Folder (We just need one Source Qualifier, delete other if taking multiple source tables)
  • Map column from source tables   with Source Qualifier and put the SQL Query under Properties tab SQL Query Attribute. SQL query should contain all the fields, joins and table name required for getting the required data.
  • Under Ports tab the Port (fields) should be in the same order as in the SQL, and data type should be same as in the source table

Now create a transformation Expression

  • Go to Transformation (Under Tool Bar) Select –> Create Select  –> Expression from Dropdown.
  • Map the ports from Source Qualifier to Expression Transformation.
  • Map the Ports with the Target Table

In next post we will discuss about different type of Transformation with an example.

Don't forget to put your valuable comments and let us know

Did you like this post or not?
What more would you like to see?

Looking forward to your input :)

zp8497586rq

12 Responses to Informatica – How to create a Mapping.

  1. A. Kootstra says:

    Although Informatica is an excellent choice when it comes loading, transforming and extracting data from one system into another – think Siebel, it is also an expensive tool which may not be readily available to everyone. If you are in a small engagement or a financially restricted one, then have a look in the open source world: Talend (http://www.talend.com) and Kettle (http://kettle.pentaho.org) are two applications that have proven themselves for those incidental data loads, transformations and extractions. Each with it’s own pro’s and con’s but both with a low barrier to entry and excellent support.

    Although no specific adapters or support for the Siebel interface schema exist, it may prove to be a useful tool for those who move between several projects and want to be able to take their favourite tools with them.

    Regards,

    Anne

  2. A. Kootstra says:

    Although Informatica is an excellent choice when it comes loading, transforming and extracting data from one system into another – think Siebel, it is also an expensive tool which may not be readily available to everyone. If you are in a small engagement or a financially restricted one, then have a look in the open source world: Talend (http://www.talend.com) and Kettle (http://kettle.pentaho.org) are two applications that have proven themselves for those incidental data loads, transformations and extractions. Each with it’s own pro’s and con’s but both with a low barrier to entry and excellent support.

    Although no specific adapters or support for the Siebel interface schema exist, it may prove to be a useful tool for those who move between several projects and want to be able to take their favourite tools with them.

    Regards,

    Anne

  3. Another powerful ETL tool is Jitterbit (www.jitterbit.com). It is robust and opensource and provides mappings between such sources and targets as webservices, ldap, csv’s and databases that have odbc drivers. I’ve used it in different integration and ETL scenario’s including a Siebel EIM position and must say it’s a free and powerful alternative to some of the more popular choices.

  4. Another powerful ETL tool is Jitterbit (www.jitterbit.com). It is robust and opensource and provides mappings between such sources and targets as webservices, ldap, csv’s and databases that have odbc drivers. I’ve used it in different integration and ETL scenario’s including a Siebel EIM position and must say it’s a free and powerful alternative to some of the more popular choices.

  5. srikanth n v v says:

    The information is good and clear.
    I request you to explain every concept in Informatica how small it may be in the same way you explained this and source qualifier common mistakes, patiently and clearly.

    I have not seen such a clear cut explaination as these. If you do that for all the possible things on Informatica, it would be highly useful for many users who are awaiting this kind of help.
    I will communicate this to all of my friends.

    Thank you.

  6. srikanth n v v says:

    The information is good and clear.
    I request you to explain every concept in Informatica how small it may be in the same way you explained this and source qualifier common mistakes, patiently and clearly.

    I have not seen such a clear cut explaination as these. If you do that for all the possible things on Informatica, it would be highly useful for many users who are awaiting this kind of help.
    I will communicate this to all of my friends.

    Thank you.

  7. Sivareddyn says:

    easy to understand

  8. Afsdfsggs says:

    very ncie explanation..
    did like the way explained..

  9. Hiddu says:

    The above is very Valuable post Thanks to Mr.Ankit to guide and please kindly posts on transformations with examples and mapping figures…thanks a lot 2u and http://www.siebelunleashed.com...

  10. nazeer says:

    yes, indeed was very helpful for the beginners. If you have time, please let us know more, simple way of understanding the informatica as developer

  11. Ravig Patti says:

    what is an dwh?with an examples

  12. dew says:

    Hi,

    is there any book available on Informatica DT mapper?

Contribute