File conversions “don’t get no respect,” as Rodney Dangerfield would say. Everyone knows they need to write programs and convert files. However, it’s not uncommon to leave off detailed discussion of which files and tables to convert, what data fields and how much data (this year’s only or how many years’ history) until the implementation.
In an order management or ERP system there are literally hundreds of files and tables that need to be discussed. It doesn’t mean that many of them are going to be converted by program to the new file format. Smaller files and tables (e.g. shipping and handling tables) are not worth the programming, test and conversion time. It’s faster with some to learn the new system and build the files manually.
Here are 13 considerations to help you make the right warehouse system file conversion decisions:
Start your file conversion planning early: If you’re still in the selection process, start this discussion immediately. Are there unplanned professional services you need to take into account? If you are in the implementation phase, you’ll find that this is one of the long lead time tasks. Not having the file conversion programs and test files completed will hold up the testing, conference room pilot and on-line training with test files in training exercises. As part of your project plan, go through what you think your file conversion requirements are and determine how this matches up with the software vendor’s expectations and experience.
Determine responsibility: Ideally you should have an IT project person and a department user working as a team for each of the files. The IT manager will do the data mapping to the software vendor’s specifications and programming. The department managers should be involved in identifying the data, reading specifications and looking at test results to validate the converted data.
Be realistic about programmatic file conversions: On the face of it, it sounds wrong to not automatically convert everything by program. But there are way too many files and tables. The effort, time and cost do not justify the programming.
- Files that are typically converted include customers, open accounts receivable (if it is a significant number of records), accounts payable, item/SKU, vendor and maybe general ledger account number. Large sites almost always convert order history and associated details (open orders, history related to returns, tracking notes/comments).
- Don’t convert warehouse inventory files, instead count and load the inventory eliminating SKU errors. In small companies, rekey backorders with a programming change to accept their actual order date. If not too voluminous, rekey open purchase orders. On the new system, can you archive the old system data, and does this eliminate any need to convert files? Figure out the complexity, number of records, time and costs.
What data? Converting from your legacy system to the new ERP or OMS may create data fields which are not in the same named files.
- The new file may have to be built from two older system files. Go through each file and specify the data characteristics (data name, type of character, length, etc.) and map where the data is coming from.
- Over time users may have keyed data in free-form fields that need to be rebuilt into the new system’s fields, such as substitute item numbers in a description field. User-defined fields will need to be identified and converted or rebuilt.
- One thing that companies often do is take user-defined data fields to have a specific meaning. Over time, many times data fields are often misused to create new functionality.
How much history? In working with large multichannel retailers, we find they often wrestle with how much customer and item/SKU sales history to convert. Much of this will depend on expectations, use in forecasting and whether the new system has history fields.
Vendor file conversion programs and data mapping: Ask your vendor for file mapping layouts they expect the data to be converted to. Does the vendor have standard conversion programs from vendor A to vendor B?
Develop conversion control totals: Develop hash totals comparing records between the old and new systems. Are all the records being converted and built, or are they being rejected?
Visual validation: Once you feel the programmatic conversion is working. Get some of the department members to visually review the exact records on the old system with the new data and format. What conversion errors do you find? It’s never perfect on the first, second or maybe third conversion pass.
Pass the file and correct process: It’s an iterative process. Write and test basic file conversion programs. Select “nth” samples. Test the file; correct the data and rerun again, then move to a larger sample.
Translation of data: A good example is the parts numbers of two different systems; they may be in totally different formats. For example, on the old system apparel items may be part/SKU oriented. On the new system maybe it’s a style, color and size matrix. What changes need to be validated?
Data cleanup: An example is any standardization of data fields. For example, black may be denoted as BLK, blue as BLU, etc. Set standards for your codes.
Benchmark the time it takes to run file loads: Test with every “nth” record for test file conversion. We have found that that loading millions of records can be a very slow process in SQL. Don’t think you’re going to test with a full file. How long will the conversion take and how will you keep an online environment in sync between old and new systems?
Be realistic: What can your internal staff do and what do you need vendor assistance with?
Start planning the file conversion tasks early, and don’t turn this over to IT. All the users of the new ERP or OMS – call center, inventory control, accounting, warehouse – have a major responsibility for accuracy and readiness for the conversion and go live.
Curt Barry is president of F. Curtis Barry & Company