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:
- Programming
Effort
- Volume of Data
and Frequency of Maintenance.
- 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.