Data Migration Approach: Data Mapping, Extraction & Transformation – 2
SQL server database for running the validation rules:
When loading source data into SQL Server is to have Staging tables to run various scripts. ie to check for integers in Varchar columns / text in Integer column etc…, check the maximum entry in a column to avoid truncation errors. Then we can factor in Data Type lengths and data Types themselves making sure we are covering all errors with dataloads.
We should all sit together with a few examples so we get all the processes in the right order and that we understand them.
Cut Down files
We need to know what tables & fields needs to be loaded in Horizon and NAV for intended functionality to work.
o Referring to the “NAV data migration templates”, we know what tables needs to be loaded for most of the business objects, but not for Open AR/AP Invoices, etc.,
We need inputs from SI indicating the tables that needs to be loaded for Open transactions.
o We have data dictionary, but we don’t know what tables needs to be populated for each business object
E.g. if we need to load more than one table and related data dependencies
o We need inputs from Horizon indicating the tables that needs to be loaded for each business object
Then we can finalize the mapping templates and identify the missing fields/gaps
• Identifying Gaps / Missing Columns
o Once we have the above information, we will be in a better position to ask client to include the missing columns/info.
There needs to be data migration strategy document detailing the approach of how the data will be staged and the transformations/validations that needs to be applied and run before data is loaded into Horizon & NAV.
In summary, we need the following process documents in place:
1. Data Migration Requirements & Strategy document covering –
a. Requirements Summary of all business objects
b. Data migration approach – Source files, Staging, Transformations/Validations, Loading
c. Reconciliation processes
2. Mapping Functional Design Document – one for each business object
3. Mapping Template – one for each business object for each Target
And the Mapping functional design document & Mapping template needs to be created for each business object by respective owner, thus will be useful if an object is assigned to other resource in future, as a reference to start with.
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
||Setup Data objects
||GL Account / COA
||Transaction Data objects
||Open AR Invoices
||Open AP Invoices
||Purchase Order Header
||Purchase Order Line
||Purchase Receipt Header
||Purchase Receipt Line