Home
 > Research and Reports > TEC Blog > Data Conversion in an ERP Environment

Data Conversion in an ERP Environment

Written By: Joseph J. Strub
Published On: October 21 2002

Introduction

Converting data in any systems implementation is a high wire act. Converting data in an ERP environment should only be undertaken with a safety net, namely a well thought-out plan of execution. This article discusses the guidelines for converting data when considering manual or electronic alternatives.

First, let me start out by saying that data conversion is not an art form; it's a science. If approached in a reasonable manner, reasonable results can be expected. This article suggests and reinforces a logical, well-thought out approach to the tricky question of data conversion, namely when should you manually enter the data and when should you electronically convert it. This article also provides the pros and cons of each alternative as related to a specific file type, whether it be inventory on-hand quantities, customer account receivable balances, or sales history.

Whether talking about a single application or an ERP software package, converting legacy data is an issue that merits discussion and careful consideration. Obviously, the conversion considerations for an ERP package, with all of its touch points, are far more involved and could require many more resources. Furthermore, since the effects of the conversion methodology may impact the end user community, these individuals should be integrally involved in the discussions and decisions. Data conversion is a critical step in the life cycle of the implementation of ERP software. As such, it deserves careful and important consideration and should not be treated as an after thought.

Factors to Consider

As discussed below, there are three sometime conflicting factors to consider when deciding whether to use manual or electronic means for converting existing data structures, namely:

  1. Programming Effort

  2. Volume of Data and Frequency of Maintenance.

  3. Availability of Resources

However, before getting into the discussion of these factors, a word of caution is appropriate. Despite my personal dislike for the overused phrase, "Garbage in; garbage out," I cannot think of any better way of describing what could happen if your legacy data is corrupted or unreliable. Take the time to cleanse and prune your data before you convert it. This may avoid looking for red herrings as sources of problems when you are in the middle of acceptance testing or, worst, in live production.

Programming Effort

When converting data from sources, such as second or third generation file structures, to target environments, such as today's relational databases, a single file can update multiple tables. For example, in a legacy design of a customer file, the customer, ship to, and bill to addresses are typically stored in a single physical structure. In a relational database, to avoid redundancy of data elements and facilitate maintenance activities, these three addresses could be maintained in three separate tables, thereby requiring three separate programs or one complex program.

Consequently, in addition to requiring a knowledge of the database design, a significant programming effort may be needed to move data elements from a legacy data source to a targeted relational database. Furthermore, what makes this effort difficult to swallow or justify is that the program is typically not used again. Unfortunately, because the data sources tend to be unique, it is unusual to be able to scavenger from other customers of your ERP vendor. Regardless, this possibility should be investigated. Once perfected, however, these shortcomings can be offset by the fact that conversion programs can be run repeatedly.

Volume of Data and Frequency of Maintenance

Obviously, the volume of records will strongly influence whether data is manually or electronically converted. However, the frequency with which these records are changed in the course of the ERP implementation is a contributing factor. Should the decision be made to manually enter data, once the data starts to be resident in the ERP system, any changes made to the source data in the legacy environment must also be made in the new target ERP environment. This is the proverbial catch 22. While there is a need to start entering data into the live ERP database as soon as possible to ensure that the activity is completed before going live, this may increase the length of time duplicate data maintenance is required and the possibility of data becoming out of sync.

While strong arguments can be made to manually convert static or semi-static data, this course of action will definitely impact the user community and should not be made without their input.

Availability of Resources

The availability of resources concerns having the necessary manpower to develop programs or manually enter data into the ERP database. While manpower is needed to develop a conversion program, typically the ERP vendor can provide assistance in this area. However, resources from the user community to enter data are more acute since this activity takes away from their time to learn, test, and exercise the ERP software. This time is particularly critical in the business process and integrated pilot phases. While the use of temporary, outside services may be a viable alternative, this approach still requires serious preparation time and cannot take advantage of users' familiarity and knowledge of the data. An accounts payable clerk tends to know intuitively when vendor information doesn't look right. How you do find a substitute for this insight?

Given these factors, the following table presents some pro's and con's of manual and electronic conversion for critical files/data structures. While this analysis may vary from company to company, it should provide sufficient food for thought to any organization to digest.

Pro's and Con's of Conversion Alternatives

Manual Conversion Electronic Conversion
File/Data Structures Pro's Con's Pro's Con's Conventional Wisdom
Inventory Balances Because of time criticality, not practical. Prone to error when time is essential. Since inventory balances must be current and accurate just prior to going live and time is critical, this option provides the most flexibility. Requires programming effort of a moderate nature. Of all the electronic conversion programs, this is one process companies cannot avoid. This is primarily due to the fact that inventory balances must be accurate just prior to going live.
Sales History No practical advantages. If this is needed in ERP database, manual data entry too intensive to be realistic. Field to field translation is consistent and straight forward. Requires programming effort of a low to moderate nature. If sales data is required in the ERP database for customer analysis, the only realistic alternative is electronic conversion. Because of the infrequent need of this type of data, companies can provide query access to the system being replaced for an interim period until sufficient history has been established.
General Ledger History No practical advantages. If this is needed in ERP database, data entry too intensive to be realistic. Field to field translation is consistent and straight forward. Requires programming effort of a low to moderate nature. If comparable financial data is needed for reporting (year-to-date, last year, etc.), electronic conversion is the only realistic alternative. For report generation, companies can supplement ERP-resident data with PC-based spreadsheet data until sufficient history has been established.
Product, Item, Raw Material Files Depending on the volume and if nomenclature does not change, this data could be entered into the live database. Prone to errors, particularly if nomenclature translation is needed and may place a data entry burden on users. When used in conjunction with a spreadsheet-based translation table, simplifies the setup process and creation of products, items, and raw materials. Requires programming effort of a moderate nature. Because of volume considerations, companies usually will opt with the electronic alternative. To minimize the complexity of programming when changes are made to nomenclature, a separate translation table will be created and maintained by the user. This table will be used an external input to the conversion program process.
Customer File Assuming a manageable volume and low frequency of maintenance, easiest to implement. Places burden of input on users during piloting process and requires redundant maintenance of both existing and ERP-resident data. Once developed and tested, conversion is repeatable and can be completed just prior to going live. Requires programming effort of a moderate to complex nature. Because there is not a sufficiently high degree of change in the customer base, most companies will manually enter customer data starting as late as possible in the piloting process and continue to maintain existing and ERP-resident data.
Vendor File Assuming a manageable volume and low frequency of maintenance, easiest to implement. Places burden of input on users during piloting process and requires redundant maintenance of both existing and ERP-resident data. Once developed and tested, conversion is repeatable and can be completed just prior to going live. Requires programming effort of a moderate nature. Even more so for vendors than for customers and because there is not a sufficiently high frequency of change in the vendors, most companies will manually enter vendor data starting as late as possible in the piloting process and continue to maintain existing and ERP-resident data.
Open Vendor Invoices Permits a cut-over to ERP systems for purchasing and accounts payable functions without ties to legacy system being replaced. If vendor structures, terms, and conditions have changed in the ERP systems, data translation is required and may be prone to errors. With adequate testing, eliminates translation errors and permits a complete cut-over to the ERP software. Requires a significant programming effort for a one-time event. Prior to going live with accounts payable, companies make a final check run on their old system forall open invoices. As inventory is received, preprinted checks are signed and released, miscellaneous inventory receipts and manual journal vouchers are posted in the ERP software
Open Customer Orders Permits a cut-over to the ERP software for customer functions without ties to system being replaced. If customer structures, terms, and conditions have changed in ERP software, data translation is required and may be prone to errors. With adequate testing, eliminates translation errors and permits a complete cut-over to ERP software. Requires a significant programming effort for a one-time event. Companies will manually re-enter open orders into ERP software to ensure that supporting manufacturing structures are properly updated.

About the Author

Joseph J. Strub has considerable experience as a manager and senior consultant in planning and executing ERP projects for manufacturing and distribution systems for medium-size and Fortune 100 companies in the food & beverage, chemical, and CPG process industries.

He can be reached at JoeStrub@writecompanyplus.com.

 
comments powered by Disqus

Recent Searches
Others A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

©2014 Technology Evaluation Centers Inc. All rights reserved.