Create Working Tables

This topic is related to the following sections:

• Importing Datasets into Mapping

• Build XML Scripting for Dataset

• Create & Maintain Target Sources

• Mapping the Fields of a Datasource

• Create Target Tables

• Create Reports

• Value Mappings by Object

• Execution of the ETL Jobs

• Mapping History

• Maintain Mapping Status

• Maintain Target Reports

• Execute Metric Calculations

• Data Type Conversion in the SKP

About Creating Working Tables for Mapping

A prerequisite for creating working tables is to complete Mapping the Fields of a Datasource.

The user adds a Source table to the Target table, and then completes the mapping of fields for translation of data from source to target. Once these steps have completed, the working table for these mappings is created in the database system.

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

As part of the process for adding the Sources to the Dataset for mappings, the working tables automatically build within the database, and the names display within the column Working Table. This section is useful should the auto build fail.

Note

Click the link for the Working Table opens the working table details within the Working Database.

Create a Working Table

Select the Release, Dataset, and Target. From within Mappings page, click on the Edit  icon for the Dataset to view the Target Source Details.  Click on Create Working Table  button initiate a Job Queue to build the Working table for this Dataset. This job also import all Metadata (columns) for the tables automatically.

Note

Should the Release have an ETL Tool setting of SAP Data Services, then the section for SAP Data Services is visible. Otherwise, it is hidden. The XML Build button creates a working table first and then builds out the XML Scripting for the ETL Tool of SAP Data Services, and any subsequent changes to mapping that alters the Working table is updated within the Database using the CREATE WORKING TABLE button.

This step is relevant once the Source has been assigned to the Target and fields have been mapped within Mappings.  Also, if using an ETL Tool other than Migration, the step for Building XML for the Working Table (Source) would run in conjunction with this step. 

View the Job Queue

To determine that the job ran successfully, use pathway - Administer > Job Queues to view the job for this process.  This job is named 'Create working table for Interface Source src...' and Build Working Table is listed as a Step in the right side panel.  To view the details of the step, click on the Debug Log  icon. The job Build/Refresh Working Table builds the table in the system.

Note

This Job imports all Metadata (columns) for the tables automatically.

If the job created an actual table within the database, there is a Log Entry with Message as shown in the image below.  Notice that the Message indicates that CREATE TABLE was performed successfully.

If the job did not create an actual table within the database, the Build/Refresh Working Table job is not present.  In this case, the table may already exist within the working database, and so this build is not necessary.

Note

If the Working Table already exists within the working database then the job queue runs an ALTER TABLE update for the table rather than a CREATE TABLE Insert.

Once these tables are created in the Job Queue, the table names are provided within Mappings page grid for the Target and the Source sections as shown below. Hover over the Working Table name to view the link - click on the link to go to the Working Database table details for this working table.