Everything About Mapping of Informatica

Mappings Overview:

Mappings represent the data flow between sources and targets. When the Informatica Server runs 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. Describes the characteristics of a source table or file.
• Transformation. Modifies data before writing it to targets. Use different transformation objects to perform different functions.
• Target definition. 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. 

  

   Mapping of Informatica

A mapping can also contain one or more mapplets. A mapplet is a set of transformations that you build in the Mapplet Designer and can use in multiple mappings. When you add an object to a mapping, you configure the properties according to the way you want the Informatica Server to transform the data. You also connect the mapping objects according to the way you want the Informatica Server to move the data. You connect the objects through ports. The Mapping Designer displays objects in three different views:

• Iconized. Shows an icon of the object with the object name.
• Normal. Shows the columns in the ports tab and the input and output port indicators. You can connect objects that are in the normal view.
• Edit. Shows the object properties. You can switch between the different tabs and configure the object in this view.

Object Dependency:

Some objects in a mapping are also stored as independent objects in the repository:

• Sources
• Targets
• Reusable transformations
• Mapplets

The mapping is dependent on these objects. When this metadata changes, the Designer and other Informatica Client applications track the effects of these changes on mappings. In these cases, you may find that mappings become invalid even though you do not edit the mapping. When a mapping becomes invalid, the Informatica Server cannot execute it properly, and the Server Manager invalidates the session. The only objects in a mapping that are not stored as independent repository objects are the standard transformations that you build within the mapping. These standard transformations are stored within the mapping only.

Developing a Mapping:

When you develop a mapping, use the following procedure as a guideline:

• Verify that all source, target, and reusable objects are created. Create source and target definitions. If you want to use mapplets, you must create them also. You can create reusable transformations in the Transformation Developer, or you can create them while you develop a mapping.

• Create the mapping. You can create a mapping by dragging a source, target, mapplet, or reusable transformation into the Mapping Designer workspace, or you can choose Mapping-Create from the menu.

• Add sources and targets. Add sources and targets to the mapping.

• Add transformations and transformation logic. Add transformations to the mapping and build transformation logic into the transformation properties.

• Connect the mapping. Connect the mapping objects to create a flow of data from sources to targets, through mapplets and transformations that add, remove, or modify data along this flow.

• Validate the mapping. Validate the mapping to identify connection or transformation errors.

• Save the mapping. When you save the mapping, the Designer validates it, identifying any errors. The Designer displays validation messages in the Output window. A mapping with errors is invalid, and you cannot run a session against it until you validate it.

Working with Mappings:

You can perform the following tasks with mappings:

• Create a mapping. When you create a mapping, you save the mapping name in the repository. You can then develop and save the mapping.
• Open a mapping. You can open one mapping at a time in a folder.
• Copy a mapping. You can copy a mapping within the same folder or to another folder.
• Export a mapping. You can export a mapping to an XML file.
• Import a mapping. You can import a mapping from an XML file that you exported in the Designer.
• Edit a mapping. You can add, modify, or delete objects in a mapping.
• Save a mapping. When you save a mapping in the repository, the Designer performs mapping validation.
• Debug a mapping. Run the Debugger in the Mapping Designer to test mapping logic.
• Delete a mapping. Delete a mapping from the repository if you do not want to use it again.

Creating a Mapping:

The first step in the process of moving data between sources and targets is to create a mapping in the Mapping Designer. Use the following steps to create a mapping. 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.

Opening a Mapping:

To open a mapping, drag it from the Navigator into the Mapping Designer workspace. If you have a mapping in the same folder already open, the Designer prompts you to close it before continuing. Click OK to close the current mapping and open the other one. You can open one mapping at a time in a folder. If you open more than one folder at a time, you can open a mapping in each folder.

Copying a Mapping:

The Designer allows you to copy mappings:

• Within a folder
• To another folder in the same repository or in another repository

When you copy a mapping, the Designer creates a copy of each component in the mapping, if the component does not already exist. If any of the mapping components already exist, the Designer prompts you to rename, replace, or reuse those components before you continue.

Using the Copy Command:

When you copy a mapping, the Designer copies all objects within the mapping. If the destination folder already contains the exact same object with the same name, the Designer prompts you to rename, replace, or reuse the object. If the folder contains an object of the same name that is not exactly the same, the Designer prompts you to rename or replace the object. If the mapping contains sources with primary-foreign key relationships to sources not used in the mapping, the Designer prompts you to copy the related source. To use the Copy command to copy a mapping within a folder:

• In the Designer, select the mapping you want to copy.
• Choose Edit-Copy. The Designer saves the mapping on the clipboard.
• If the folder has more than one version under the Mapping node, select the version where you wish to paste. If the folder has only one version under the Mapping node, select the Mapping node.
• Choose Edit-Paste. The Designer prompts you to rename the mapping.
• Enter the new mapping name.
• Click OK. The new mapping appears under the mapping node.

You can copy a mapping to a different folder. After you copy the mapping, it appears in the Mappings node of the new folder. To use the Copy command to copy a mapping across folders:

• In the Designer Navigator, select the mapping.
• Choose Edit-Copy. The Designer saves the mapping on the clipboard.
• Select the folder where you want to create a mapping.
• Choose Edit-Paste. The Designer creates a copy of the mapping.

OR

• In the Designer, open the folder containing the mapping you want to copy.
• In the Navigator, select the mapping.
• If you are copying from a shared folder, press Ctrl and drag the mapping to the destination folder. If you do not press Ctrl, the Designer creates a shortcut to the mapping.

OR

• If you are copying a mapping from a folder that is not shared, drag the mapping to the destination folder.

Using the Copy As Command:

If you make changes to a mapping, but you do not want to overwrite the original mapping, you can make a copy of the changed mapping by choosing Mapping-Copy As. When you use Copy As, the copy of the mapping contains the changes and the original mapping does not reflect these changes. You can only use Mappings-Copy As within the same folder. When you use Copy As, the mapping must be open in the workspace.

After you copy the changed mapping using Copy As, choose Repository-Save to save the new mapping. To use the Copy As command to copy a mapping:

• In the Mapping Designer, select the changed mapping.
• Choose Mappings-Copy As.
• Enter the new mapping name.
• Click OK. You cannot use Copy As to copy shortcuts.
• Choose Repository-Save to save your new mapping.

Renaming and Adding Comments to a Mapping:

You can rename, add comments, or specify links to business documentation for a mapping at any time. Adding comments or business documentation links is an easy way to document the purpose of a mapping. The Repository Manager and MX views include these comments to help you analyze your metadata. To rename or add comments to a mapping:

• From the Mapping Designer, open a mapping.
• Choose Mappings-Edit.
• In the Edit Mapping dialog box, enter a new name for the mapping.
• Add a description of the mapping in the comments box. You can enter up to 2,000 characters.
• Click OK.

Invalidating Sessions:

When you edit and save a mapping, some changes cause the session to be invalid even though the mapping remains valid. The Informatica Server does not run invalid sessions. If you edit a mapping, the Designer invalidates sessions under the following circumstances:

• Add or remove sources or targets.
• Remove mapplets or transformations.
• Replace a source, target, mapplet, or transformation when importing or copying objects.
• Add or remove Source Qualifiers or COBOL Normalizers, or change the list of associated sources for these transformations.
• Add or remove a Joiner or Update Strategy transformation.
• Add or remove transformations from a mapplet in the mapping.
• Change the database type for a source.

Deleting a Mapping:

You may decide to delete mappings that you no longer use. When you delete a mapping, you do not delete any sources, targets, mapplets, or reusable transformations defined outside the mapping. However, you do delete all standard transformations that exist within the mapping. If you want to save any of these transformations, you can make them reusable before deleting the mapping. You can delete a mapping from the Navigator window, or you can delete the mapping currently displayed in the Mapping Designer workspace.

• To delete a mapping from the Navigator window, select the mapping and press the Delete key, or choose Edit-Delete.
• To delete a mapping currently displayed in the Mapping Designer workspace, choose Mappings-Delete.

Connecting Mapping Objects:

Once you add and configure source, target, and transformation objects in a mapping, you complete the mapping by connecting the mapping objects. You connect mapping objects through the ports. Data passes into and out of a mapping through the following ports:

• Input ports. Receive data.
• Output ports. Pass data.
• Input/output ports. Receive data and pass it unchanged.

Every source instance, target instance, mapplet, and transformation contains a collection of ports, each representing a column of data:

• Sources provide data, so they contain only output ports.
• Targets receive data, so they contain only input ports.
• Mapplets contain only input and output ports.
• Transformations contain a mix of input, output, and input/output ports, depending on the transformation and its application.

To connect ports, you drag between ports in different mapping objects. The Designer validates the connection and creates the connection only when the connection meets validation requirements.

Working with Sources in a Mapping:

When you create a mapping, you must add one or more source definitions to it. When you drag a source into the Mapping Designer workspace, you add an instance of the source definition to the mapping. The Mapping Designer displays source instances in read-only mode. Every mapping requires at least one transformation object that determines how the Informatica Server reads the source data:

• Source Qualifier transformation. Represents data read from relational and flat file sources.
• Normalizer transformation. Represents data read from COBOL sources.
• ERP Source Qualifier transformation. Represents data read from ERP sources.
• XML Source Qualifier transformation. Represents data read from XML sources.

You can let the Designer create the Source Qualifier by default, so each time you drag a source into a mapping, the Designer adds a Source Qualifier and connects it to the source. Use the automatic Source Qualifier creation when you want to create one Source Qualifier or Normalizer for each source in your mapping. You can disable the automatic creation when you want to join data from different relational sources. You can then manually create and connect it to the source. When you edit the source in the Source Analyzer, all instances of the source in mappings inherit the changes. Some changes might invalidate the mappings using the source. When you add a source definition with the slash character (/) in the table name to mapping, the Designer replaces the slash character with an underscore character in the source instance name for the mapping.

Working with Transformations in a Mapping:

A transformation is a repository object that generates, modifies, or passes data. You configure logic in a transformation that the Informatica Server uses to transform data. The Designer provides a set of transformations that perform specific functions. For example, an Aggregator transformation performs calculations on groups of data. Each transformation has rules for configuring and connecting in a mapping. For more information about working with a specific transformation, refer to the chapter in this book that discusses that particular transformation. You can create transformations to use once in a mapping, or you can create reusable transformations to use in multiple mappings. When you add a reusable transformation to a mapping, you add an instance of the transformation. When you edit the reusable transformation in the Transformation Developer, all instances of the transformation in mappings inherit the changes. Some changes might invalidate the mappings using the reusable transformation.

Working with Mapplets in a Mapping:

You can build mapplets in the Mapplet Designer when you want to use a standardized set of transformation logic in several mappings. When you use a mapplet in a mapping, the mapplet represents all the transformations in the mapplet, even though the Mapping Designer only displays transformations that provide input or output for the mapplet. These transformations display as groups. When you use a mapplet in a mapping, the Designer creates an instance of the mapplet. You can enter comments for the instance of the mapplet in the mapping, but you cannot edit other mapplet properties. When you edit the mapplet in the Mapplet Designer, all instances of the mapplet in mappings inherit the changes. Some changes might invalidate the mappings using the mapplet.

Working with Targets in a Mapping:

When you create a mapping, you must add one or more target definitions to it. When you drag a target definition into the Mapping Designer workspace, you add an instance of the target definition. You can configure different target properties for different target types. For relational targets, you can configure the following properties within a mapping:

• Reject truncated and overflow data. Select this option in the target instance Properties tab when you want the Informatica Server to write truncated data to the reject file.
• Update override. Override the default UPDATE statement using the SQL Editor in the target instance Properties tab.
• Table name prefix. Specify the owner of the target tables in the target instance Properties tab.

For XML targets, you can configure the following property within a mapping:

• Duplicate Group Row Handling. Specify how the Informatica Server handles duplicate rows in XML groups.
• DTD/Schema Reference. Specify the DTD or XML schema file for the target XML file. The Informatica Server writes the DTD or XML schema file name as part of the document type declaration in the XML file.

You can configure the following target property for a mapping containing any type of target definition:

• Set the target load order. Choose Mapping-Target Load Plan to designate the order that the Informatica Server loads multiple targets in a mapping. You do not configure this in the target instance.

Note: You cannot configure these properties in the Warehouse Designer.

When you edit the target in the Warehouse Designer, all instances of the target in mappings inherit the changes. Some changes might invalidate the mappings using the target.

When you add a target definition with slash characters (/) in the table and name to mapping, the Designer replaces the slash character with an underscore in the target instance name for the mapping.

Rejecting Truncated and Overflow Data:

The Designer lets you convert data by passing it from port to port. Sometimes a conversion causes a numeric overflow (numeric data) or truncation (on character columns). For example, passing data from a Decimal (28, 2) to a Decimal (19, 2) port causes a numeric overflow. Likewise, if you pass data from a String(28) port to a String(10) port, the Informatica Server truncates the strings to 10 characters. When a conversion causes an overflow, the Informatica Server, by default, skips the row. The Informatica Server does not write the data to the reject file. For strings, the Informatica Server truncates the string and passes it to the next transformation. The Designer provides an option to let you include all truncated and overflow data between the last transformation and target in the session reject file. If you select Reject Truncated/Overflow Rows, the Informatica Server sends all truncated rows and any overflow rows (between the last transformation and target) to the session reject file.

Target Update Override:

By default, the Informatica Server updates targets based on key values. However, you can override the default UPDATE statement for each target in a mapping. You might want to update the target based on non-key columns. For a mapping without an Update Strategy transformation, configure the session to mark source records as update. If your mapping includes an Update Strategy transformation, the Target Update option only affects source records marked as update. The Informatica Server processes all records marked as insert, delete, or reject normally. When you configure the session, mark source records as data-driven. The Target Update Override only affects source rows marked as update by the Update Strategy transformation.

Overriding the WHERE Clause:

You can override the WHERE clause to include non-key columns. For example, you might want to update records for employees named Mike Smith only. To do this, you edit the WHERE clause as follows:

UPDATE T_SALES

SET     DATE_SHIPPED = :TU.DATE_SHIPPED,

TOTAL_SALES = :TU.TOTAL_SALES

WHERE :TU.EMP_NAME = EMP_NAME

AND EMP_NAME = 'MIKE SMITH'

Entering a Target Update Statement:

Follow these instructions to create an update statement. To enter a target update statement:

• Double-click the title bar of a target instance.
• Click Properties.
• Click the arrow button in the Update Override field.
• The SQL Editor displays.
• Select Generate SQL. The default UPDATE statement appears.
• Modify the update statement. You can override the WHERE clause to include non-key columns.
• Click OK. The Designer validates the SQL when you save the mapping.

General Rules:

Keep the following rules in mind when entering custom target update queries:

• When you save a mapping, the Designer verifies that you have referenced valid port names. It does not verify the accuracy of the SQL.
• If you update an individual row in the target table more than once, the database only has data from the last update. If the mapping does not define an order for the result data, different runs of the mapping on identical input data may result in different data in the target table.
• A WHERE clause that does not contain any column references updates all rows in the target table, or no rows in the target table, depending on the WHERE clause and the data from the mapping. For example, the following query sets the EMP_NAME to 'MIKE SMITH' for all rows in the target table if any row of the transformation has EMP_ID > 100.

UPDATE T_SALES

SET EMP_NAME = 'MIKE SMITH'

WHERE :TU.EMP_ID > 100

• If the WHERE clause contains no port references, the mapping updates the same set of rows for each row of the mapping. For example, the following query updates all employees with EMP_ID > 100 to have the EMP_NAME from the last row in the mapping.

UPDATE T_SALES

SET EMP_NAME = :TU.EMP_NAME

WHERE EMP_ID > 100

• If your mapping includes an Update Strategy transformation, the Target Update statement only affects records marked for update.
• If you use the Target Update option, be sure to configure the session to mark all source records as update. 

Setting the Target Load Order:

In the Designer, you can set the order in which the Informatica Server sends rows to different target definitions in a mapping. This feature is crucial if you want to maintain referential integrity when inserting, deleting, or updating records in tables that have the primary key and foreign key constraints. The Informatica Server writes data to all the targets connected to the same Source Qualifier or Normalizer simultaneously to maximize performance. To specify the order in which the Informatica Server sends data to targets, create one Source Qualifier or Normalizer transformation for each target within a mapping. To set the target load order, you then determine the order in which each Source Qualifier sends data to connected targets in the mapping. When a mapping includes a Joiner transformation, the Informatica Server sends all rows to targets connected to that Joiner at the same time, regardless of the target load order. To set the target load order:

• Create a mapping that contains multiple Source Qualifier transformations.
• After you complete the mapping, choose Mappings-Target Load Plan. A dialog box lists all Source Qualifier transformations in the mapping, as well as the targets that receive data from each Source Qualifier.
• Select a Source Qualifier from the list.
• Click the Up and Down buttons to move the Source Qualifier within the load order.
• Repeat steps 3 and 4 for any other Source Qualifiers you wish to reorder.
• Click OK.
• Choose Repository-Save.

Validating a Mapping:

When you develop a mapping, you must configure it so the Informatica Server can read and execute the entire mapping. The Designer marks a mapping invalid when it detects errors that will prevent the Informatica Server from executing the mapping. The Designer marks a mapping valid for the following reasons:

• Connection validation. Required ports are connected and that all connections are valid.
• Expression validation. All expressions are valid.
• Object validation. The independent object definition matches the instance in the mapping.

Connection Validation:

The Designer performs connection validation each time you connect ports in a mapping and each time you validate or save a mapping. When you connect ports, the Designer verifies that you make valid connections. When you save or validate a mapping, the Designer verifies that the connections are valid and that all required ports are connected. When you save or validate a mapping, the Designer makes the following connection validations:

• At least one source and one target must be connected.
• Source Qualifiers and Normalizers must be mapped to a target.
• Mapplets must be connected. At least one mapplet input port and output port is connected to the mapping. If the mapplet includes a Source Qualifier that uses a SQL override, the Designer prompts you to connect all mapplet output ports to the mapping.
• Datatypes between ports must be compatible. If you change a port datatype to one that is incompatible with the port it is connected to, the Designer generates an error and invalidates the mapping. For example, you have two Date/Time ports connected, and you change one port to a Decimal. The Designer invalidates the mapping. You can however, change the datatype if it remains compatible with the connected ports, such as Char and Varchar.

Expression Validation:

You can validate an expression in a transformation while you are developing a mapping. If you did not correct the errors, the Designer writes the error messages in the Output window when you save or validate the mapping. If you delete input ports used in an expression, the Designer marks the mapping as invalid.

Object Validation:

When you validate or save a mapping, the Designer verifies that the definitions of the independent objects, such as sources or mapplets, match the instance in the mapping. If any of the objects change while you configure the mapping, the mapping might contain errors. If any of the objects change while you are not configuring the mapping, the Designer and other Informatica Client applications track the effects of these changes on the mappings. The Repository Manager displays the status of mappings, so you can see if a mapping is valid or not. If you notice that a mapping is invalid, you can open the mapping and validate it to see the error messages in the Output window.

Steps for Validating a Mapping:

The Designer allows you to validate a mapping while you are working on it. In addition, when you choose Repository-Save, the Designer automatically validates all mappings since the last time you saved. When you validate or save a mapping the results of the validation appear in the Output window. The Repository Manager and MX reports also display whether a mapping is valid. To validate a mapping, open the mapping, and choose Mappings-Validate. If the Output window is not open, choose View-Output Window. Review any errors to determine how to fix your mapping.