Tips on maintaining the maintenance spare parts database

Jeff Shiver, People and Processes
Tags: maintenance and reliability, CMMS and EAM, planning and scheduling, inventory management

I often see groups really struggling with managing their spare parts data due to years of neglect and lack of ownership. On the initial CMMS implementation, the data may have been properly described with a noun/qualifier/qualifier approach for the short text description. As time passes, people move on and someone else takes over the database. They may not receive training or understand the naming concepts. Soon, the users of the data can’t find the parts housed in the storeroom due to the way items have been named. The storeroom inventory value goes up, too, as parts become duplicated since no one could find the stocked item. So, how do you straighten this mess out?

Depending on the number of items, it may be simpler to opt for a data-scrubbing exercise by a third party. Before I describe this process to you, let me prepare you for a little sticker stock on the cost. Expect to pay somewhere between $3.25 and $4.50 per SKU (stock-keeping unit), depending on the services rendered. So, 30,000 SKUs will run you roughly $100,000 to $135,000. If you were unsuspecting, you can pick yourself up off the floor now.

In general terms, here is how the data-scrubbing process works. You provide a database listing of all of your SKU information – like manufacturer, model number and short text description – to the vendor. The vendor will already have a data dictionary that it has built over time with all of the parts it has scrubbed to date. This is usually a pretty comprehensive listing; but if you are in the railroad business as an example, the data dictionary listing for those parts may not be as extensive. The vendor will typically work with you to customize the data dictionary when required. The database of SKUs that you provided is then compared against the data dictionary. You end up with direct matches, close associations and complete exceptions. The close associations require another pass from a human to verify and correct as required. They may require field inspection. The exceptions do require field inspection to address and correctly describe. As an example, a short text description of “Item 2 from Drawing 234563-2” doesn’t cut it. Can you tell me what part that is; is it a shaft, or a spindle? For what piece of equipment? It might be an SKF 6207-2RS1 bearing, but how would you know from a description like that? So, there is an on-site component to the data-scrubbing effort.

Once this effort is complete, the vendor provides you a database that is loaded back into the CMMS. You can ask the vendor to identify duplicates for you. In addition, it can provide a more comprehensive inventory analysis, if needed, based on (for instance) stocking levels and turns.

Now, the real priority for you is to put processes in place and audit to ensure you never end up in this situation again. You need to do this before you load the scrubbed data. That means that you really need a gatekeeper or two trained in creating the short description text as an example. After all, you just spent $135,000 of the business’ money to scrub this data. But wait, you are not done yet. Now you should be looking at the storeroom inventory to rid yourself of the duplicates, if not already done so, and optimizing the items stored. This means (for example) determining the need to stock, at what quantities, and what reorder points.

About the author: