ETL Tasks Add Complex Rules Manually

This topic relates to the following sections:

• Mapping When Using Syniti Migrate ETL Tool

• 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

• Working Databases in Migrate

Overview

ETL Jobs are accessed from all four levels of the Mappings page. Each ETL icon when clicked displays that level of the overall ETL Job with rows of Job Tasks all linked together.

View the following page by navigating to Migrate > Mappings on the Syniti Migrate Homepage.

At each level of the Mappings page the ETL icon servers to execute the job and process the list of tasks assigned. New tasks may be added for each level and the process for adding them uses the same pages. The ETL Tasks maybe be assigned to a field in the mapping, but they can also be independent of field mapping. This section provides the details for adding tasks to a Transform grid.

The user ensures that the view is complete - the system auto generates each view as they are added as Tasks, but they all must be completed and accurate for successful use. During execution, either the entire source set of Tasks may be run, or each individual task.

In the case of an Update, Insert, or Delete rule, these may be run against any working database in the current server. The fields Affected Datasource and Affected Table designate the target of the rule and can apply to working databases. The views are stored in the Rule Datasource (working database of the object), however, they pull from a different or Affected Datasource (any other database of same server).

Note

The Breadcrumb path in the header links to higher levels of the ETL Job chain. The user may jump up from Source to Target or to the Dataset level details for ETL Tasks with a click on the task name.

At this level, the Transform a Source may be run as a Job with each active tasks run in the sequence provided, or the individual task may be run. Click the Execute icon within the header to run all tasks, or click on the Execute icon for the specific task.

Within header of this page there are Quick Use icons:

The ETL Job grid displays the following Columns:

ETL Tasks for Mapping Rule

From the Mappings page, click on Mapping icon in the row for the Target Source to view current mappings. Click a mapping where Mapping Action = Rule to view the details for the mapping.

Click the ETL Tasks (x) button to view the ETL Tasks assigned to this field. Due to the assignment of ETL tasks to specific versions, there may be multiple tasks for the same update by version.

The ETL Tasks for Mapping Rule grid displays the following Columns:

Tasks may be added within ETL Tasks for Mapping Rule by clicking the icon to build a Complex Rule of various task types.

Note

When saving a new task, the Description is defaulted to be the same as the Task Rule (the name of the view or procedure) if the value is left blank.

There are unlimited amounts of updates that may be made against each field in the Mappings page for Rules. Once the new Rule is saved, it displays in a grid for ETL Tasks for Mapping Rules page as shown below.

The ETL Rules grid displays the following Column Headers:

Edit a Complex Rule

A click on the Edit  icon provides a window to make changes to the Description, Comment or any of the fields within section Execution. The Active toggle may be updated from the grid.

Within the details for ETL Rules, the user may alter the field values as described below.

Click the Rule DDL tab to view the DDL script for the ETL Task. You can run the DDL script to see if it returns correct data as expected. The script may be opened in SQL Editor for use in testing in the database.

When finished with edits, click the Save  icon to complete.

Add a Complex Rule Manually

While viewing the ETL Tasks for Mapping Rule page, click the Add  icon to create a new Complex Rule. In this example, select Task Type = Update Rule. In this scenario, the rule updates the value based upon only a view that sets up Fieldname and Fieldname_New. The Task for this type of Rule includes scripting to UPDATE value to Fieldname_New as shown below.

Note

Methodology for Naming of these SQL Complex Rules -

View - <WORKING_TABLE>_Upd_<TARGET_FIELD> _SEL

Note

Within the database, the user completes the View for update and the ETL Job Tasks perform the Update as part of the job. There is no need to build a Stored Procedure for any of the Task Types. The scripting for the correct type of task is determined by the Task Type (Insert, Delete, Update as example).

Note

The Datasource filters to appropriate list for these as CONSTRUCT or a working database. For a Refresh Snapshot Table task type, the Datasource filters to only Snapshot Datasources.

Note

When adding a Complex Rule to the ETL Jobs Task List directly, the user MUST update field Mapping Field within the details to tie that field back to the Field Mapping. Otherwise, the rule is not counted or included in the ETL Tasks for which the field is being built.

Examples of Job Task Types

The details page used in adding a new task to the ETL Task List is dynamic and the fields and requirements change based upon the Task Type entered. This image below is of the Edit ETL Task window before task type is assigned. The system defaults in field values that apply based upon the Datasource, working database, and working table name for this window.

Delete Rule

Although the ETL auto generator builds a Delete Rule during the initial access, the user may mark that one as inactive (Do Not Delete the auto generated Delete rule as it builds again) and manually add a new task as Task Type = Delete Rule. This action type runs the view against a Delete stored procedure based upon the Key fields and filtering of records.

The Delete Rule could apply to the Working or Target tables in an effort to remove rows of data that are not needed. First, create a View containing the Primary Key Fields that filter data to the list to delete, and register that view within this rule as a Rule Name*. The associated Mapping Field should be identified to link this delete to a mapping field but is not required. However, the Affected Table needs to reference the working or target table for the deletion and the Affected Datasource must point to the correct database that stores this table. The delete rule may be set against any datasource in the database system.

Note

Refer to section Cross Database or Cross Server ETL Rules for Delete, Insert, or Update below for setting up a rule that runs within one server to update a table in another server.

Note

If the Rule Datasource or Affected Datasource are pointing to a Working database within the Edit ETL Task details, it must be defined within the Version database as well or running the ETL against that version fails.

Insert Rule

Although the ETL auto generator builds an Insert Rule during the initial access, the user may mark that one as inactive (Do Not Delete the auto generated Insert rule as it builds again) and manually add a new task as Task Type = Insert Rule. Make sure to validate that the Affected Datasource and Affected Table field are pointing to the correct database and table for this insert. This action type runs the view against an Insert stored procedure based upon the Primary Key Fields and filtering of records.

The auto generated Insert Rule inserts all legacy fields from Datasource as well as make updates to mappings as follows:

• All Default rules insert the default value to the mapped field during the Insert Rule.

• All Simple Copy rules insert the copied field value in the mapped field during the Insert Rule.

Note

Refer to section Cross Database or Cross Server ETL Rules for Delete, Insert, or Update below for setting up a rule that runs within one server to update a table in another server.

Note

A Simple Copy rule copies the value of a field within the Datasource / Table being mapped. Any other Datasource / Tables would become a Complex Copy rule and would require an auto generated Update Rule build.

Note

If the Rule Datasource or Affected Datasource are pointing to a Working database within the Edit ETL Task details, it must be defined within the Version database as well or running the ETL against that version fails.

After the Insert Rule is saved successfully, the details of the Insert Rule scripting is available in the Rule SQL field.

Note

The database view must be maintained within the database. Migration ETL is for storage and use of these views and procedures.

Refresh Snapshot Table

This can be useful for READ BACKS during post load processing, or if you are loading an object that is dependent on other people loading SAP data. So refreshing some target tables might be necessary during the execution of your own job.

When the task is run, it produces a job queue step as shown below:

Update Rule

Update Rule task type is the most frequently used rule. Any updates to fields that are outside of the basic copy, default, or the auto generated Rule requires an Update Rule task added to the ETL job list. Build an update View within the database including the Primary Key Fields and filtering prior to adding the new task to the ETL Job list. For this rule it is very important that the field Mapping Field contains the value for the field being mapped. This way any new Update Rules added displays in the ETL Tasks list for the field in the Mappings page. The View should be based upon a Field / Field_New for each field updated (SET FIELD = V.FIELD_NEW)

Note

If the Rule Datasource or Affected Datasource are pointing to a Working database within the Edit ETL Task details, it must be defined within the Version database as well or running the ETL against that version fails.

Note

Refer to section Cross Database or Cross Server ETL Rules for Delete, Insert, or Update below for setting up a rule that runs within one server to update a table in another server.

After the Update Rule is saved successfully, the details window may be accessed to view the Rule DDL tab for details of the Update Rule scripting.

Note

The database view must be maintained within the database. Migration ETL is for storage and use of these views and procedures.

Within header of this page there are Quick Use icons:

Stored Procedure

The user clicks on the Add  icon to create a new Complex Rule. In this example, select Task Type = Stored Procedure. In this scenario, the rule updates the value based upon a view and stored procedure. The Task for this type of Rule is the Name of the stored procedure that updates the field. In this case, the Stored Procedure updates the data and not the system.

Note

Methodology for Naming of these Procedure Complex Rules -

        SP -  <WORKING_TABLE>_Upd_<TARGET_FIELD>

        View - <WORKING_TABLE>_Upd_<TARGET_FIELD> _SEL

Note

Within SQL Server database, the user manually build out the View for update and Stored Procedure.

Cross Database or Cross Server ETL Rules for Delete, Insert, or Update

You can create and execute cross-server ETL tasks (for Delete, Insert, or Update) anytime you need to manipulate data from one server to a second or remote server. For example, when you have a SAP HANA or PostgreSQL working database where CONSTRUCT is on a remote database, or when the Migration Working Server (any working database type) is inserting into the SAP HANA LTMC (Migration Cockpit) staging database.

In this situation, use an ETL Task with the following fields, which allow ETL rules to pull data from one server to alter a table in another server:

• Rule Datasource contains the database where the Rule is run.

• Affected Data Source stores the database of the table that is affected.

• Affected Table is the table from the Affected Data Source.

These Datasources are setup within the Datasources to point to the server.

The cross database rule displays as XSERVER INSERT type.