Mapping When Using Syniti Migrate ETL Tool

This topic relates to the following sections:

• ETL Jobs

• ETL Jobs Details

• Execution of the ETL Jobs List

• ETL Version Selection

• ETL Tasks at the Release level

• ETL Tasks at the Dataset level

• ETL Tasks at the Target level

• ETL Tasks at the Source level

About Mapping when using Syniti Migrate ETL Tool

Syniti Migrate allows for conversion of migration data using the ETL Jobs tool stored within Migrate > Mappings section of Syniti Migrate.

If the project requires an ETL processing tool, they may activate the Syniti Migrate ETL Jobs Tool for one or more Releases within the application. Effects of this change are:    

• Developer completes the auto generated views for all Rules as part of the ETL build process. They may also add additional manual rules.

• Developer maintains the order of all steps of the conversion within the ETL Jobs page

• Developer executes the ETL Job process to run the conversion through SQL Server to update the working table and target table

• As each Mapping is marked as Complete, the system updates for changes to the Working Table for all XREF and RULE XREF actions

• As each Mapping in a dataset is modified, the system updates the target table to reflect same.

Note

XML Builds and Views are not relevant for the Dataset, Working tables, or Snapshot Management as they are used within SAP Data Services. These options are hidden.

Users maintain the Syniti Migrate ETL Tool within these sections of the system:

• Parameters page - Field ETL Tool allows for assignment of Syniti Migrate as the ETL Tool as a default. Refer to section Administer > Setup > Parameters for details.

• Project Setup > Release - Field ETL Tool allows for assignment of Syniti Migrate as the ETL Tool for each Release. Refer to section Migrate > Project Setup > Creating & Maintaining Releases for details.

Mapping of the Datasource to target table - Map a field as a Type = Rule or Rule XRef to provide access to ETL Tasks page where all supporting update rules are stored for ETL process. Refer to section Mapping Action Details for a Rule using Syniti Migrate ETL Tool below for details.

• Mappings > Release - Execute the ETL Job at the Release level to process all Datasets within the Release. Refer to section Migrate > Mappings > Mapping the fields of a Datasource > ETL Tasks at the Release level  for details.

• Mappings > Dataset - Execute the ETL Job at the Dataset level to process all Targets within the Dataset. Refer to section Migrate > Mappings > Mapping the fields of a Datasource > ETL Tasks at the Dataset level  for details.

• Mappings > Target  - Execute the ETL Job at the Target level to process all Rules of each Datasource within the Target. Refer to section Migrate > Mappings > Mapping the fields of a Datasource > ETL Tasks at the Target level  for details.

• Mappings > Source  - Execute the ETL Job at the Source level to process all Rules for that Datasource. Refer to section Migrate > Mappings > Mapping the fields of a Datasource > ETL Tasks at the Source level  for details.

Prerequisite for this Process

In addition to the dependencies discussed within section Migrate > Mappings, the selection of an ETL Tool within the Release is required.

Although the default for the Project may be set as any ETL Tool (including Syniti Migrate or SAP Data Services) as shown in the Parameters image below, each Release of the Project Setup must be assigned to the appropriate ETL Tool to set the use of that tool for the Mappings and Rules builds.

Mapping Action Details for a Rule using Migration ETL Tool

ETL (Extract Transform Load) process within Syniti Migrate - Within Mappings, the conversion of data through mapping actions of field values produces a list of views in the database as well as building a conversion that may be executed within Syniti Migrate to translate the data from Source to Target.

Note

If the ETL Tool <> Syniti Migrate, all ETL icons and ETL Task buttons are hidden - it is tied to user Security (Changes to the ETL Tool setting requires users to log off / log in to refresh and update these settings).

Once the Mapping Rule action has been determined, options for the rule's details display -

1. SIMPLE RULE - A field ' Rule Sql' displays with a text field so the user may include a Simple Rule (EX: LEFT(NAME1, 25) ). The entry is part of the Simple Rules Task of the ETL Job for this Object (EX:  zNAME1 = LEFT(NAME1, 25) )

2. COMPLEX RULE - The ETL TASKS  button displays as active. Auto generate the Complex Rules based upon Views saved within ETL Tasks page.

Note

Complex Rules generate a default Update Rule view in the working database if no other Rules have been registered against that DATA_MAP_ID.

Note

In the event that the data map changes from complex to simple (i.e., non-complex) the complex tasks (rules) become disabled. For example, if a mapping changes so that it now can be handled via a simple Copy, all ETL tasks (whether automatically or manually created) should become disabled when the job is rebuilt. The update is part of the insert task so the additional rule(s) won't be necessary.

Build the Complex Rule

A click on the ETL TASKS  button changes pages to display the ETL Rules page for this mapping. The page opens with no rows of tasks at first. The tasks are auto generated once the user initiates the Build functions by opening the ETL Tool set with a click on ETL  icon within the Mappings main page.

Note

This process is relevant for projects using Oracle, HANA DB, or SQL Server Database tools.

Once the ETL Tasks have been built for the mappings marked as Complete, the ETL Tasks for Mapping Rule page contains the rows for each defined rule. In the example below, the Update Rule was auto generated as part of the ETL Jobs Task Build function. There are two tabs for Edit ETL Task - Edit ETL Task and Rule DDL

The Edit ETL Task tab contains fields for the task type, while the Rule DDL tab shows the scripting of the Rule (View in database).

Rule DDL Tab contains scripting text for the task.

The Quick Access icons perform as listed:

Icon	Name	Use
	View Mapping	Click to change pages to view the related mappings of this object.
	Copy	This icon copies the data into a new task.
	Save	Saves the current updates to system.
	Cancel	Cancels the current action without saving.

Subsequent Tasks for using Syniti Migrate ETL Jobs Tool

Once all of the Mapping has completed, the user may build the ETL Job for this process. The first time an ETL icon is clicked, it attempts to build out the entire set of tasks for that panel and it sets all simple mappings as Build Complete. It produces a list of errors if necessary. At this point, the Rule Views and Stored Procedures should be completed within SQL Server by the user who validates that they are correct.

Highlight a row for the Target Source and then click on the ETL  icon  to view the ETL Job List for that Target Source Transform.

Refer to section Migrate > Mappings > Migration ETL Job > Execution of the ETL Jobs List  for more details of this process.

Execute the ETL JOB Queue

To run the object through the conversion process, the user clicks on the ETL  icon within the row of the Release, Dataset, Target, or Target Source. This opens the Job Queue window so that the user may select and run the job at that level. Should the user want to manage the jobs for the project overall, they use the ETL Job List from the main menu. Refer to section Execution of the ETL Jobs for details of this process.