Working with Mappings

Mappings relate source and target metadata, and allow moving and transforming data from several source datastores (files, tables, XML) to target datastores.

Designing a Mapping: Overall Process

The overall process to design a mapping follows the steps given below.

  1. Creating a New Mapping
  2. Adding the Target and Sources
  3. Joining the Sources
  4. Filtering the Sources
  5. Mapping the Target Columns

Creating a New Mapping

To create a new mapping:

  1. Click on the New Mapping button in the Project Explorer toolbar. The New Map Diagram wizard opens.
  2. Select the parent folder or project for your new resource.
  3. Enter a File Name and then click Finish. The mapping file is created and the editor for this file opens.

Adding the Target and Sources

Tip: To see in the Project Explorer view the objects contained in the metadata files, configure this view accordingly. Select the Customize View in this view’s menu (as shown in the screen copy below) and make sure that the Semarchy Convergence for DI Diagram Objects are not selected.

To add the source and target datastores:

  1. In the Project Explorer, expand the metadata file containing the datastore (table, file, XML file) that you want to integrate.
  2. Drag and drop first the datastore into which data will be loaded (the target) from the Project Explorer into the mapping editor. This target datastore appears in the mapping diagram with a red background.
  3. Select this target datastore.
  4. In the properties inspector, set the following properties:
  5. Drag and drop one of datastores from which data will be extracted (a source) from the Project Explorer into the mapping editor. The source datastore appears in the mapping diagram with a blue background.
  6. In the properties inspector, set the following properties:
  7. Repeat the two previous steps for each source from which data will be extracted.
  8. Press CTRL+S to save the mapping.

Warning: When adding datastores to the mapping, the first datastore added is always the target. There is only one target per mapping.

Note: While adding source and target datastores, blocks representing the load and integration process templates are automatically added in the mapping editor.

Note: While adding source datastores, the columns from these sources are automatically mapped to the target datastore columns using column name matching.

Joining the Sources

The various source datastores must be related using Joins.

To create a join:

  1. Select a column from a source datastore in the mapping diagram.
  2. While keeping the mouse button pressed, drag this column on another source column in the mapping diagram.
  3. Release the mouse button. A join is created between these two columns and appears in the diagram.
  4. In the Expression Editor view, edit the code of the join. You can lock the expression editor and drag columns from the diagram into the expression editor. See the Mapping, Filter and Join Expressions section for more information about mapping expressions.
  5. Select the join in the mapping diagram.
  6. In the Properties view, set the following Standard Properties:
  7. In the Properties view, optionally set the following Advanced Properties:
  8. Press CTRL+S to save the mapping.

Warning: Be cautious when using Cross and Full joins as they may lead to a multiplication of rows and performance issues.

Filtering the Sources

Data from the various source datastores may be filtered.

To create a filter:

  1. Select a column from a source datastore in the mapping diagram.
  2. While keeping the mouse button pressed, drag this column in the mapping diagram.
  3. Release the mouse button. A filter is created and appears in the diagram.
  4. In the Expression Editor view, edit the code of the filter. You can lock the expression editor and drag columns from the diagram into the expression editor. See the Mapping, Filter and Join Expressions section for more information about filter expressions.
  5. Select the filter in the mapping diagram.
  6. In the Properties view, set the following Standard Properties:
  7. Press CTRL+S to save the mapping.

Tip: It may be preferable to position the filters on the source to reduce the volume of data transferred from the source data server.

Mapping the Target Columns

The target columns must be mapped with expressions using source columns. These expressions define which source columns contribute to loading data into the target columns.

To map a target column:

  1. Drag a source column and drop it on top of the column in the target datastore. The source column is appended in the expression of this target column. You can also drag and drop columns from the diagram in the expression editor of the target column. See the Mapping, Filter and Join Expressions section for more information about mapping expressions.
  2. Select the target column.
  3. In the Properties view, set the following Standard Properties:
  4. Press CTRL+S to save the mapping.

Understanding Column Icons

Source and target columns have an icon that contains the first letter of their datatype. This icon appears in grey when the source column is not used in the mapping or when the target column is not mapped

In addition, target columns are tagged with icons to identify their key properties. The various icons and their meaning are listed in the following table.

Icon Meaning
The yellow key indicates that the column is part of the key. The white star in the upper left corner indicates that the column is not nullable. If reject management is activated, rows with null values for this column are rejected. The letter represents the column data type (I: Integer, V:Varchar, etc.)
The cross in the upper left corner means that the column is not nullable but Semarchy Convergence for DI does not check the nulls for this column.
The plus sign in the upper left corner means that the column is nullable but Semarchy Convergence for DI may check the nulls for this column.
This expression runs in the source.
This expression runs in the staging area.
This expression runs in the target.
These four letters have the following meaning when they appear:
  • I: Enable Insert is selected for this mapping.
  • U: Enable Update is selected for this mapping.
  • A: Aggregate is selected for this mapping.
  • T: One or more tags are set for this mapping.

Mapping, Filter and Join Expressions

For a mapping, filter or join, you specify an expression in the expression editor. These expressions are also referred to as the code of the mapping, filter or join.

The mapping, join or filter code can include any expression suitable for the engine that will process this mapping, filter or join. This engine is either the engine containing the source or target datastore, or the engine containing the staging area schema. You select the engine when choosing the Execution Location of the mapping, filter or join. This engine is typically a database engine. In this context, expressions are SQL expressions valid for the database engine. Literal values, column names, database functions and operators can be used in such expressions.

Examples of valid filter expressions:

Examples of valid join expressions:

Examples of valid mapping expressions:

Tip: When editing a Join or a Filter, think of it as a conditional expression from the WHERE clause. When editing a mapping, think of one expression in the column list of a SELECT statement.