Data Migration Services

1
We make Web apps, Mobile apps and Data analytics
happen !
2
Data Migration
3
Data Migration ?

• Data migration is the process of transferring
  data. It has to be considered
  before implementing, upgrading or
  consolidating a new system.
• It is usually performed programmatically to
  achieve automated migration, freeing up human
  resources from tedious tasks.
• It occurs for various reasons such as server
  replacements, maintenance or upgrade, application
  migration, website consolidation and data center
  relocation.

4

• To achieve an effective data migration, old
  system data should be mapped to new
  system by properly designing data
  extraction and loading procedures.
• This design relates old data formats to new
  system's formats. Data migration may involve many
  phases including data extraction where data is
  read from old system and data loading where data
  is written into new system.

5
Data migration Steps
6
Analyze field level mapping
Excel File
3.Cleansing evaluation
6.Repeat process till error free data migration
5.Moving data to destination db Verification
process
4.Load data using upload programs
Data Upload Programs
Destination Database
7
Analyze

• Its important to define the scope by discussing
  with key users and
  stakeholders. this is similar as gathering
  requirements but it also includes comparing with
  old systems data and to find out where it is
  stored.
• By proper analysis , it can be defined what needs
  to be migrated and the scope. No need of junk
  data.

8
Design

• Define the mapping and discuss with business
  users. Microsoft Excel is a very good tool to
  define mapping and to link
  requirements.
• Keep the data model as simple as possible in
  every migration. This makes it easier to trace
  errors and to understand the script, but also to
  run an update.
• When you use files, you should use one folder
  with a unique file name. For every migration, you
  can specify a filter for the file name.

9
Extraction

• The first part of the process involves extracting
  the data from source
  system(s).
• In many cases this represents the most important
  aspect of data migration, since extracting data
  sets the stage for success of subsequent
  processes.
• In data extraction database structure has to be
  analyzed with all possible scenarios and its
  mapping relations.

10
Cleansing

• Data cleansing is the process of detecting,
  correcting or removing incomplete,
  incorrect, inaccurate, irrelevant,
  out-of-date, corrupt, redundant, incorrectly
  formatted, duplicate, inconsistent, etc. records
  from a record set, table or database.
• Steps in Data Cleansing

11
Loading

• This phase loads the data into destination that
  may be a simple delimited flat
  file. Depending on requirements of the
  organization, this process varies widely.
• Import data into destination table or database
  with its own mapping relations.

12
Verification

• After loading into new system, results has to be
  verified to determine whether it is
  accurately translated, completed, and supports
  processes in new system.
• During verification, there may be a need for a
  parallel run of both systems to identify areas of
  disparity and forestall erroneous data loss.
• For applications of moderate to high complexity
  are commonly repeated several times before the
  new system is deployed.

13
Difficulty Criteria

• Number and size of databases in Application
• Number of Tables per database
• Total Number of Attributes
• of attributes that have had multiple
  definitions over time
• of attributes in terms of synonyms and antonyms
  
• Number of DB dependent processes
• Number of one time Interfaces
• Number of ongoing Interfaces
• Number of Data Quality problems and issues to fix
• Knowledge/Documentation of Data Quality issues
• Ease of de-duping similar entities in the same DB
• Ease of matching same entity records across
  multiple DBs
• Completeness of the functional documentation

14
Our Successful Implementation

• Environment
• Source PHP, MySQL
• Table count205
• Destination Asp.net MVC, SQL 2014 R2
• SAAS Model Table count(Main db with 29 tables,
  domain db with 42 tables)

Main db
Source DB(MySQL)
SAAS Model
Data Migration Process
Client dbs
15
1. Define the mapping and discuss with business
users. Microsoft Excel is a very good tool to
define mapping and to link requirements.
16

• 2.Extraction of data from MySQL is coded in
  python
• and stored the resultant data in excel.
• 2.1. Data fetched from individual tables based on
  the analysis, that consists of valid and invalid
  data. Remove all inconsistent, duplicate and
  test/dummy data.
• 2.2. Once these data are fetched we restructured
  those data based on the target database tables
  and exported into excel sheet with its mapping
  relations.
• 2.3 Then these data can be moved to cleansing
  process.

17
Python sample code
18

• 3. After completion of extraction, moving for
  cleansing
• process such as
• Detecting, correcting or removing

19

• 4.Once cleansing process was successfully
  completed
• then initiate the loading process.
• 4.1 Programmed a script in SQL to migrate the
  cleansing data into destination or targeted
  tables.
• 4.2 Have to maintain primary key for relational
  purpose for other tables.

20
SQL Sample Script
21

• 5. There may be a need for a parallel run of both
  systems to identify areas of
  disparity and forestall erroneous data loss.
• 6. Check the data flow of an application. Till we
  get error free application this has be repeated.

22
Excel File
Analyzed Mapping Mandatory fields
(Analyzed and design sheet)
3.Cleansing evaluation
6.Repeat the process till error free data
migration
5.Moving data to destination db Verification
process sql process
4.Load data using upload programs SQL script
Data Upload Programs
Destination Database
23
Challenges faced

• 1. Data Migration for SAAS model(1 db to many
  subdb).
• Soln script programming created unique subdomain
  with existing column
  values every client and create a sub domain
  database based on respective tables.
• 2. Understanding database structure from source
  is biggest
• challenges as it might have unused tables.
• 3. Reporting feature has to be redesigned in new
  system.
• Soln With Repeated testing required to
  find exact column name.
• 4. Chances of getting wrong data for wrong column
  as naming
  convention was improper.

24
Contact us

• Data migration projects are not as easy as new
  development and it requires skill set,
  knowledge.
• W2S solutions worked on many migration projects
  and capable of handling projects with massive
  data.
• Let us know how we can help.
• Email id Sales_at_w2ssolutions.com
• Phone No 1 512-375-4345
• Web www.w2ssolutions.com