ERP Information Immigration

PostedOn: 2016-12-05 12:11:49

The riskiest part of a development comes in one of two areas, the incorporation with other systems, (particularly where you have near-real time transactions) and most gravely, the ERP in sequence relocation.  You can build out your processes in the system correctly, you can ensure that all the reports are spot on and you can appropriately train your users.  However, if your ERP Data migration does not implement perfectly, 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 concepts you first need to be familiar with are the two types of data that you and your team will be commerce with.  First, there are the Master Data records.  This is the data that unusually 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 whole record.  It should be understandable that you want to ensure that the Master Data is clean and weighed down first before you load the Transactional Data.

Put together a Data Plan

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

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

The Data Immigration Development

  • Profile the source system to recognize the data relations, as well as the current state of data superiority and any discrepancies that exist between the source and objective data model.
  • Perform a data mapping keep fit 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.
  • Take out 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 spreadsheet software.
  • Use an Extract, Transform, Load (ETL) Tool and a sql server performance 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.
  • 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  

  • Perform a User Acceptance Test (UAT) on the data in a test environment and receive a sign-off from the business owners
  • Extract a “delta” or net difference extract from source to collect recently transacted data
  • Migrate data with modified logic into the target system
  • Validate that the final data migration was successful
  • Execute a data quality plan and de-duplication in production instance
  • Go Live
  • Update your documentation with any last-minute changes      

Working through your ERP information immigration

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

How much data do you need to bring over?  Characteristically, you will bring all of your master data, but possibly 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 contingency.  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 construction environment correctly.