Problem solve Get help with specific problems with your technologies, process and projects.

IT Challenge: ERP data conversion for an ERP upgrade

In this IT Challenge of the Month, we'll brainstorm ways to perform a successful ERP data conversion when moving from a legacy ERP system to a SQL-based ERP system.

The IT Challenge of the Month for November 2010 is:

My business is upgrading from a legacy system to a new SQL-based ERP system. How can I ensure that data from the old ERP system -- which is all basic alpha or numeric types -- can be converted into appropriate SQL data types without file corruption or loss of data?

Do you have a solution to this challenge? Have you encountered a similar issue at your business? If so, please contact the editors and share your suggestions or experiences. 

And be sure to check back here all this month -- we'll be posting solutions from experts and readers as we receive them.

From ERP and manufacturing industry expert Steve Phillips:

When it comes to ERP project management, the fewer data conversion programs that must be written, the better. However, for those conversions that are justified, it is first important to get consensus on how the data will be used in the new system.  

Once you know these requirements, you can develop a clear specification for each conversion.  This helps avoid rework and defines expected outcomes, which are the basis for testing and verification. Most software vendors and consultants have data-mapping templates that jump-start the specification process.

Next, you need to test data loads. Here are some tips::

1.)    The number of records in the extract file from the legacy system should match the number of records loaded into the new system file. 

2.)    Most data-import programs that are provided with ERP software enforce data integrity with error reports listing data that failed to load, and the reason. Check the reports, correct the data issue, and rerun the conversion.

3.)    Visually compare the field content of a good-size sample that covers most of the key data scenarios. Use the original specification to verify the values in the fields.

4.)    When large amounts of data are involved, write some simple exception-based queries against the files in the new system to highlight data anomalies, such as values that are not equal to specified values, conflicting data, or formatting issues.

5.)    Test all key applications using the converted data. If a data format issue exists, application error messages will occur.

Dig Deeper on ERP integration