Converting data to a new CMMS might not be as easy as you think

David Berger explores the art and science of data conversion.

By David Berger, P.Eng., contributing editor

The expectation when replacing your current CMMS is that the process will include the smooth transfer of all information from your old system to the new system. Converting data in this manner, from one system to another, is often referred to as “data conversion.” But to the shock of some, data conversion is not as simple as it might appear. Data residing on the old system may be obsolete, inaccurate, incomplete, or in an incompatible format. Converting data in this state may do more harm than good.

Thus, data conversion is a delicate balance of determining what information is worth moving to the new system (the “art” of data conversion), and how best to do it (the “science” of data conversion). Consider these items when you are contemplating conversion of your legacy data.

Types of data for conversion

Legacy data can be exported to spreadsheets or other file formats, manipulated if necessary, and then imported into the new CMMS.

It may not be necessary to convert all of your data to the new CMMS. For example, do you really need historical data on the new system, such as old work orders, purchase orders, projects, and other elements of equipment history? Not only are these documents potentially inaccurate, they may not be in a format compatible with the new system. Typically, companies are reluctant to spend the time and money needed to convert transactional/historical data, especially when data quality is suspect. Many companies prefer to start with a clean slate and are quite happy to retrieve historical information in other ways within the first few years following the implementation of the replacement CMMS.

Static or tombstone data refers to non-transactional information that describes a given object, such as asset characteristics, reorder point for a spare part, supplier contact information, and employee certification. The problem with data conversion of tombstone data is that it may require extensive scrubbing to ensure its accuracy and completeness. The cost may be prohibitive compared to simply entering data from scratch into the new system.

Another type of data that may be considered for conversion is table-driven data, such as priority, cause, and status codes. This information is not often ported electronically to the new system, as companies prefer to rethink their relevance to the more sophisticated replacement CMMS.

Preparing a request for proposal (RFP)

Data conversion requirements can be defined when preparing user specifications as part of a request for proposal. It is very helpful for prospective bidders to understand your current environment in terms of hardware, software, and data. The more information that can be provided to CMMS vendors, the more accurate their quotes will be. However, it is rare that CMMS vendors will risk committing to a firm quote for data conversion from an RFP alone. This is because, until vendors are able to inspect the condition of legacy data and determine exactly what you intend to convert, it is difficult to determine a fair price.

To increase the accuracy of vendor quotes, it is useful to include, as an appendix within the RFP, the data dictionary of the current system, as well as sample data. A data dictionary is an alphabetical listing of all fields in the current system from all master files, such as the equipment master, parts master, and supplier master. The data dictionary outlines at least the following information for each field in the system:

  1. Field name
  2. Description
  3. Field length
  4. Field type (alphanumeric, numeric, date, text)

Sample data should be fairly representative of the general condition of key information residing on legacy systems. Sample data can also be useful for building test scripts, as well as for comparing and evaluating CMMS vendor offerings.

Scrubbing master data — Is it worth it?

Even if a quote is provided for data conversion through the RFP process, a final decision should not be made as to which data to convert and how until the selection process is completed and detailed implementation workshops have begun. This is to ensure sufficient rigor in evaluating data collection requirements for the new processes and CMMS. Once it is clear which master data is required, a decision must be made as to whether legacy data is sufficiently clean to port to the new CMMS. In most cases, extensive scrubbing of master data will be required to bring data quality up to an acceptable standard.

Data scrubbing can be a long and tedious process if legacy master files were not sufficiently maintained. For example, consider the following questions.

  1. How many records are still valid (obsolete parts that are still on the books, assets that were scrapped or sold, suppliers that are no longer in business, and employees that have left the company)?
  2. How many records are still accurate (supplier contact information that has changed, spare part lead times that have increased or decreased over time)?
  3. How many records are complete (a standard spare parts listing for all major equipment, part numbers cross-referenced for all OEM and alternative suppliers)?
  4. How many records are in sufficient level of detail, especially in light of the capabilities of the new CMMS (detailed job plans for preventive and condition-based maintenance, sufficient level of granularity for equipment and spare part hierarchies)?
  5. How many records are missing altogether (new equipment that was never entered onto the system, secondary warehouse locations, viable table values for problem/cause/action codes)?
David Berger, a Certified Management Consultant (C.M.C.) registered in Ontario, Canada, is a Principal of Western Management Consultants, based in the Toronto officeDavid Berger, a Certified Management Consultant (C.M.C.) registered in Ontario, Canada, is a Principal of Western Management Consultants, based in the Toronto office. David has written more than 200 articles on a variety of topics such as maintenance management, operations management, information technology, e-commerce, organizational design, and strategy. In Plant Services magazine, he has written a monthly column on maintenance management in the United States, as well as three very extensive reviews of maintenance management systems available in North America. David has done extensive work in the areas of strategy, information technology and business process re-engineering. He can be reached at david@wmc.on.ca.
Subscribe to the Asset Manager RSS feed

In my experience, more than half of companies replacing their CMMS quickly realize the mountain of work associated with master data scrubbing, and instead opt to collect the data using alternate methodologies discussed below. If any master files are in good shape, many CMMS vendors have data migration tool sets to help port the data electronically into the new system.

Compatibility issues

One of the difficulties in converting data is a potential incompatibility in field length, type, or configuration. For example, suppose your legacy system uses a single, 120-character text field for the entire supplier address, but the new system employs a three-line address field and includes separate fields for suite number, county, post office box, and postal code, complete with the most appropriate field type. This makes it difficult and costly to transfer data electronically into the improved formatting of the new system.

Manual vs. automated data conversion

Most modern CMMS packages are able to import bulk data, such as for data conversion. Legacy data can be exported to spreadsheets or other file formats, manipulated if necessary, and then imported into the new CMMS. At the very least, master data can be collected manually or semi-manually on spreadsheets and imported into the new system. Some CMMS vendors provide more sophisticated data conversion tools and consulting services, usually for an additional cost. These tools are especially useful for large, complex conversions involving data manipulation.

Alternatives to data conversion

If the effort or cost of data conversion proves excessive, the alternative is manual keying of data directly into the system or bulk-loaded via the import function. For transactional/historical data, some companies keep some or all legacy systems operational for the purpose of preparing reports or retrieving old data. Although there is an additional expense in keeping legacy systems alive, you never know which historical data might be required, especially in the first year or so following implementation of the new system. Alternatively, some companies print and store hard copy or electronic reports and other data from legacy systems, which offers a cheaper but less flexible solution.

Show Comments
Hide Comments

Join the discussion

We welcome your thoughtful comments.
All comments will display your user name.

Want to participate in the discussion?

Register for free

Log in for complete access.

Comments

No one has commented on this page yet.

RSS feed for comments on this page | RSS feed for all comments