Friday, November 9, 2012

A Case for Data Scrubbing

Often maintenance systems don't reap the benefits that they promise through no fault of their own. How can you expect a system to improve underlying data? The answer is that you can't. What you need is to have good data in the system so that it can be accessed, processed and used to provide practical information for the organization.
Let me illustrate the cost of not having good data with an example. A multi-site manufacturer has four locations, three of which are in fairly close proximity to each other. Each site has its own autonomous storeroom with inventory parts. At each site, there is a part time catalog manager responsible for all database activity. Because the plant is unionized and positions often change, the catalog manager may be replaced every few months.
The resulting inventory catalogs reflect this: inconsistent manufacturer naming; missing manufacturer part numbers; inconsistent use of symbols/abbreviations; spelling mistakes; incomplete descriptions and; duplicate items. System word searches are next to impossible and finding a part is a frustrating, challenging, usually unsuccessful experience.
Maintenance workers at all locations had long lost faith in stores; each kept a stash of parts hidden somewhere for his own use. To plan for a repair job, they would attempt to find parts through the system, but if unable to locate what they needed, they would abandon the search and just order the part directly; in the case of an emergency, they might call another location to request the loan of a part. Inventory value across the company topped $80 million.
Recognizing that something had to be done, the company attempted to undertake the data cleaning themselves. They established a team of nineteen internal people comprised of maintenance workers (Electrical, Mechanical, Instrumentation & Pipe Fitters) from all four sites as well as two support people and one Inventory Specialist.
After more than a year of effort, and with only half the database cleaned, they decided to engage outside data cleaning experts to revitalize the effort. Systematically, the data from each site was cleaned. In conjunction with maintenance workers from all sites, a common layout for item descriptions with acceptable noun/modifier pairs was developed; the order of attributes was negotiated to satisfy all locations; terminology, symbols, abbreviations and industry nomenclature were agreed upon. It took six months to rework the entire database.
Having good data brings with it measurable rewards. Duplicates within sites were revealed to be in the 10% range. Common items across sites were identified in the 25% range. Merging the three regional stores into a central warehouse reduced overall stocking levels and allowed sites to share common critical spares. It also freed up millions in cash savings.
Item searches successfully revealed part information that maintenance workers could count on. As confidence in the central stores grew, additional stock from private caches was repatriated, further adding to the savings realized. Overall, across the company, inventory was reduced by more than 20%.
The data cleansing effort clearly paid for itself several times over. It also became the impetus for other corporate initiatives. The company went on to improve its item-equipment links to further enhance the maintenance system. In addition, it consolidated items along product lines and reduced its supplier base for volume discounts.