Data, data, where art thou data?

If you speak with any seasoned ERP implementation expert, they will probably cite ‘data’ as being one of the keys to the success of an ERP programme. Often underestimated and never normally correctly budgeted, data migration can be a lengthy and sometimes complex task. If you ask an executive team “What needs to be migrated?” they will often answer “Everything”. Whilst this is possible, it would probably cost more and take more time than the entire ERP project.

There are many pitfalls in data migration, but the underlying starting point is to be realistic. Migration always runs alongside the main ERP project, with key touch points throughout. Do not leave it until the last minute.

Data Migration Strategy

All migration phases need to start with some form of strategy document. This sets out what will happen during the phase, the process that will be followed and importantly the scope of what is being migrated. The document should be reviewed and signed off by the executives of the project. This ensures that all the stakeholders know the requirements and scope of the migration exercise, their obligations during the migration phase and how the project will conduct extract, transformation, cleansing and loading. The strategy also explains how the data will be validated after the load.

During the planning/strategy phase, the continual assessment of cost/benefit/requirement needs to be assessed. One common requirement or request is for sales history to be migrated. To migrate into posted ledger tables and mark transactions as fully paid, with all the reporting requirements of the new system catered for, is a very lengthy and costly process that generally would never make a return on investment. Far more productive is to push the legacy data into a separate referenced SQL database or cube for cross-analysis with the new system or to simply leave the data where it is for reference. Converting any form of historical data should be considered carefully with regards to benefit, timescales with the impact on the project, and cost.

The Process

The methods and processes for extracting, transforming and importing vary between systems, but one common factor is it will typically be executed by technical teams. It should be remembered that these teams are focused generally on the migration process. The focus is on getting data from one field into another, not that the result meets processing requirements.

Extract – Hopefully self-explanatory, this is the process of removing data from the legacy system. This might be via reporting, extract functionality built into the legacy toolkit or direct database table extracts.

Transformation – Once the data has been extracted the next issue is the content itself. There are generally several issues with the data:

• The structure of the tables will be different
• Concepts in one system not being present in another
• It will be of poor quality, (a common example of this is address records stored in separate text fields with different county references and no way of mapping these to different address format)
• The new system can be used in a manner that the existing data is not set up to handle.

Transformation is the process of changing/adapting/augmenting so that it meets the requirement of the new ERP system. It might add different settings and categories that do not exist in the current system, might involve merging sets together or even two legacy system sets together. Every data type and combination of legacy system will be different. The processes and mappings should be documented, both for reference and repeatability.

Loading – The ‘How the data will be loaded’ question is generally assessed on a volume basis. If there are 100,000 item records then they will be migrated via an automated process, if there are 10 they will be manually keyed (be aware of human error). Between these levels of volume assessments, there is a grey line where a decision is made; and this line moves in volume depending upon the area of the records being migrated and the complexity. Be sure to include any loading tools within your ERP budget.

Testing

Once data exists and is present in a test system the end users responsible must test this thoroughly. Responsibility for the acceptance of the migration in the end system resides with the end users and not the technical teams responsible for the migration. This means to ensure the best possible results from the migration project the routines must be run in full in advance of the live date and the end users must test, test and then test again.

The final data routines should be finalised months in advance of a go live. Whilst initially testing will be undertaken on subsets of data it is imperative prior to live that at least one full import run is undertaken to ensure all the possible issues from a full import are understood and potentially resolved in advance of the live date. It is also important to capture the execution timings. This will form a vital part of the cutover and will allow accurate estimation on the duration/any downtime required.

Data drives your business. Invest the time and resources necessary to get it right.

Data Migration/Cleansing Systems

During the migration phase, there will be many iterations of build, testing, cleansing and retest. It is therefore advisable to have a data migration ERP system “off track” and away from the other key project activities. We do not want migration tasks to interfere with user acceptance testing, or for one task to be holding up the other.

Data Cleansing

Ask any veteran of an ERP implementation to cite critical success factors and you will always hear “data cleansing” near the top of the list. It also is the most underestimated area of a programme – both in terms of scope and complexity.

There are three primary reasons that people underestimate the task of data cleansing:

• Master data is usually dispersed among multiple systems and people rarely appreciate the volume
• Data cleansing ends up being an iterative process, which creates a bit of a Catch-22 – you cannot test without data, and the reason for the test is to evaluate the data
• It is extremely difficult to objectively measure progress on data cleansing, particularly considering the point above. Data you might have considered cleansed and complete does not test well and suddenly becomes “uncleansed”.

There are lots of steps you can take to improve the data cleansing process:

• Make progress where you can. Some data will take time to accumulate but recognise basic issues e.g. “South Main Street”, “South Main St.” and “S. Main Street” and “S. Main St.” and work on fixing those
• As early as possible, try to extract the field requirements from your new ERP system and analyse your existing data. Start with the simple and obvious and then move onto the more challenging area
• Create data or systems owners and make them accountable for their success. Gamify the process. Try to create some form of incentive to complete the task as quickly as possible.
• Schedule routine data cleansing review meetings and be relentless about probing for bottlenecks and problem areas
• Establish an aggressive completion plan. You may not hit it, but review progress and understand the blockers early in the process
• Assign the right people to the task. Smart, hard-working, detail-oriented people – match the right skills with the job need

Interfaces

Do not forget any of your legacy systems that are passing data into your new ERP product will also be subject to data cleansing. If the data is substandard, it is likely it will fail during the transfer. Taking this a step further, once the data is cleansed in the upstream system it will be important to prevent further deviation in future. Consider adding validation rules to any upstream systems. This will stop the slippage and prevent any future issues.

Unlike most of an ERP implementation, the lessons learned, and the processes created for data cleansing become part of an organisation’s DNA; complete and accurate data will be as essential for success years after go live as on cutover.

Data Checklist

• Have you created a migration strategy/plan detailing your intention for the organisation?
• Have you started migration work early in the programme?
• Do you teams understand the process of migration?
• Have you allocated resource to test and validate?
• Have you invoked a cleansing exercise to ensure your ERP system operates correctly?
• Have you considered and impact of data in your upstream/downstream systems?

This chapter was taken from our publication ‘8 steps to a successful ERP’.

Click here to download the full 32-page report on all aspects of project success.