Steps in a Simplified Approach to Data Migration
This Page describes a series of Steps in a Simplified Approach to Data Migration
between Source Systems and Target Systems.
It covers the basics of Extracting data from a number of Sources, Transforming it
and Loading it into a Target Database.
- Preparation ...
The only Deliverable from this Simpified Approach is a Word document Table or a Spreadsheet which
defines the Mappings between Source and Target Data Items.
You will start by defining an appropriate Document or Spreadsheet with these Columns :-
- Source Table (or Filename)
- Source Column (or Fieldname)
- Source Data Type
- Validation Rules.
- Typical Values.
- Transformation Rules.
- Target Table
- Target Column
- Target Data Type.
Identify all the required Data Sources, and an 'owner' for each Source.
(Data Feeds, Legacy Systems & Operational Data Stores).
If you want to migrate Access Databases to Oracle,MySQL, Sybase or PostgreSQL,
then check out MDB Tools,
from Brian Burns.
Define the Data Items required, in consultation with the Users.
- Setting-up the Simplified Data Migration Process ...
- Optionally define and create the Data Dictionary.
Define Data Validation Checks
(bottom-up, meaning identify available low-level data, such as Invoice Dates,Amounts,etc. ).
Define Validation Rules, (eg Start Date earlier then End Date).
Define Clean-Up Business Rules for Source Data, (eg Set default End Date).
- Optionally create the Data Models for the Source Data.
Define the Staging Area, with 'Source' Tables to store Extract Files,
and 'Target' Tables to store data after transformation.
- Optionally carry out an Audit of the Data Quality in major Databases, (bottom-up and top-down).
- Optionally evaluate the benefits of a Data Cleansing Product.
I prefer to have Target Tables that exactly reflect the final Database so that the data can be loaded in directly,
in the knowledge that there is no chance of errors in the final load.
Define the Data Mapping between Source and Target Data Items.
Define Acceptance Tests for data in the Integrated Database.
- Optionally create the Business Data Model for the Consolidated Database
Executing the Data Migration Process.
- Either use SQL or choose a Data Migration Tool.
- Create Extract Files for each Data Source in turn.
- Carry out Validation Checks against Extract Files.
- Resolve any Errors with the Users.
- Create Extract and perform Data Clean-Up.
- Complete the Migration, (Extract, Transform and Load) for each Data Source.
- Run Acceptance Tests on the Integrated Target Database,(esp. Referential Integrity).