Mappings in Informatica: Create, Component, Parameter, Variable (2023)

What is a Mapping?

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

A mapping consists of the following set of objects

• font definition– The source definition defines the structure and characteristic of the source, its underlying data types, the data source type, etc.
• Transformation– Transformation objects define how the source data is transformed, and various functions can be applied during the process.
• destination definition– The destination definition defines the final destination where the data will be loaded.
• links– Links connect the source definition to different transformations and target tables. Defines how data flows from source to destination and transformations.

In this tutorial, you will learn

• Why do you need Mapping?
• Mapping Components
• What is scenario mapping?
• How to create a task
• Assignment of parameters and variables
• How to create a mapping parameter
• How to create a 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 reaching the target or target object. For example, if you have an employee name like "Bill Clinton" in your source system and in the target system, the requirement is to have an employee name in the form "Clinton Bill", such operations can be designed at the assignment level . In basic terms, what you do with the source data is defined at the mapping level.

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

Mapping Components

The basic components of a mapping are

• source tables
• Assignment of parameters and variables
• target objects
• Transformation Mapping

There are several objects that make up a mapping. A mapping can consist ofsources, targets, assignment parameters and variables, mapplets, various transformations and user-defined functions.

• mapping source: Mapping sources are the objects from which you get the source data. It can be a database table, flat file, XML source, or COBOL file source.
• Target Assignment:The target of the mapping is our target objects where the final processed data is loaded. The mapping target can be a relational table in a database, a flat file, or an XML file. Sources and destinations are mandatory in any mapping, their type can be different
• Assignment of parameters and variables: Map parameters and variables help to create temporary variable objects that will help you to set and store temporary values ​​while processing mapping data. Mapping parameters and variables are user-defined optional data types that can be created for a mapping and can be referenced and updated for a specific requirement. We will learn more about parameter and variable mapping in this section.
• Mapplets: These are objects consisting of a transform, source, or target set. Mapplets are often created to reuse existing functionality from a transform suite. Can be used in any number of assignments.

What is scenario mapping?

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

Having a local stage table gives you several advantages, such as production downtime, which will not affect your ETL system because you have your own "employee_stage" table instead of referring to the production "employee" table. In a Production system, there may be other operations and processes that affect performance. However, when you have a replication staging table, only ETL processes will access it. This offers performance benefits.

In Scenario Mappings,

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

For example, if your source table contains details of employees from departments #10, 20, 30 and 40. The middle table could be a table that contains records for employees from departments #10 and 30 only.

The purpose of creating staging tables in the data warehouse is to make the data transformation process efficient, getting only the data that is relevant to us and also to minimize the ETL/Data Warehouse dependency on the real-time operating system.

How to create a task

In this exercise, we will create a stage mapping, where the source will be "emp table" and the destination will be "emptgt".

naming convention– mapping names are prefixed with 'm_' followed by the source and target table names separated by an underscore.

Example: If you are loading the emp_target table from the emp table, the mapping name could be 'm_emp_emp_target'.

Step 1– Launch of Mapping Designer

1. Open the Informatica Designer tool
2. Click the Map Designer icon to launch the Map Designer

step 2– No Map Designer

1. Click on the Assignments menu
2. Select the Create option

Step 3– Enter the name of the mapping as 'm_emp_emp_target' and select the OK button.

The assignment will be created and appear in the assignments folder.

A mapping must have at least oneOrigin and destiny, you will add sources and targets to the mapping.

stage 4– In this step we will,

1. Select the "emp" source table in the sources folder.
2. Drag and drop the "emp" table onto the map designer.

In the map designer, the imported source table will be displayed.

To use– When importing any relational table (database) into a mapping, an additional object of type source qualifier will also be created. This Source Qualifier transformation is required and helps the Informatica Integration Service identify the source database table and its properties. Whenever you import a source table, a Source Qualifier transformation is also created. You should never delete a source qualifier object in an assignment.

Step 5– In this step we will,

1. Select the "emp_target" source table in the Targets folder.
2. Drag and drop the "emp_target" table into the map designer

In the map designer, the "target table" will be imported and displayed.

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

Step 6 –Right-click anywhere in the empty Map Designer workspace and select the option: Arrange All Icons.

After selecting the "Arrange All Iconics" option, your desktop will look like this.

Step 7– At Informatica, we design with left-to-right flow. Therefore, the source tables must be on the left side and the target tables must be on the right side. To organize the tables in our workspace, select the "emp_target" table, drag and drop it onto the right side of the emp table.

After this rearrangement, the workspace will look like this.

To use– Periodically use the “ctrl+s” shortcut to save changes to the repository.

Step 8 –You now have source and target tables in your mapping, but the mapping is not yet complete. The source and target tables must be linked to complete a mapping.

To link origin and destination

Double-click the SQ_EMP table and the EMP_TARGET table to change your view from iconic to graphic.

Step 9 –Right-click on the map designer workspace and select the "Autolink" option by name.

Step 10 –To link the source to the target table

1. Select columns from the source table.
2. Drag and drop columns into the target table.

The source and target tables will be linked and connecting arrows will appear from source to target table.

To use– Here you have bound all the source columns to the column of the respective target table. This means that for every source record fetched, all columns from the target will be loaded. If you want to exclude a specific column from loading, click on that column's link and press the Delete key on your keyboard. The link will be removed and the target column will not load.

Step 11– Use the “ctrl+s” shortcut to save changes to your mapping. In the output window you can see the mapping validation/analysis message. Confirm that your mapping is valid. Additionally, there will be a temporary green checkmark next to the assignment name in the assignment folder tree, indicating that the assignment was successful.

In assignments there may be a requirement, where we need to pass the variable to the assignment or there may be a scenario where we need to compute temporary variables and furthermore need to store them for the next run of the assignment session. For this, we create parameters and mapping variables.

Assignment of parameters and variables

Like any programming language, Informatica has its own way of defining parameters and variables. But unlike other programming languages, Informatica is not a code-based language. To create parameters and variables in Informatica, you must follow the predefined syntax and navigation.

Difference between Parameters and Variables -

Variables and assignment parameters are specific to an assignment only. They cannot be referenced in another mapping.

How to create a mapping parameter

When you create a mapping parameter, during the mapping run, the Integration Service looks for its mapped value. These values ​​can be assigned to the following locations.

• internal parameter file
• In pre-session variable assignment
• Initial value in repository

• Default value assigned during variable creation

Step 1 -To create a mapping parameter–No map designer,

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

step 2– On the next screen,

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

You have now created a deptno mapping parameter, with an initially assigned value of 10, and this parameter can be referenced in the mapping.

How to create a mapping variable

Step 1 -No map designer

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

step 2– On the next screen

1. Click menu Add new variable
2. From the drop-down menu, select the type as a variable
3. Enter variable name like $$TotalSalary
4. Select data type as decimal
5. Enter an initial value of 0
6. Select OK button

This will create a mapping variable.

To use– Variable and mapping parameter names always start with $$.

Summary

Mappings are important in Informatica to match source data to target data based on design requirements. We discuss stage assignments and the concept of assigning variables and parameters.