ETL Import

This topic is related to the following sections:

• Mapping the Fields of a Datasource

• Mapping Import

• Reporting Import

About ETL Import

As the clients who currently are supported with older versions of Syniti ADM or DMR Concento begin to upgrade into Syniti Migrate application, the active data is converted into this system. The users extract from the older version the mappings for each table into an Excel spreadsheet of standard fields (ETL Import Template.xlsx). They extract and save each active mapping to import into Syniti Migrate using this set of pages. This process imports only Excel extensions and fails should the user attempt to import other extensions (Ex:  txt, csv)

Note

UTF- 8 double-byte characters load using Excel Import (Ex: Japanese or Korean language).

Prerequisites

Prior to importing mappings metadata from a prior version of Stewardship Tier ADM or from DMR Concento complete the following steps:

• Create the Datasources within Catalog > Datasources for Source Datasource and Target Datasource.

• Create the Subject Area within Administer > Setup > Subject Areas.

• Create the Dataset within Catalog > Dataset Design.

• The Project, Release, Milestones, and assigned Subject Area must be set up within Migrate > Project Setup.

• Set the ETL Tool for Syniti Migrate within either the Parameters page or the Release for the dataset.

• The object data must be exported from the source system and organized within the template spreadsheet and saved as an Excel file. The excel file must have same names for the source datasource, target datasource, working datasource and release names as in the Syniti Migrate application.

• The ETL Import Template may be produced locally by clicking the Download Template button. Refer to Download ETL Import Template section for details.

ETL Import Template Spreadsheet

During the initial setup of this application for a new project, the Admin should add the Mapping Import template file to Migrate > File Management so that it is easily accessible for users. The Excel spreadsheet is titled ETL Import Template.xlsx and it may be requested from Syniti IT and then added to File Management for use. The spreadsheet must remain in its original form with no changes to the column sequence or column names. The template provides a few rows of example data for use in determining the field values. Make sure to use the same names for the Release, Dataset, Tables and Datasources as are setup within the target Mapping in Syniti Migrate for this ETL, otherwise the import will fail.

The ETL Import Template spreadsheet contains column headers for: ETL Object Type, ETL Release, ETL Dataset, ETL Dataset Table, ETL Source Datasource, ETL Source Datasource Table, ETL Name, ETL Description, ETL Type, ETL Notes, ETL Index, ETL Milestone, Target Object Type, Task Release, Task Dataset, Task Dataset Table, Task Source Datasource, Source Datasource Table, Task Type, Description, Comment, Datasource, Task Index, Active, Run ETL Type, Command Text, DB Object Name, Affected DB Object Name, Affected Datasource, Export Text Qualifier, Export Field Delimiter, Export Include Header Row, Keep Syncronized, and Data Element Name. It provides directions for use in the first row and the column headers are in the second row as shown below:

Import the Template Spreadsheet

View the following page by navigating to Administer > Imports > ETL Import from the Syniti Migrate Homepage. The initial page displays as ETL Import Setup.

Click Choose File button and then select the appropriate file. Click Open to add this file to the page.

The ETL EXTRACT TEMPLATE spreadsheet contains a row of notes and instructions at the top, and so the value in Header Start Row defaults to 3. Should there be need to alter the start row, change it now. The preview lists the first ten rows of data and the count of rows is provided within the Import File section in Number of Records Detected.

Click the IMPORT ETL  button to initiate the import of data to the assigned objects within Syniti Migrate. A warning message displays to request a selection of either CANCEL or APPEND or TRUNCATE. Depending upon the need, the user may want to append this new data onto an existing object, or they may want to truncate the tables and add this data as the only data for the object.

The page indicates that the job is running. It updates to Status: Import Finished once complete.

Once the job completes, the user may click the VIEW DIAGNOSTICS (Debug Log) button to display the job queue.

Job Queues Steps for Each Import

The resulting job is entered as a Job Queue with the insert of ETL Tasks set as a step.

Job Queue Steps for Failing Import

If the import fails, the system will provide the VIEW DIAGNOSTICS button to view the Debug Log of the Job Queue. In this case, the import failed due to the data not matching existing data for the Datasource. Prior to importing these files, the system must be prepped for the Datasource and other objects. See Prerequisites section above for details.

Validate Data has Imported Successfully

Once the import job queue has completed without errors, and the diagnostics have been viewed and verified, go to Mappings and click to view the ETL tasks to ensure that the list of tasks imported are displaying as expected.

Source Transform Tasks

All tasks labeled with ETL_OBJECT_TYPE = Interface Source will show within the working table ETL tasks as a Source Transform. The new tasks are set to allow for edit and delete, and their status will be unknown until the ETL is run to change status.

Target Transform Tasks

All tasks labeled with ETL_OBJECT_TYPE = Interface Target will show within the working table ETL tasks as a Target Transform. The new tasks are set to allow for edit and delete, and their status will be unknown until the ETL is run to change status.

Download ETL Import Template

To produce a local copy of the ETL Import Template, click the Download Template button. This produces a local version of the Excel spreadsheet used to store the rows of data for ETL Import.