Data Migration Approach: Data Mapping, Extraction & Transformation – 1

Data Migration Approach: Data Mapping, Extraction & Transformation – 1

I have described below the data migration approach for transforming/migrating data for each business/data object to migrate the data between Oracle R12, Horizon Property System and Microsoft Dynamics NAV. This is one of the approach and there are many other methods available based on the project scope, duration and tools available.

1. Create an excel templates between source and target depending on what data is being migrated to target system (i.e. Oracle R12, Horizon, NAV)
a. Indicate the data types in source and target fields
2. Extract subset of data (say, 100 records) with necessary fields as per the above template from Source system (i.e Oracle BI or Oracle R12)
a. Oracle BI – URL
b. Login to Oracle BI
c. Dashboards ==> Catalogue
d. Select a report, provide parameters and run
3. Identify the gaps (missing fields) that are not present in the existing reports when compared to the data migration template created.
4. Create a new analysis in Oracle BI by selecting the subject area and selecting the necessary columns (by applying a filter to extract subset of data); otherwise we will end up pulling large dataset.
a. Login to Oracle BI =>  New => Analysis => Select Subject area
b. Drag necessary columns into Selected Columns area
c. Apply filters based on date in Filters area
d. Click Results tab
e. Export the data to Excel format (there is an export option on the menu bar)
5. Create the table in SQL Server
6. Develop SSIS transformation routines (Dataflow & Workflows – Embed Validation Rules)
7. Run the SSIS routines to load the data into SQL Server tables
8. Export the data into an excel format for Horizon & NAV for Business Validation
9. Repeat steps 1-8 based on inputs from Business & other SI partners involved

I have categorized the business objects into two sets as follows. Setup data is a pre-requisite to be loaded before transaction data is migrated. So, we will be working on setup data templates first, which will solve the data dependencies on transactional data.
1. Setup Data
2. Transaction Data

No Setup Data objects Area
1 Customer Receivables
2 Sales Person
3 Job
4 Location
5 Receivable Activities
6 Payment Methods Payables
7 Item Purchasing
8 Vendor
9 Bank Account Cash Management
10 GL Account / COA General Ledger
No Transaction Data objects Area
1 Cust Ledger Receivables
2 Open AR Invoices
3 Open AP Invoices Payables
4 Paid Invoices
5 Item Ledger Purchasing
6 Vendor Ledger
7 Purchase Order Header
8 Purchase Order Line
9 Purchase Receipt Header
10 Purchase Receipt Line
11 Fixed Asset Fixed Assets
12 General Journal General Ledger


Leave a Reply

Your email address will not be published. Required fields are marked *