4 i A p p s S o l u ti o n s

I N D I A | U A E | O M A N | Q ATA R | U S A

Interfaces & Data

Migration

4 i a p p s . co m
Data Migration - Meaning

 Data migration is a set/series of activities that moves data from one or more legacy systems to a new
application

 Data Migration is the process of transferring data from one system to another while changing the database
or application.

 Data migration can be in level of :

– Application

– Data

4iapps.com
Data Migration(continued…)

 Typically data migration occurs during an upgrade of existing system or transfer to a

completely new system

 Examples include: upgrading a database or migrating to new software; or company-

mergers when the parallel systems in the two companies need to be merged into one

4iapps.com
Why migrate your data

 Data migrations generally result from the introduction of a new system. This may
involve an application migration or consolidation in which one or more legacy systems
are replaced or the deployment of an additional system that will sit alongside the
existing applications.
 Accurate data is the raw material that maximizes the value of enterprise applications.
 However, when existing data is migrated to a new target application, it can become
apparent that it contains inaccuracies, unknowns, and redundant and duplicate
material. And although the data in the source system may be perfectly adequate for
its current use, it may be wholly inadequate, in terms of content and structure, for the
objectives of the target system.

4iapps.com
Why migrate your data

 Data migration is usually performed programmatically to achieve an automated

migration, freeing up human resources from tedious tasks

 It is required when organizations or individuals change computer systems or upgrade

to new systems, or when systems merge (such as when the organizations that use
them undergo a merger/takeover)

4iapps.com
Frequently used terms in Data Migration

 Cutoff Date:

The day in which the customer/client will switch over and adopt Oracle applications.
From this date the Legacy system operation will be dropped for official reporting
 Set Up Data:

The information that is Mandatory to use the Oracle Applications to be entered for
the setup of the systems (e.g. Flex Fields, profile options, operating units etc.)
 Master Data (Static Data):

Essential reference information needed to perform transactions. The system cannot

operate without as they are referenced by transactions (e.g., suppliers, customers,
accounts, Items, Fixed Assets);

4iapps.com
Frequently used terms in Data Migration

 Transactional Data:

The data changes or actions against the Master Data for the preparation of Business
Documents such as Invoices, Journal Entries etc. The transactional data can be of the
following for Go Live:
 Open Items:

Transactions that are included in the current period Trial Balance either as unpaid items,
Receivables, Accrued, Provisioned for future.
 History:

Transactions or balances that exist in the prior period/closed periods Trial Balances which
forms part of the current period Opening Balances.

4iapps.com
Data Integration

 Data integration involves combining data from several disparate sources, which are
stored using various technologies and provide a unified view of the data

 Data integration becomes increasingly important in cases of merging systems of two

companies or consolidating applications within one company to provide a unified view
of the company's data assets

4iapps.com
Interface

 In computing an interface is a shared boundary across which two separate

components of a computer system exchange information
 The exchange can be between software computer hardware, peripheral devices,
humans and combinations of these

 In terms of oracle applications interface is a communication channel that allows the

data to move in and out of the system
 a point where two systems, subjects, organizations, etc. meet and interact

 a device or program enabling a user to communicate with a computer

4iapps.com
Interface Types

 INBOUND INTERFACES

 OUTBOUND INTEFACES

Inbound Interface: The one which allows data to get into oracle application from
outside is called inbound interface.

OutBound Interface: The one which allows data to get data from oracle applications to
other systems is called outbound interface.

4iapps.com
Types of Inbound Interfaces

 Open interfaces

 API's(Application Program Interface)

 EDI(Electronic Data interchange)--Mainly used for automation transactions with third

party systems
 WEBADI--Used for uploading data from excel with the scope to have some
validations--mainly used for one time loading.Just like sqlloader+validation
program..
 PLSQL Packages for Reading XML Data--Use this in case of importing non standard
transactions

4iapps.com
Types of Outbound Interfaces

 Custom programs where we pull data to a csv file using UTL_FILE in the required
format
 EDI

 PLSQL Packages for generating XML

4iapps.com
What is ETL?

ETL is Extract  Transform  Load

 Extract – Get the data from the source system as efficiently as possible

 Transform – Perform operations/calculations on data

 Load – Load the data into the target system

4iapps.com
ETL Tool

Source A ETL Target B

Files, Databases Read, Apply Logic Files, Databases

& Write

ETL is a bridge for bi-directional flow

4iapps.com
Tools used for Oracle Migration

Tools commonly used in Oracle for Data Migration

 Oracle SQL*Loader

 Excel Data Loader

 Web Application Desktop Integrator

4iapps.com
Interface Programs

 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
 Once the interface tables are populated, the respective interface program (each data
element interface has a specific interface program to run) is submitted. The interface
programs validate the data, derive and assign the default values and ultimately populate the
production base tables
 Translation scripts: 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.
 Interface Programs: 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.

4iapps.com
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.

4iapps.com
Migration Steps

4iapps.com
Steps involved in Data Migration

 Data extraction of Legacy information.

 Data cleansing.

 Data Mapping

 Load preparation.

 Load (Manual/Automated)

 Validate and verify.

 Report

 Reconcile.

4iapps.com
Business Object Template Creation Process

 The template creation process is a pre-requisite to be completed prior to Legacy

Data Extraction Design process. This shall help business to understand what data is
to be provided as part of data conversion process. Templates shall be created for
each business object that needs to be converted.
 All templates for data migration will be circulated to the Client Project Manager by
the implementation team
 The general practice is, Client Project Manager would coordinate with IT and
business users for getting the required information

4iapps.com
Steps involved in the template creation process

 Develop the Initial Template to be given to Legacy

 Refine the Template based on inputs from users

 Details on Scrubbing/Data Transformation Process for the Business Object based on

common lessons learnt
 List critical assumptions for all Business Objects

 List all dependencies for all Business Objects

 List all Data Accuracy and Integrity Measurement Metrics.

4iapps.com
Legacy Data Extraction Process

 After obtaining the Data Templates from the Consultants, the Client IT team should
decide on how and which data will be extracted based on the availability.
 It is always suggested that Master Data to be made available first which is the key
to prepare transactional data.
 Internally the Client IT team should have a plan which should not affect the business
users during the extraction as well as the Project Timelines.

4iapps.com
Data Clean-up process

 Profile the current condition of your data

 Intelligently identify elements in free-form data such as first and last names,
business names, address lines, cities, serial numbers, dates, part numbers and
other data "shapes"
 Recode data patterns for consistency

 Enhance and fix missing data in business objects

 Eliminate duplicates in large databases

4iapps.com
Data Mapping

 The data mapping process provides detailed lists of the data sets and data elements
that will need to be moved into the Oracle 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. 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.

4iapps.com
Flat 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.
 One of the outputs of conversion data mapping is to identify the legacy data element
data types and precision. After the conversion data mapping is complete, 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.

4iapps.com
Download Programs

 These programs are used to extract the identified conversion data elements
from the current systems in the form of a flat file
 The structure of the flat file must match the structure of the Oracle standard
interface tables
 The tool that is used to accomplish this task is usually dependent on the
abilities and training of the current system programmers, or on the availability
of legacy reports to supply this data in electronic form.
 It is important to remember how the flat file will be structured (the order of the
records as they are pulled); type of delimitation used, number of records, etc.
 The output from a download program is a flat file

4iapps.com
Upload Program

 Once data has been put into a flat file, it is then moved to the target file system
and the data from the file is loaded into user defined staging tables in the target
database using SQL Loader or UTL_FILE utilities.
 Then programs are written and run which validate the data in the staging tables
and insert the same into the Oracle provided standard Interface tables. Usually a
single loader program is written for each data table being loaded
 Since transaction data is required the migrated data will make use of open
interface tables & import programs associated with those tables where
appropriate to do so. Depending on volumes loading through the forms will also
be investigated.
4iapps.com
Queries

28 4iapps.com
 Thank you
sales@4iapps.com

INDIA UAE OMAN QATAR USA

1-1-1 Tecci Park PO Box No: 42693 Knowledge Oasis Muscat Al Sadd Area, Doha 18210 Contour Rd #202
285, Rajiv Gandhi Salai Hamriyah Free Zone – Sharjah Sultanate of Oman Qatar Montgomery Village MD 20877
Sholinganallur United Arab Emirates
Chennai - 600 119

Tel +91 44 6638 0000 Tel +971 6526 9057 Tel +968 9780 7119 Tel +974 4432 6634 Tel +1 240 252 5913

29 4iapps.com