Legacy Data

When one talks about Oracle E-Business suite implementation following questions about data arise:

  • How existing legacy system data will be available in new system?

  • Will there be a complete move from OLD system to NEW system or both systems will co-exists for sometime before complete cut over?

  • Will there be any communication needed between OLD and NEW system?

  • Most important is HOW to achieve it?

While discussing about these questions few terms get used interchangeable viz. Data Migration, Data conversion, Interfaces etc. All can be brought under one umbrella/category called Data Movement.

This white paper talks about various strategies/technologies which can be used for data communication between legacy system and Oracle E-Business Suite. This gives the overview about various technical components involved in taking care of most important word called "DATA".

This white paper tries to give direction to answer above questions. To make legacy data available to new system, one has to perform data entry manually or use tools like data loader, win-runner which simulate keyboard/mouse action and make data entry faster. If data is huge the programmer/developers help is needed to build the program to automate data loading. If two systems are going to coexists for some time and communicate with each other then programs which run at regular intervals to move the data are needed.

Defining Various Terminologies

Data Migration is a process of moving required (and most often very large) volumes of data from our clients' existing systems to new systems. Existing systems can be anything from custom-built IT infrastructures to spreadsheets and standalone databases.

Data conversion can be defined as a process of converting data from one structural form to another to suit the requirements of the system to which it is migrated.

Interface is set of programs for connection between two systems in order to synchronize the Data. They can be Manual, Batch or Real-Time. They are used repeatedly and should therefore be designed and constructed in the most efficient manner possible.

Though Data Migration, conversion, interfaces are treated differently but technical folks should not differentiate between them. The design of the all program should same like interfaces, because in most of the implementation data need to be moved multiple times and for some time both legacy system and Oracle Apps 11i happened to be active. Bottom line if data needs to be moved programmatically i.e. without using tools like data loader or manual data entry then it is better to design the program which can be execute multiple time as a concurrent program.

Data Movement Entities

Generally data conversion/interfaces include following entities

  • Legacy System (Oracle/Other database e.g. DB2/File System)

  • Oracle Apps custom staging tables

  • Shell scripts to read data files and load them in custom staging tables using SQL*Loader utility

  • Oracle Apps custom data validation programs

  • Oracle seeded open interface tables (e.g. MTL_TRANSACTIONS_INTERFACE)

  • Oracle seeded APIs (e.g. Process Order API)

  • Oracle Apps Seeded Import Program (e.g. Item Import, BOM Import)

Legacy System

There can be various types of legacy systems which can be based on databases like Oracle, DB2 etc or based on file systems. Generally it is better to use files extracted from legacy system while development of data conversion program as data volume is very high. DB Links can be used for connecting to other databases while developing interfaces.