Data And History Load: How Much Is Enough?

Data migration and history loading is the process of gathering data from your current systems and then editing and correcting the data as required.

Table of Content

    This post is the sixth of our ERP implementation series. If you want to start from the beginning, or learn more about other ERP implementation phases, look at:

    At this point, your ERP implementation should be far along. System requirements, Fit/Gap and the overall system design should be close to completion. If you have not started on these tasks, the example below illustrates a requirements and fit/gap worksheet.

    The requirements and Fit/Gap process must be completed prior to the system design being created.

    requirements and Fit/Gap process

    What is Data Migration and History Loading?

    Data migration and history loading is the process of gathering data from your current systems and 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.

    Data migration and history loading is not an uncomplicated process and takes a lot of time and effort to be done correctly.

    For example, a lot of new ERP implementations come about due to financial reporting issues. Often, financial reporting difficulties can be traced to a Chart of Accounts (COA) which no longer meets the company’s needs.

    In data migration, this means that the implementation team needs to build the new COA, map the COA from the current system to the new ERP structure and then correctly insert the new COA data into any ERP records which are COA reliant (e.g., General Ledger, Accounts Payable).

    Obviously, the first data migration task is to determine the scope of the data to load into the new system. Depending on the amount of data to be loaded, there are different ways to complete the process.

    In addition to data migration and import, there may also be a need to convert and re-format current system information (GL accounts, vendor codes etc.) into formats required by the new ERP. This can be a pretty big deal and should be considered in the in the volume decision.

    View the chart below to gain an understanding of the data migration process.

    implementation process flow

    An implementation process flow example appears below. The process flow consists of five phases. Data migration is part of the “Configure and Data Load” phase.

    ERP Change of VAR flow chart

    Note that the Configure and Data Load phase also includes configuration and development. These tasks are normally completed by consultants and are not discussed here.

    Data Types

    Data Type Sources

    ERP data comes from legacy system(s), databases, or user-maintained documents (e.g., spreadsheets). Looking at these data sources and quantifying their ability to easily and accurately provide the required data is an important first step.

    If the legacy system can’t download the information into a file or database, or if the downloaded information’s quality is suspect, a decision needs to be made whether to include the data in the new system. Additional issues such as different ERP record formats and new ERP functionality make the decision a little more difficult.

    Data Migration and History Load Issues

    There are any many different data types to consider when planning ERP data migration and history load. Each type has its own challenges. To give you an idea of what to expect, let’s take a look at the chart of accounts (COA).

    COA records are found in the ERP’s COA tables. Depending on the new ERP requirements, several tasks need to be completed and COA issues may need to be addressed:

    Legacy ERP COA Formats

    The legacy COA may not be in the same format as needed by the new ERP. In this situation, the current COA will need to be re-structured to conform to the new ERP requirements.

    This process can be challenging, especially when having to re-structure from a single account code-based COA to one which uses multiple account segments. A lot of times though, the single account code can be parsed into the necessary segments prior to importing.

    For example, a single account code 8110010101 (fringe benefits- medical insurance- executive department) can be parsed into 8110-010-101 (fringe benefits- medical insurance- 101 executive department. This type of parsing is an easy Excel function.

    Legacy COA Re-Structuring to Correct Past Issues

    Over time, a poorly maintained or improperly structured COA causes GL account set up errors (e.g., duplicate or illogical accounts). Additionally, insufficient room within the original account type ranges requires the user to set up accounts out of the formal COA structure. This results in manual editing of GL based reporting and introduces potential errors and additional reconciliation tasks into the reporting process.

    Issues like these require that the legacy COA be scrubbed to eliminate duplicate or illogical accounts and re-structured to provide a more logical structure in support of future company growth.

    Legacy COA Accounts and Sub-Accounts Used to Track Transaction Detail

    Sometimes, a company chooses to use sub-accounts to maintain a sort of sub-ledger within the GL (e.g., projects, fixed assets, locations). This practice can be traced to the legacy ERP not providing the necessary ERP sub-ledger functionality, causing an inordinate amount of GL accounts to be set up.

    If researched correctly during the sales process, the new ERP should be able to provide the necessary sub-ledger functionality (e.g., project, fixed assets), allowing the user to keep the detail in the applicable sub-ledger, the balances of which, are mapped to a limited number of GL accounts, streamlining the COA considerably.

    Effect on Data and History Load Volume

    None of the issues discussed above are insurmountable problems by themselves but, the major issue in all of them is that any data sitting in these accounts needs to be mapped to the new GL account structure. This is one of the major considerations in determining how much historic data to load into the new system and the level of detail needed.

    For example, if you decide to load detailed sub-ledger transaction history such as Accounts Payable (AP) transactions, you may need to consider that you’ll have to complete the process twice.

    Loading period end GL Trial Balances into the new system already includes the effect of any vouchering activity as it is posted to the GL when the invoice is entered and posted in AP. Loading the Trial Balances should not be a big issue.

    However, if you want to load AP transaction detail into the same periods you need to:

    • Navigate to the AP module
    • Disable the posting to GL option
    • Convert and load the AP transactions into the AP module
    • Reconcile the data loaded and make corrections
    • Enable the post to GL option prior to live processing

    The example above is just one of several options to consider. There are other options that are easier to process. To review additional options, see our companion post Data and History Load How To Ensure Data Quality.

    Don’t take the above information lightly. Data load 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. Again, consider any alternatives available.

    Common Conversion Data Type Examples

    Examples of common conversion record data types are illustrated in the table below:

    Data Type Source Potential Issues
    Chart of Accounts ERP tables ·Legacy ERP COA formats.

    ·Legacy COA re-structuring to correct past errors.

    ·Legacy COA accounts and sub-accounts are used to track transaction details.

    JE detailed transactions General ledger ·Current ERP JE’s may not be in the same format as needed in the new ERP.

    ·Current JE’s may need to be re-mapped to the new ERP COA.

    ·Any automatic JE’s (e.g., recurring, reversing) need to be identified and addressed.

    System record codes Accts Payable

    Accts Receivable

    Inventory items

    Projects

    ·Current codes may need to be scrubbed and re-structured to correct past errors.

    ·Current codes may need to be re-mapped to revised classes in the new ERP.

    ·Additional code information may need to be added to the legacy ERP records to allow the records to be imported into the new ERP.

    Conclusion

    Data migration and history loading are an important part of an ERP implementation. A lot of time and energy are required to complete the tasks involved. Be sure to keep data migration and history loading plans within your company’s capabilities as the tasks to be completed can quickly become overwhelming.

    Finally, remember that your VAR has been doing this for a lot longer than you have and can come up with some viable alternatives worth considering. Be sure to discuss any VAR recommendations on how best to complete the data migration and history load requirements you need.