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?
Components of Mapping
What is Stage Mapping?
How to Create a Mapping
Mapping Parameters and Variables
How to Create Mapping Parameter
How to Create Mapping Variable
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”.
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
Open Informatica Designer Tool Click on Mapping Designer Icon to launch Mapping Designer
Step 2 – In Mapping Designer
Click on Mappings Menu Select Create Option
Step 3 – Enter Mapping name as ‘m_emp_emp_target’ and select OK Button.
Mapping will be created and listed under mappings folder.
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,
Select “emp” source table under sources folder. Drag and drop “emp” table into mapping designer.
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.
Step 5 – In this step we will,
Select “emp_target” source table under Targets folder. Drag and drop “emp_target” table into mapping designer
In mapping designer, “target table” will be imported and shown.
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.
After selecting the option “Arrange all Iconic”, the workspace will look like this.
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.
After this rearrangement, the workspace will look like this.
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.
Step 9 – Right Click on mapping designer workspace and select “Autolink” by name option.
Step 10 – To link source with target table
Select Source table columns. Drag and drop columns to the target table.
The Source and the Target tables will be linked, and connecting arrows will appear from source to the target table.
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.
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 – 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,
Select mappings menu Select parameters and variables menu
Step 2– In next screen,
Click on add new variable menu From drop down, select type as parameter Enter parameter name as $$Deptno Enter an initial value of 10 Select OK button
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
Select mappings menu Select parameters and variables menu
Step 2– On the next screen
Click on add new variable menu From drop down, select type as variable Enter variable name as $$TotalSalary Select DataType as decimal Enter an initial value of 0 Select OK button
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.
