Three Tips for Avoiding the Dirty Data Trap

Visit Website View Our Posts

Data Migration is often the bottleneck which busts the budget and shatters the schedule early in any implementation project.  Problems encountered during data migration often force your staff to maintain both the new systems and the legacy system for a period of time.  This parallel processing is inefficient and unnecessary if data migration tasks are effectively automated and easily replicated.   Unfortunately, this is not always the case. Why?

To answer this question we must first define data migration and understand the tasks involved.

Data migration is the process of transferring current and historical data from one or more legacy systems to a new, more efficient and more effective system. The tasks involved typically include data extraction, data cleansing, data mapping, data loading and data validation, hereafter referred to as EXTRACT>CLEAN>MAP>LOAD>VALIDATE.

A typical payroll implementation project will require that the EXTRACT>CLEAN>MAP>LOAD>VALIDATE process be executed multiple times, often 3 to 4 times.  At minimum, there will be two legacy data migration cycles: one during the Build & Validate phase and one during the Deploy phase. The latter occurs after the latest payroll processing cycle in the old system and before the first payroll cycle in the new system. This final data migration cycle is designed to provide a final synchronization of the new payroll system to the legacy system and often must happen within a one or two week window.

Now, back to the question of why this data migration process can be a major project bottleneck and source of budget overruns and schedule extensions.  The answer can be summed in two words – dirty data.  More specifically, the data cleansing tasks often are not automated, or even documented, and therefore not easily repeatable.  Each successive  EXTRACT>CLEAN>MAP>LOAD>VALIDATE cycle wastes time and money re-cleaning data, usually manually, which were cleaned, usually manually, in the previous cycle.  This rework usually means that the final data load cannot be accomplished in the window available between the last payroll cycle in the old system and the first payroll cycle in the new system resulting in the duplication of efforts to maintain, or synchronize, the two systems.

How can you avoid the dirty data trap?

  1. Don’t rely on your implementation partner to clean your legacy data if you don’t have to. You and your expert staff are aware of dirty data in your legacy system before you even decided to implement a new payroll system.  Establish an in-house team of IT and Payroll experts to clean and correct the data in the legacy system before the implementation project begins.
  2. If you do not have the capability or bandwidth to clean the legacy data in-house, tell your implementation partner upfront, during the Initiate or Discovery phase of the project. Be prepared to accept additional cost and time during the early phases of the project for your implementation partner to work with your IT and Payroll staff to develop a programmatic system to clean and map your data. This is the classic example of “pay me now, or pay me later.”  If you don’t spend the time and money up-front, you will spend it on the back-end in the form of rework, budget overruns, and schedule slippages. The back-end costs are even higher because they include project team frustration and loss of project momentum.  If you go live with dirty data you risk alienating employees and possibly even incur additional financial liability.
  3. Be prepared to allocate sufficient staff resources to ensure a comprehensive validation of the migrated data. Remember, the accuracy of your data is always your responsibility.  Take the data migration task seriously and don’t assume it is a minor effort.

In conclusion, your implementation partner should be able to guide you through these important project tasks and help you avoid the dirty data trap. At RSM McGladrey, we offer a proven implementation methodology for business systems and a track record of successful payroll project implementations.  Our consultants are Microsoft Certified Business Management Solution Specialists for Microsoft Dynamics GP Payroll and have experience developing and deploying automated data migration techniques.

By:  David Funk, RSM McGladrey – Microsoft Dynamics Certified Payroll Implementation Partner

Look for future blogs from David Funk on the following Payroll Implementation topics:

  • Prepare your staff
  • Standardize pay policies
  • Develop a realistic schedule
  • Synchronize integrated systems

3 thoughts on “Three Tips for Avoiding the Dirty Data Trap”

  1. payroll software

    The latter occurs after the latest payroll processing cycle in the old system and before the first payroll cycle in the new system. This final data migration cycle is designed to provide a final synchronization of the new payroll system to the legacy system and often must happen within a one or two week window.

  2. Martin,
    Thanks for your comments. I looked at you company's web site and I agree that your progressive migration method is a good strategy for some projects. I think that we often do employ this method informally. For example, in a payroll project we will sometimes migrate historical (1-3 years of prior tax year data) after we have migrated the current tax year data and after the client has gone live with the new system. We also believe in your concept of early data migration to "demonstrate it in a near-production environment." We call it rapid prototyping. What I find to be the most challenging is effectively and efficiently executing the iterative cycles of EXTRACT>CLEAN>MAP>LOAD>VALIDATE that are required in a small window for a payroll project. I believe you may have some good ideas and tools to help us improve this process.

  3. David,

    I think that your tips are very valid and although often difficult to do and sidelined, are important for the successful completion of a migration project. The closer the migration can be to the business requirement, the owners of data and service, the more likely it will succeed. There is one additional tip that I would add. Use of a progressive migration method that allows the data to migrated by business requirement foremost rather than technical need. In this way it is possible to migrate the application data that business requires early in the project and demonstrate it in a near-production environment with the huge benefit of gaining the confidence and buy in of the business customer. A progressive approach allows for change in subsequent iterations of target or transformation imperatives not to mention cleansing of the data in a progressive manner throughout the project rather then relying on early analysis (which is good) to necessarily be fit for purpose on the traditional "day of migration". By migrating data and keeping successfully migrated data in synchronisation between sources and targets you work on an iterative provision of data and increasing confidence in the quality and usability of that data in the target.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons