As competition and technology continue to evolve in today’s industrial and manufacturing industries, companies are faced with the ever-increasing challenge of reducing costs and improving efficiency while maintaining production quality. These manufacturing companies often have multiple sites spread across large geographic regions, each with thousands of maintenance, repair and operations (MRO) spare parts on hand to keep operations running. In such large organizations, several different employees enter items into various enterprise asset management (EAM) systems at each site, with little or no standard guidelines, and often in multiple languages. Over time, this lack of standardization causes materials data to become inconsistent and inaccurate, resulting in many negative effects that can be felt throughout all units of the business.
The most common effects caused by corrupt materials data include:
- Unidentifiable items
- Excess inventory
- False stock-outs
- Equipment downtime
- Inefficient part searches
- Increased maverick purchases (direct buys)
- Limited benefits from EAM systems
These inefficiencies can cost companies significant time and money, while preventing them from making critical data-driven decisions.
The Data-Cleansing Process
In order to transform corrupt data into consistent quality data, a data-cleansing process must be implemented to create one common corporate catalog that can be maintained throughout the entire organization.
While the data-cleansing process may appear very simple in nature, it requires a very unique and specialized set of software, people and procedures. Some data-cleansing companies pride themselves on efficiency and speed through the use of automated software, but in reality, there is no software application that can accurately cleanse mass data files without human intervention. The data-cleansing process is actually much more detailed, and for the most accurate results, requires the use of automated software applications combined with the involvement of cleansing specialists to ensure consistency, accuracy and efficiency.
To illustrate the data-cleansing process from start to finish, it has been broken down into nine steps. While every project is different based on specific customer requirements, these nine steps cover the standard procedures involved in every data-cleansing project.
Step 1 – Segregate and Standardize the Manufacturer Name and Part Number
Using automated software, the manufacturer name and part number are extracted and segregated from the unstructured free text description. Once segregated, the manufacturer name and part number are corrected and standardized, ensuring each unique manufacturer name and part number maintains one consistent structure throughout the entire database.
Step 2 – Assign Noun-Modifier and Required Attributes
Following the segregation and standardization of manufacturer names and part numbers, a noun-modifier dictionary is used to assign the correct identifier and descriptive properties for each item. As illustrated below, using the noun-modifier dictionary, each item is assigned a noun-modifier pair, where the noun is the primary identifier and the modifier is the secondary identifier. Each noun-modifier pair also contains on average five to seven associated attributes, which further describe the characteristics of that item.
Step 3 – Populate Attributes
After standardizing and populating information provided in the customer’s raw description, the remaining attributes are populated using internal and external tools such as the master parts library, which contains millions of pre-standardized items. An online research tool assists in the search and collection of additional part information. Using these powerful tools, item descriptions are accurately and efficiently enhanced with information retrieved directly from manufacturer catalogs.
Step 4 – Assign Classification Codes
Once all items have been correctly described by a noun, modifier and corresponding attributes, they can be assigned customer-specified classification codes. The classification codes are typically used for commodity segmentation, spend analysis and other custom reports, enabling companies to leverage purchases and gain insight for improved procurement-related efficiencies.
Step 5 – Identify Duplicate Items
After cleansing and classification is complete, duplicate items within the database are identified by direct duplicate (same manufacturer name and part number) or by form-fit-function (different manufacturer name and part number but identical according to type, size and material). Once duplicates have been identified, they are assigned one common corporate part number, descriptions are duplicated to appear identical throughout the database, and the items are flagged for customer review.
Step 6 – Quality-Control Review
Due to the emphasis on quality and consistency, the next step involves a final human review of all items, typically conducted by an assigned project leader or dedicated quality-control person. The quality-control process ensures that every item follows proper format and nomenclature according to pre-defined customer standards, while verifying that enhanced descriptions are correct, accurate and complete.
Step 7 – Send Review List to Customer
On average, 10 percent of the materials database is usually found to be review items, meaning items lacking critical information for accurate part identification such as manufacturer name or part number. During the data-cleansing process, these items are flagged and compiled into a customer review list. The review list is returned to the customer, who must then physically locate the item within the storeroom and record the necessary part information to be added into the material master.
Step 8 – Format Data to Customer ERP System
Once the missing information has been collected for all review items and the entire cleansed database has been approved by quality control, it is deemed complete and transferred to the IT department. At this stage, IT specialists format the data to the customer-specified enterprise resource planning (ERP) system and extract it into a return file. The formatting stage is critical to achieving the desired end result, as every ERP system has its own unique layout, headers and field limitations.
Step 9 – Return Cleansed File
Once the entire data file has been cleansed, standardized, enhanced, de-duplicated, reviewed and formatted to the customer’s ERP system, it is electronically delivered to the customer. At this time, the data can now be uploaded directly to the customer’s ERP system.
Esthetically, the results of data cleansing are obvious, as the data now clearly maintains one consistent format and nomenclature throughout the entire organization while containing enhanced information for improved part identification. However, the real benefits are those that may not be as visually obvious but present the greatest return on investment. The most valuable benefits are those that come from the ability to identify and remove excess, obsolete and duplicate items while improving the ability to quickly search and locate parts when time is of the essence and minimizing equipment downtime is crucial. Key benefits include:
1. Cost reduction
- Identification of excess-active and obsolete inventory
- Identification and elimination of duplicate items
- Reduction of equipment downtime
- Reduction of maverick purchases
- Reduction of expedited part orders
2. Improved maintenance efficiency
- Efficient part searches
3. Maximum ERP/EAM benefits
- Improved reporting capabilities
From a long-term perspective, quality materials data is the key to maintaining operation costs and efficiencies. This process does not end once the data-cleansing project is complete. Maintaining ongoing data quality requires a strict set of catalog management procedures to ensure accuracy and consistency as new items are added and existing items are modified or suspended. Most data-cleansing companies offer some type of catalog management software or service for customers to maintain the quality of their cleansed catalog. However, unless the customer is able to dedicate an internal resource to manage the catalog, outsourcing this activity to the experts who originally cleansed the database will always deliver the best results.
About the Author