Data and History Load: How To Ensure Data Quality

Let's discuss some best practices that can help ensure a successful data migration and history load.

Table of Content

    In our data and history load post: Data And History Load: How Much Is Enough we defined data migration and history loading in context with an ERP implementation.

    To recap, data migration and history loading is the process of gathering data from your current systems, then editing and correcting the data as required. Once the data has been edited and quality tested, it needs to be formatted allowing the data to be input or imported into the new ERP.

    In this post, we discuss some best practices that can help ensure a successful data migration and history load. The post also identifies some “pitfalls” which if not mitigated, often result in data migration issues.

    Best Practices

    Over the course of dozens of ERP implementations, five top best practices have emerged:

    Business Leaders Guide to Dynamics 365Business Leaders Guide to Dynamics 365
    • Visible Executive ownership
    • Quantify your data sources and stay within your capabilities
    • Be open to other options
    • Leave a trail if you can
    • Be sure everything is correct before go-live

    Visible Executive Ownership

    Visible executive ownership is the number one success driver across all ERP implementation phases. Executive involvement is a key component of data migration and history loading. Review the plans with executive team members. Find out what data they think is important.

    See if they can live with using other data resources for prior period information, or if having all data in the new ERP is required. Be sure though, that they understand there is a genuine cost to this decision in both time and dollars.

    To learn more about executive ownership, look at Preparing for ERP Implementation: Getting Started on the right Foot.

    Quantify Your Data Sources and Stay Within Your Capabilities

    Data conversion and history load requirements start with the best of intentions. A lot of times though, as the project team takes a closer look, issues arise.

    All data comes from either current system(s), databases, or user-maintained documents (spreadsheets). Looking at these sources and quantifying their ability to easily and accurately provide the required data is an important first step. If the system can’t download the information into a file or database, or if the download information’s quality is suspect, a decision needs to be made whether to include the data in the new system.

    Regardless of the data source and loading method, the most important decision is to be sure you stay within your company’s ability to handle data effectively and load only data that’s truly needed.

    Remember, any data loaded into the new system must be manually entered or downloaded into a file, scrubbed, converted and formatted properly for loading. After the loading process the data needs to be reconciled, corrected as needed and finalized.

    Don’t take the above tasks lightly. Data volume can easily climb to thousands of records quickly. Data migration can be very time-consuming and will overwhelm your staff if not planned with a keen eye towards the project team’s capabilities. Unless you want to spend a lot of money having the VAR or outside help assist in this process, keep it as simple as possible.

    Be Open to Other Options

    There are options to traditional data migration and loading methods. Depending on the amount of data being loaded manual input may be a better option. It’s a lot cheaper to hire a low-level temp to enter data than to pay higher priced developers to code or modify import routines for anything other than a large volume of records. A good example of this would be the vendor set up of less than 250 records.

    Keeping the current system running for a period of time and referring to it as required after the cutover is another excellent choice.

    Running the applicable reports from the current system and referring to them when needed may be an effective alternative as well. Depending on your current systems, you may be able to capture the reporting electronically which positively impacts future research considerably.

    There are some more creative ways to manage the amount of data being loaded into the new system. Let’s look at open (unpaid) AP invoices as an example.

    If you’re loading period end GL Trial Balances into the new system you’ve already included the effect of any vouchering activity as it is posted to the GL when the invoice is entered and posted in Accounts Payable. That means you can cut the applicable checks from the current system (post cutover) and enter the payments into the new system via a journal entry. This process can be completed manually or via file import. The required entry is quite simple as Accounts Payable and Cash are usually posted (Debit AP / Credit Cash) per each check processed.

    The Right Microsoft Partner Can Drive Business SuccessThe Right Microsoft Partner Can Drive Business Success

    If you were to use this process, you might be concerned with future duplicate payment controls. The same duplicate payment test issue may emerge whether you pay from the current system or load unpaid Accounts Payable invoices. Even though loading unpaid invoices will track the post cutover payments made in the new ERP, anything paid before that time would not be captured. So, consider the volume of unpaid invoices and make the decision that best suits your needs.

    The take home here is to keep an open mind to suggested alternatives as they can save a lot of time and money. Your VAR has been doing this for a lot longer than you have and can come up with some viable alternatives worth considering.

    Leave a Trail if You Can

    Some data will need to be re-formatted for loading into the new system. Data changes may also be made to correct past mistakes. For example, the Chart of Accounts may need to be re-organized to increase reporting efficiency, or vendor and customer codes scrubbed and re-structured to get rid of duplicates and errors.

    If possible, try to leave a trail linking the old and new data to assist in new system transaction processing.

    This isn’t as hard as it sounds. Most systems today can easily add additional fields to a specific record. They are sometimes called “user fields” and are intended to capture specific user defined data. Use a field to capture the original information for cross reference purposes.

    You can also add data needed in a record’s description. For example, an inventory item’s original item code. Again, ask the VAR for recommendations on how best to do this.

    Don’t expect more out of user fields than they are designed to provide. The fields usually capture static data only and do not have additional functionality attached to their contents without modification.

    Be Sure Everything is Correct Before Go-Live

    Once the data has been reviewed, scrubbed, re-formatted and tested, its time to load the data into the ERP. Depending on the data being loaded, the process and sequence will differ.

    For example, if you’re going to load GL balances for the prior year(s) and GL transactions for the current year to date, you’ll need to load the balances first, close out the prior year’s periods and year, then load the transactions in the current year. The VAR will be able to explain the proper sequence.

    Some prior period data can be loaded after the cutover if required. You can load data such as GL balances, budgets or forecasts for pre-cutover periods after the cutover. Just remember, each time you load data you’ll need to re-run all of the financial reports to date to account for the new data loaded.

    You can’t be too careful here. If you’re looking for accurate data and reporting from the new ERP this process is critical.

    Be sure to plan data loading allowing enough time to complete the reconciliation. Break up the data load into manageable parts. For example, don’t try to load all of the data a couple of days before the cutover. Load the bulk of it well before the cutover to keep the pre-cutover data load to a minimum. A good rule of thumb here is to only have to load a week or two worth of transactions prior to the cutover.

    Business Leader's Guide to RansomwareBusiness Leader's Guide to Ransomware

    Remember, the data is loaded as a system transaction and will need to be posted. Once posted in the new system the data cannot easily be revised without sacrificing control and accuracy, so be sure it reconciles prior to posting.


    Data migration and history loading are an important part of an ERP implementation. In my mind’s eye, if you’re willing to take the time and energy required to complete the tasks involved, then the data must have value to your company, so do it right the first time and avoid future headaches.

    There is nothing quite as embarrassing to an Accountant as distributing inaccurate reports. You do that once or twice and your credibility is gone forever, maybe your job too.

    Business Leaders Guide to the New Digital AgeBusiness Leaders Guide to the New Digital Age