Working with Mappings |
Previous
|
|
Next
|
Working with Metadata |
|
Working with Processes |
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.
-
Creating a New Mapping
-
Adding the Target and Sources
-
Joining the Sources
-
Filtering the Sources
-
Mapping the Target Columns
Creating a New Mapping
To create a new mapping:
- Click on the
New Mapping button in the
Project Explorer toolbar. The
New Map Diagram wizard opens.
- Select the parent folder or project for your new resource.
- 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:
- In the
Project Explorer, expand the metadata file containing the datastore (table, file, XML file) that you want to integrate.
- 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.
- Select this target datastore.
- In the properties inspector, set the following properties:
-
Name: Name of the target table. The default value is the physical name of the target datastore.
-
Enable Rejects: Check this box to enable rejects management. When this option is selected, rows in the data flow not meeting the target table’s constraints are isolated in the rejects instead of causing a possible runtime failure.
-
Tag: Add a tag to the table. Tags are used in certain process templates.
-
Description: Free form text.
- 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.
- In the properties inspector, set the following properties:
-
Alias: Alias used in the expressions when referring to this source datastore. It defaults to the datastore name.
-
Use CDC: Check this box to enable consumption of changed data from the source, captured via the CDC feature..
-
Tag: Add a tag to the table. Tags are used in certain process templates.
-
Description: Free form text.
- In the
Advanced properties section, the
Order defines the order of this datastore in the FROM clause generated for this mapping.
- Repeat the two previous steps for each source from which data will be extracted.
- 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:
- Select a column from a source datastore in the mapping diagram.
- While keeping the mouse button pressed, drag this column on another source column in the mapping diagram.
- Release the mouse button. A join is created between these two columns and appears in the diagram.
- 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.
- Select the join in the mapping diagram.
- In the
Properties view, set the following
Standard Properties:
-
Enable: Enables or disables the join.
- Set the join type by selecting either
Join Type to
Inner,
Full or
Cross or by selecting the
Left Part or
Right Part to perform a
left outer or
right outer join. See
Join (SQL) for a definition of the join types.
- Set the
Execution Location. The join may be executed within the source system (when joining two datastores in the same data server) or in the staging area.
-
Description: Free form text.
- In the
Properties view, optionally set the following
Advanced Properties:
-
Join Type:
Explicit uses the ISO Syntax (join in the FROM clause),
Implicit places the join in the WHERE clause, and
Default takes the default type defined for the technology. This latter option is preferred for non-inner joins
-
Order: Defines the order of the join when using the
Explicit Join type.
- 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:
- Select a column from a source datastore in the mapping diagram.
- While keeping the mouse button pressed, drag this column in the mapping diagram.
- Release the mouse button. A filter is created and appears in the diagram.
- 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.
- Select the filter in the mapping diagram.
- In the
Properties view, set the following
Standard Properties:
-
Enable: Enables or disables the filter.
-
Aggregate: Check this option if this filter is an aggregate and must produce a HAVING clause.
- Set the
Execution Location. The join may be executed within the source system or in the staging area.
-
Description: Free form text.
- 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:
- 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.
- Select the target column.
- In the
Properties view, set the following
Standard Properties:
-
Enable: Enables or disables the mapping.
- Set the
Execution Location. The mapping may be executed within the source system, in the staging area or in the target itself (while inserting data into the target).
-
Use as Key: Check this option if this column must be used as part of the unique key for this mapping. Several columns participate to the unique key in the mapping. These may be the columns from one of the target table’s unique keys, or different columns. This unique key is used in the context of this mapping to identify records uniquely for reject management and target records update purposes.
-
Enable Insert: Enable inserting data with this mapping.
-
Enable Update: Enable updating data with this mapping.
-
Aggregate: Indicates that this column contains an aggregate expression. Other (non-aggregated) columns are added in the GROUP BY clause of the queries.
-
Tag: Add a tag to the mapping. Tags are used in certain process templates.
-
Description: Free form text.
- 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:
-
CUSTOMER_COUNTRY LIKE 'USA'
-
COUNTRY_CODE IN ('USA', 'CAN', 'MEX') AND UCASE(REGION_TYPE) = 'SALES'
Examples of valid join expressions:
-
CUSTOMER.COUNTRY_ID = COUNTRY.COUNTRY_ID
-
UCASE(CUSTOMER.COUNTRYID) = GEOGRAPHY.COUNTRY_ID AND UCASE(CUSTOMER.REGIONID) = GEOGRAPHY.REGION_ID
Examples of valid mapping expressions:
- For the CUSTOMER_NAME field:
SRC.FIRST_NAME || ' ' || SRC.LAST_NAME
- For the SALES_NUMBER aggregate field:
SUM(ORDERLINE.AMOUNT)
- For an OWNER field:
'ADMIN'
to set a constant value to this field.
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.