ERP Data Migration – A Primer

The riskiest part of a project comes in one of two areas, the integration with other systems, (especially where you have near-real time transactions) and most critically, the ERP data migration.  You can build out your processes in the system correctly, you can ensure that all the reports are spot on and you can properly train your users.  However, if your ERP Data migration does not execute flawlessly, then you are going to have unhappy users and a failed implementation.  Since this is a critical part of any implementation, let’s look at some key data concepts and areas to watch for to ensure that you have a good understanding of the migration process.

The key concept you first need to understand are the two types of data that you and your team will be dealing with.  First, there is the Master Data records.  This is the data that infrequently changes and is the core of the system records.  Records such as Companies (Vendors and Customers), Contacts, Inventory Items, Bills of Materials, and GL Accounts are all Master Data.  All other Transactional Data relies on the Master Data.

The second type of records is the Transactional Data. Orders, Purchases, Work Orders and the like are your Transactional Data. These are your day-to-day transactions. As mentioned, these utilize (or link to) the Master Data to create a complete record.  It should be obvious that you want to ensure that the Master Data is clean and loaded first before you load the Transactional Data.

Build a Data Plan

The key to success is proper planning.  Before you embark on a data migration it is important that you catalog all of your data sources that you will migrate.  These should be part of a comprehensive data plan document.  Key things that should be included in your Data Plan are:

1. Data Inventory
   1. Source Tables
   2. Target Tables
   3. Record Counts
2. Data Mappings
3. The data migration tool(s) to be used
4. Any data consolidations
5. Transformations (moving the data record fields from one format to another to accommodate the new system)
6. Data cleanup activities
7. Testing Plan
8. Cutover Plan

The Data Migration Process

1. Profile the source system to identify the data relationships, including the current state of data quality and any discrepancies that exist between the source and target data model.
2. Perform a data mapping exercise with the owner of the data. The output of this exercise will be a data mapping workbook that contains granular field mappings and transformation/normalization rules.  It should also include the sequence of data table loads due to the dependencies of the data relationships.
3. Extract a sample dataset from the source system into a sql database to be manipulated prior to loading.  A flat file CSV format can be used, but then the file needs to be cleaned manually using a spreadsheet software.
4. Use an Extract, Transform, Load (ETL) Tool and a sql server staging database to perform the transformations, clean up and load of the sample data into a test environment.  An iterative approach should be used to fine-tune the rules and process.
5. Validate sample data in the test environment using the following methods:
   1. Mapping validation
   2. Match reports and record count validation
   3. Correct any discrepancies identified in the validation process  
6. Perform a User Acceptance Test (UAT) on the data in a test environment and receive a sign-off from the business owners
7. Extract a “delta” or net difference extract from source to collect recently transacted data
8. Migrate data with modified logic into the target system
9. Validate that the final data migration was successful
10. Execute a data quality plan and de-duplication in production instance
11. Go Live
12. Update your documentation with any last-minute changes

Working through your ERP Data Migration

When planning your ERP Data migration, some things you should consider:

How much data do you need to bring over?  Typically, you will bring all of your master data, but maybe only two years of transactional data.  This could vary on a table-by-table basis.  For example, you may want to bring over five years of customer orders, but only two years of purchase order data.

1. Can you archive your older data into a database that can still be accessible if needed, but not needed in the ERP?
2. Will the old system be available or is it completely going away?  If the old system is still available, then perhaps you do not need as much transactional data migrated.  The old system could be available in a read-only mode.  If you are migrating from a licensed system and you are shutting off the licenses, then moving the legacy data to an SQL database or data warehouse is probably the better solution.
3. In your data cleanup, you may want to enhance the data using third party sources, such as Dun & Bradstreet’s company information.  This should be additive and not replace your core data.

In summary, it is critical that you have a really well-thought-out plan that addresses not only the migration but also built-in contingencies.  Is there a rollback plan?  Then, once you are in the migration, be sure to run a significant amount of tests on the data to ensure it is valid data, it is properly mapped to the correct target fields, and that the sequencing of the data is carefully considered.  Once tested, make sure you have a go-live validation to ensure that even the well-tested data came into your production environment correctly.  This should happen prior to the users being allowed into the production system.

If all goes well, the users will notice only surface level things such as the new systems user interface.  The underlying data should feel very much the same to them.  If you have any other tips or suggestions on making an ERP data migration run smoother, please comment below.