Articles

In the starting of my career, Every time terms like conversion and migration comes, I used to get confused, what is the difference between migration and conversion(To me both seems to be same) and again it used to increase my confusion when someone says for data conversion/migration we will use Interface/Standard API. To get rid of my confusion I gone through many documents especially about data conversion and migration, and I got opportunities to do all these things and I thought of sharing my experience and knowledge, so that it can help others to understand these terms.

 

When we need to enter data into oracle Apps, following are the few techniques:
• The Data can be entered using the application Screens (for small amount of data, like creating PO, entering sales orders using Oracle Apps screens).
• The data can be entered using Oracle's Open System Interface (for regular operations e.g. for moving data from one module to another).
• The data can be stored in the database table directly (Not recommended by oracle and can be very risky, because on any event data is going to be stored in many tables and data should be validated before inserting into tables that may cause data integrity and inconsistency problem, sometimes it may corrupt the data completely.).
• Using third party tools like data loader (It is also can be used when data is relatively small (25-200 records) because it captures the keystrokes and works like manually entering the data into Oracle form but much faster as process is automated).

What is the need of Migration/Conversion?
Migration/Conversion are required when we are upgrading to one version to another (e.g. Oracle Apps 11.5.7 to Oracle 11.5.10) or moving data from some legacy system to Oracle Apps. There will be bulk  of data (sometimes millions or even more than that) that needs to be moved from one system to another  and  before moving the data it should be validated and only valid records should be entered into Oracle Apps.
If both the systems (Target and source) are not having same structure for data (Tables are not same/Table Structure is not same/The data is being stored in database is not same), it needs to be translated (e.g. upgrading from Oracle 11i to R12 where table structures are not same) then we say it as conversion (any kind of translation of data on Source data to make it suitable for Target system) otherwise migration.

What is Migration?

Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data.
Process of Migrating of data: for Migrating the data we should follow following steps
• Identify the data to be imported to new system (Business requirement).
• Extract the data into flat file/Staging table
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface)
• If using API, fetch the data validate it and then call API to import the data 

What is Conversion?
Conversion of data means translating the data to suite target system (data should be formatted according to target system )  and then move the translated data using Interface Programs/APIs.
• Identify the data to be imported to new system (Business requirement).
• Extract into flat file/Staging table
• Translate/Convert/Format the data
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables
• If using API, fetch the data, validate it and then call API to import the data
Both conversion and Migration are one time process the only difference between Migration and Conversion is, Translation of data in Conversion. Since data structure and data design in legacy systems (Source System) are different from those of Oracle Applications newer version (Target System), data need to be converted satisfying the business rules to suite the system requirement.

What is Data Cleaning in Conversion/Migration?
The task of converting questionable data into reliable, consistent and clean data is data cleaning. During Migration and conversion process we should maintain data quality
There are two types of data Quality:
• Technical Quality
• Business Quality

Technical Quality:
The technical quality of the data encompasses:
1. What is the quality of the referential integrity? In other words, if a file of requisitions Contains a buyer code, the record is considered bad if the buyer code in not the list of valid buyers. If a requisition also contains item numbers, are there any items in the File which are not found in the item master? Are there units of measure not found in the unit of measure table?
2. What is the consistency of the data? This refers to whether the arrangement of the Fields within the data is consistent and whether the data within a given field is of the same data type.
3. For files with self-referencing data, where the child of a particular record appears as a parent in the file, are there any recursive relationships?
For example, in a BOM, is there a part that has itself as a subordinate parent? In other words, if part A contains part B, and part B contains part A, a recursive relationship exists. Files of self referencing data should not have recursive relationships.

Business Quality:
The business quality of the file relates to how well the data in the file adheres to a set of business rules unique to each business. The most difficult part of assessing business quality is gaining a consensus on what those rules should be. For example, a business might decide that a purchase order is considered obsolete if all line items have been received and the last receipt of material is more than two years old, it is to be rejected from the file. A business may decide that sales history for a particular discontinued product line is to be rejected. The business quality rules may also require specific conversions to take place. For example, if the business is changing the chart of accounts in implementing Oracle Applications.

What Is an Interface?
These are programs for connection between Two Systems In Order To Synchronize the Data.
• They can be Manual, Batch or Real-Time.
• Used Repeatedly and Should Therefore Be Designed and Constructed In the Most Efficient Manner Possible.
• These can be triggered by an Event (Such As Running a Concurrent Program) Or It Can Be Scheduled to run at a Certain Time.

What is an API?
They are Application Program Interfaces, which are standard stored procedure, package or functions created for performing specific activities in the Oracle Apps E-Business Suite.
Ex: The Order Import API when called would create orders corresponding to the record in the Order Interface tables.
How conversion/Migration and interface differ?
There are good numbers of parameter on which they can be categorized. Take few of them:
Frequency
• Conversions/Migration are a one time event
• interfaces are ongoing
Occurrence in the project timeline
• conversions/Migration executed before production
• interfaces executed during production
Manner of execution
• Conversions/Migration are batch
• Interfaces may be batch or real time
Complexity
• Conversion/Migration does have very complex, it’s totally depends upon the data mapping activity.
• Coordinating with other systems make interfaces more complex
Maintenance
• Maintenance of interface is bit cost intensive task.

Interface Type:
There are two types of interfaces:
• Inbound
• Outbound

Inbound Interfaces:
An inbound interface receives data from one system (legacy) into Oracle open interface tables.
A typical inbound interface would follow these steps:
• Extract data from legacy system into a flat file.
• Use SQL*Loader or equivalent tool to upload information into a temporary table.
• Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.
• Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

For example Item Interface (Interface table MTL_SYSTEM_ITEMS_INTERFACE, MTL_ITEM_REVISIONS_INTERFACE, MTL_ITEM_CATEGORIES_INTERFACE, MTL_INTERFACE_ERRORS and Interface Program name is “Import Items” short name INCOIN) when we need to create new items or migrate items we can insert the data into interface tables after insertion we can launch Import Items program which will create the item.

Outbound Interfaces:
An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).

What Is An Open Interface Table (OIT)?
For inbound interfaces, the interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
Open Interface Tables are standard Oracle tables.
Oracle uses OITs to provide a simple interface to Oracle base tables.
Oracle Interface Program:
Most Oracle modules have standard import programs (concurrent processes) to facilitate custom inbound interfaces. The specific processing performed varies by application.
These programs pull data from the open interface tables, validate the data, and then insert into one or more Oracle base tables.
Upon successful completion of processing, the program deletes the processed rows from the interface table or marks them as completed.
Depending on the import, errors can be viewed in various ways (exception reports, error tables, forms etc).
Examples of standard import programs:
GL: Journal Import
AP: Payables Open Interface
AR: Customer Interface
INV: Item Import
AR - Auto invoice
Brief Meaning of the terms used for Migration/Conversion
Data Extraction - Taking data out of the source system
Data Cleaning - removing incorrect/invalid data
Data Translation - translating data elements from one value to another
Data Conversion - transformation of the data from the source structure to the                          target structure.
Data Validating - Ensuring the new data is consistent with itself and any existing data in the target system
Data Loading - Putting the new data in to the target system

 

• < Prev
• Next >