Mappings in Informatica

A mapping is a collection of source and target objects linked together by a set of transformations. These transformations consist of a set of rules, which define the data flow and how the data is loaded into the targets.

A mapping consists of following set of objects

  • Source Definition - Source definition defines the structure and characteristic of the source, its underlying data types, type of the data source, etc.
  • Transformation - Transformation objects define how the source data is transformed, and various functions can be applied during the process
  • Target Definition - Target definition defines the final target where the data will be loaded.
  • Links - Links connect the source definition to different transformations and target tables. It defines how the data flows from source to target and the transformations.

In this Tutorial- you will learn

Why do you need Mapping?

Mapping is an object in Informatica with the help of which you can define how the source data is modified before it reaches the destination or target object. Like if you have employee name as "Bill Clinton" in your source system and in the target system the requirement is to have employee name in the format as "Clinton Bill", such operations can be designed at the mapping level. In basic terms, what you do with the source data is defined at the mapping level.

Mapping is the basic Informatica object with the help of which we can define the data transformation details and source/target object characteristics. Mappings help us to define the data transformation at the individual column levels for each row. Even in a single mapping you can handle multiple sources and targets.

Components of Mapping

Basic components of a mapping are

  • Source tables
  • Mapping parameters and variables
  • Target objects
  • Mapping transformations

There are various objects that constitute a mapping. A mapping can consist of sources, targets, mapping parameter and variables, mapplets, various transformations, and user-defined functions.

  • Mapping Source: Mapping sources are the objects from where you fetch the source data. It can be a database table, flat file, XML source or COBOL file source
  • Mapping target: Mapping target is our destination objects where final processed data gets loaded. Mapping target can be a relational table of a database, a flat file or XML file. Sources and targets are mandatory in any mapping, their type can differ
  • Mapping Parameters and Variables: Mapping parameters and variables helps you to create temporary variable objects which will help you to define and store temporary values while mapping data processing. Mapping parameters and variables are optional users defined data types, which can be created for a mapping and can be referenced and updated for a specific requirement. We will learn more about mapping parameters and variables in this section
  • Mapplets: They are objects which consist of a set of transformation, source or targets. Mapplets are generally created to reuse the existing functionality of a set of transformations. It can be used in any no of mappings.

What is Stage Mapping?

A stage mapping is a mapping in where we create the replica of the source table. For Example, in a production system if you have an "employee" table then you can create an identical table "employee_stage" in ETL schema.

Having a local stage table offers various advantages, like production downtime, won't affect your ETL system because you have your own "employee_stage" table, instead of referring to production "employee" table. In a Production system, there can be other operations and processes which affect the performance. However, when you have replica staging table, only ETL processes will access it. This offers performance benefits.

In Stage Mappings,

  • Source and Target tables have identical structures
  • The data in the target table is a replica of source table data or
  • Data in stage (target) table is a subset of source data.

For example, if your source table contains employee details of deptno 10, 20, 30, and 40. The staging table can be a table having employee records of deptno 10 & 30 only.

The purpose of creating stage tables in Data warehouse is to make the process of data transformation efficient by fetching only those data which is relevant to us and also to minimize the dependency of ETL/Data Warehouse from the real-time operational system.

How to Create a Mapping

In this exercise, we will create a stage mapping, in which source will be "emp table" and the target will be "emptgt".

Mappings in Informatica

Naming Convention – mapping names are prefixed with 'm_' followed by source and target table names separated by underscore sign.

Example – if you are loading emp_target table from the emp table, then mapping name can be 'm_emp_emp_target'.

Step 1 – Launching Mapping Designer

  1. Open Informatica Designer Tool
  2. Click on Mapping Designer Icon to launch Mapping Designer

Mappings in Informatica

Step 2 – In Mapping Designer

  1. Click on Mappings Menu
  2. Select Create Option

Mappings in Informatica

Step 3 – Enter Mapping name as 'm_emp_emp_target' and select OK Button.

Mappings in Informatica

Mapping will be created and listed under mappings folder.

Mappings in Informatica

A Mapping must have at least a source and a target, you will add sources and targets to the mapping.

Step 4 – In this step we will,

  1. Select "emp" source table under sources folder.
  2. Drag and drop "emp" table into mapping designer.

Mappings in Informatica

In mapping designer, imported source table will be shown.

Note – When you import any relational (database) table in a mapping, an additional object of source qualifier type will also be created. This source qualifier transformation is necessary and helps Informatica integration service to identify the source database table and its properties. Whenever you import a source table, source qualifier transformation will also be created. You should never delete a source qualifier object in a mapping.

Mappings in Informatica

Step 5 – In this step we will,

  1. Select "emp_target" source table under Targets folder.
  2. Drag and drop "emp_target" table into mapping designer

Mappings in Informatica

In mapping designer, "target table" will be imported and shown.

Mappings in Informatica

To manage the view space, you can iconize these objects in the mapping.

Step 6 – Right click anywhere in the mapping designer empty workspace and select option – Arrange all iconic.

Mappings in Informatica

After selecting the option "Arrange all Iconic", the workspace will look like this.

Mappings in Informatica

Step 7 - In Informatica, We design with the flow from left to right. So, source tables should be at the left side, and target tables should be at right. To arrange tables in our workspace, Select the "emp_target" table then drag and drop it to the right side of the emp table.

Mappings in Informatica

After this rearrangement, the workspace will look like this.

Mappings in Informatica

Note – Periodically use "ctrl+s" shortcut to save changes to the repository.

Step 8 - Now you have source and target tables in your mapping, but the mapping is not yet complete. The source and target tables should be linked to complete a mapping.

To Link source and targets

Double click on SQ_EMP table and EMP_TARGET table to change the view of them from iconic to the graphic.

Mappings in Informatica

Step 9 – Right Click on mapping designer workspace and select "Autolink" by name option.

Mappings in Informatica

Step 10 – To link source with target table

  1. Select Source table columns.
  2. Drag and drop columns to the target table.

Mappings in Informatica

The Source and the Target tables will be linked, and connecting arrows will appear from source to the target table.

Mappings in Informatica

Note – Here you have linked all source columns to the respective target table column. It means, for every source record fetched, all the columns of the target will get loaded. If you want to exclude any specific column from getting loaded, click on that column link and press delete key from the keyboard. The link will get removed, and the target column will not get loaded.

Step 11 - Use shortcut "ctrl+s" to save changes to your mapping. On the output window, you can see the message of mapping validation/parsing. It confirms that your mapping is valid. Also, there will be a temporary green tick mark next to the mapping name in mapping folder tree indicating mapping is done successfully.

Mappings in Informatica

In mappings there can be a requirement, where we need to pass variable to the mapping or there can be a scenario where we need to calculate temporary variables and further required to store them for the next session run of the mapping. For these purposes, we create mapping parameters and variables.

Mapping Parameters and Variables

Like every programming language, Informatica has its own way of defining parameters and variables. But unlike other programming languages, Informatica isn't a code based language. To create parameters and variables in Informatica, you have to follow the predefined syntax and navigation.

Difference between parameters and variables –

Mapping Parameters

Mapping Variables

Mapping parameters are those data types whose value once assigned remains constant throughout the mapping run. Like if you have created a mapping parameter deptno=20, then the value 20 will be constant for the whole mapping run. The parameter wherever it will be referenced will always return value 20 for that instance of mapping run. For a new mapping instance, the parameter value can be redefined.

Mapping variables are objects which can be referenced throughout the mapping run (to access their values) and their values can be reassigned. For example, a mapping variable of total_salary can be used in a mapping, and its value can be updated based on salaries.

The mapping parameters and variables are specific to a mapping only. They cannot be referenced in another mapping.

How to Create Mapping Parameter

When you create a mapping parameter, during execution of mapping Integration service looks for its assigned value. This values can be assigned to following places.

  • Inside parameter file
  • In pre-session variable assignment
  • Initial value in repository
  • Default value assigned during variable creation

Step 1 – To Create mapping parameter - In mapping designer,

  1. Select mappings menu
  2. Select parameters and variables menu

Mappings in Informatica

Step 2- In next screen,

  1. Click on add new variable menu
  2. From drop down, select type as parameter
  3. Enter parameter name as $$Deptno
  4. Enter an initial value of 10
  5. Select OK button

Mappings in Informatica

Now, you have created a mapping parameter deptno, with initially assigned value of 10, and this parameter can be referenced inside the mapping.

How to Create Mapping Variable

Step 1 – In mapping designer

  1. Select mappings menu
  2. Select parameters and variables menu

Mappings in Informatica

Step 2- On the next screen

  1. Click on add new variable menu
  2. From drop down, select type as variable
  3. Enter variable name as $$TotalSalary
  4. Select DataType as decimal
  5. Enter an initial value of 0
  6. Select OK button

Mappings in Informatica

This will create a mapping variable.

Note – mapping parameter and variable names always begin with $$.

Summary

Mappings are important in Informatica to match source data with target as per project requirements. We have discussed stage mappings and concept of Mapping Variables and Parameters.