13 Data Conversion and Migration Design

There are several aspects implementation should consider when designing the legacy data extract processes and creating the Input Data Files. The data conversion process is very flexible and configurable, and can be fine-tuned to address both application and client data specifics.

This chapter provides information about designing extract processes, including:

Extract/Upload by Table or Maintenance Object

The SQL Loader allows users to insert data into one or multiple tables from a single input file. Choose the more convenient option, depending on the structure of the legacy data (source), data volumes, and extract technique:

  • Table-level. Extract file contains data for the single table. The data is loaded into a table in the OUAF/ application database.
  • Maintenance Object-level. Extract file contains data for the entire object. The data is loaded into a set of tables that represent the corresponding Maintenance Object in the OUAF/ application database.

Both options are supported in Cloud Service Foundation. Generate the artifacts and review the differences in the specifications.

The table below illustrates the difference between Table and Maintenance Object data file:

Target Object Table: CI_PER Data file contains records for a single table. Maintenance Object PERSON:Tables:CI_PERCI_PER_NAMECI_PER_ID ….etcData file contains records for multiple tables within Maintenance Object. Table name serves as “record type” qualifier.
Input Data File Layout 1234, IND, Doe,…5678, IND, Moon,...9063, BUS, ABC Corp,.. CI_PER 1234, IND, Doe,…CI_PER 5678, IND, Moon,...CI_PER 9063, BUS, ABC Corp,..CI_PER _ID 1234, SSN,72346781CI_PER _ID 5678, SSN, 87635241CI_PER _ID 9063, EIN, 09182835CI_PER _ID 9063, TID, 82528555CI_PER _NAME 1234, Doe, MaryCI_PER _NAME 5678, Moon, Barry

CLOB Data in a Secondary File

CLOB data can be supplied as part of the record in the "main" data file or as a secondary file. Once again, the decision should be made based on the source data volumes, extract techniques, and the availability of the CLOB data in most records.

  • If most of the records have CLOB column(s) populated, and/or the CLOB field often contains large amount of data, it may make sense to use a secondary file.
  • Otherwise, if the CLOB column(s) are rarely populated and/or the CLOB field rarely contains large amount of data, you may choose to include the CLOB data in the record.

Note:

If supplied as secondary file, the CLOB data file has to contain exactly as many records as the main file. This means that a line has to be added even for empty CLOB fields.

Both options are supported. The definition is controlled by the Conversion Instruction (Conversion Task Type).

Multiple Data Files for Single Table or MO Upload

The Cloud Service Foundation data upload process supports the upload into single target (table or maintenance object) from multiple data files. For example, instead of extracting a large Payment table into a single payment.csv file, you can split the extract into payment1.csv, payment2.csv, payment3.csv, and so on.

It is recommended to keep the file size under 2 gigabytes. The number of files is unlimited. Naming conventions apply. See the online help for more details.