Mapping in Informatica
Mapping is a collection of source and target objects which is tied up together through a set of transformations. These transformations are formed with a set of rules that define how the data is loaded into the targets and flow of the data.
Mapping in Informatica includes the following set of objects, such as:
- Source definition: The source definition defines the structure and characteristics of the source, such as basic data types, type of the data source, and more.
- Transformation: It defines how the source data is changed, and various functions can be applied during this process.
- Target Definition: The target definition defines where the data will be loaded finally.
- Links: Link is used to connecting the source definition with target tables and different transformations. And it shows the flow of data between the source and target.
Why do we need Mapping?
In Informatica, Mapping is an object which can define the process of modification of the source data before it reaches the target object.
For example: Suppose an employee name as “Edward Cullen” in the source system and the target system. Now we need to have an employee name in the “Edward Cullen” format. At the mapping level, these kinds of operations are designed.
Mapping can define the data transformation details and source or target object characteristics because it is a primary object in the Informatica.
Mappings define the data transformation for each row at the individual column levels. And we can hold multiple sources and targets in a single mapping.
Components of Mapping
Here are some essential elements used in mapping, such as:
- Source tables
- Mapping parameters and variables
- Target objects
- Mapping transformations
A mapping contains sources, targets, mapping parameters, variables, multiple changes, mapplets, and user-defined functions.
- Mapping Source: Mapping sources are those objects whose allow fetching the source data. It can be a flat file, database table, COBOL file source, or XML source.
- Mapping target: The mapping target is the destination objects where the final data is loaded. A mapping target can be a relational table of a database, XML file, or a flat-file. Sources and targets must be present in any mapping with their different data types.
- Mapping Parameters and Variables: It is an optional user-defined data types. The Mapping parameters and variables are used to create temporary variable objects. It helps us to define and store temporary values during the processing of mapping data. This user-defined data type is designed for mapping.
- Mapplets: The mapplets are objects which consist of a set of source, transformation, or targets. With the help of Mapplets, we can reuse the existing functionality of a set of changes.
What is Stage Mapping?
In a stage mapping, we create the replica of the source table.
For Example, if we have a “Student” table, and we want to create an identical table, “Student_stage” in ETL schema.
A local stage table provides some advantages, such as production downtime, so it will not affect the ETL system because we have our own “Student_stage” table instead of referring to the “Student” table. There can be some other operations and processes which can affect the performance. The ETL processes will only access it when we have a replica staging table. Then it provides advantages with better performance.
In Stage Mappings,
- Source and Target tables are having identical structures.
- In the staging table, data is a replica of source table data.
- In the source table, data is a subset of source data.
For example, if the source table contains student details of rollno 1, 2, 3, and 10. The stage table is a kind of table which has student records of rollno 1 & 3 only.
In the data warehouse, we need to create stage tables to make the process of data transformation efficiently, to minimize the dependency of ETL or Data Warehouse from the real-time operating system. It could happen when we are fetching the relevant data only.
Mapping Parameters and Variables
Informatica has a way of defining parameters and variables as other programming languages. But Informatica is not a code-based language just like other programming languages.
In Informatica, we need to follow the predefined syntax and navigation to create parameters and variables.
Here are some fundamental differences between the mapping parameters and mapping variables, such as:
| Mapping Parameters | Mapping Variables |
|---|---|
| Mapping parameters are those data types whose value assigned at once and remains constant throughout the execution of the mapping. For example: If we have created a mapping parameter rollno=2, then the value 2 will be constant throughout the whole execution of mapping. The referenced parameter will always return value 2 for that instance of mapping run. And the value of a parameter can be redefined for a new mapping instance. | Mapping variables are the objects referenced throughout the execution of the mapping, and their values can be reassigned. For example, A mapping variable of total_marks is used in mapping, and its value will be updated on the basis of marks. |