Business Intelligence (BI) - ERP - Dashboards

Data Conversion for Oracle Applications

This topic is shared to provide an overview of Data Conversion for Oracle ERP.

Approach used for Data Conversion

1. Conversion Data Mapping ( map to specify the data of legacy system –> data of oracle ERP system )
The data mapping process provides detailed lists of the data sets and data elements that will need to be moved into the Oracle ERP tables during the data conversion.

During this process, some decisions will need to be made with regard to obtaining information needed by the target application that may not be present in the old system(Legacy System ). Decisions like ->>Default settings, user input, and new data entries are some of the issues that must be addressed during this phase.
The output of this activity is data mapping tables that show what is needed for the Oracle target application processing to meet business operational requirements and where these data elements will come from. Based on this mapping, a design of the legacy data extract is defined.

2. Download Programs ( programs to extract data from database and insert into the Flat file )
These programs are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file. This can be done with a 3rd party tool or programmatically done within the lagacy database systems.carefull planning must be done before the flat file created as the data can be placed in the order its extracted , type of delimeter used( either ,(comma) or | (pipe) or anything other, number of records, etc.

3. ASCII Flat File (Flat file / Text file)
Most database or file systems output data in text form. A comma or space delimited, variable or fixed format data file from the existing system should be generated. If you cannot find a way to produce clean text data, try generating a report to disk, using a text editor to format your data.
One of the outputs of conversion data mapping is to identify the legacy data element data types and precision(example decimals and dates specifics).

Once the conversion data mapping is done, you should know if there are inconsistencies between the legacy data types and the requirements for the Oracle data types. If there are translations that need to take place, these translations can be performed on the legacy system prior to creating the extract or in an interface table. Also, if you are creating a fixed length extract file, you need to take into account the precision level of decimal numbers.

4. Upload Program
Once data has been put into an ASCII EBCDIC flat file and physically moved onto the same computer that has the Oracle RDBMS, the next step is to load the data into a relational database environment.
Programs must be written and run to move data, validate data, and insert/update standard values into default fields. Usually a single loader program is written for each data table being loaded.

5.Interface Tables ( know more about Interface tables here )

a. Description of Interface Table
The detailed technical description of any interface table that the data is placed into from the ASCII flat file is prepared. An interface table that mimics the production table into which the data will eventually be loaded into should be defined. This allows you to manipulate the data as needed before loading the legacy data into the production tables.

b. Creation of Interface Table
Before loading the Oracle Application production tables, the legacy data should first be loaded into temporary or interface tables. The interface tables provide a location for you to manipulate and translate the data as needed before validating the data and loading the application production tables. These temporary interface tables need to be built before you run the loader script to populate these tables. The interface tables may be standard Oracle Application interface tables or may be custom interface tables.

6. Translation Programs
These scripts are developed to translate data from the existing system format into useful data for the Oracle target application. An example of this might be taking the date format that exists in the legacy system and converting it into an Oracle format. There may be several or no translation programs, depending on both the type of data coming across and the format of that data.

7. Interface Programs ( example )
The interface program scripts are used to populate the production database. The purpose of the interface programs is not only to move the data to the target tables but also to validate the data that would be validated by the form in the target application if the data was converted manually.

8. Application Production Table
This is the final production data table where the converted data resides. These tables are identified early on when doing the initial data mapping. These tables drive some of the translation programs that must ultimately ensure that 100% of the information that the target applications require is present in the final data structures.

9. Testing
This test plan has been integrated into the entire conversion process so that, even during the pre-conversion steps, some type of validation reports are generated from the legacy systems, to be compared later with the converted data.
The approach taken is to use as many standard reports as possible that are available in the legacy and target system for the final data validation. If no reports support the validation requirements, then custom scripts will need to be created for specific validation purposes.

10. Write and Perform Conversion Execution Plan
The conversion execution plan is the execution document that is to be followed when performing the actual conversion. This document is specifically tailored for the <Company Short Name> conversion.

Filed under: ERP, Interview Questions(& answers), Oracle Apps

Free Dashboards