THE DATA CHALLENGE

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 uptime. These manufacturing companies often have multiple sites spread across large geographic regions, each with thousands of MRO spare parts on hand to keep operations running. In such large organizations, several different employees enter items into various enterprise 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;
• Duplication;
• False stockouts;
• Equipment downtime;
• Inefficient part searches;
• Increased maverick purchases (direct buys);
• Limited benefits from enterprise resource planning (ERP) or enterprise asset management (EAM) systems.

These inefficiencies can cost companies significant time and money, while preventing them from making critical business decisions.

THE DATA CLEANSING PROCESS

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 the most accurate results requires the use of automated software applications combined with the expertise of cleansing specialists to ensure consistency, accuracy and efficiency.

Step 1 – Establish a Standard Operating Procedure

The first step in any data cleansing project is to establish a custom standard operating procedure that will address data format, naming convention and abbreviation requirements. Each company and industry is different and it is critical that data be tailored to the specific enterprise system and business need. Once the standard operating procedure has been approved, it will become the single source for structuring materials data moving forward.

Step 2 – Pre-Cleanse

A pre-cleanse program passes through raw client data in preparation for the standardization and enhancement process. Using automated software, manufacturer names and part numbers are identified and segregated from the unstructured free text descriptions. Once segregated, the manufacturer names and part numbers are corrected and standardized, ensuring each unique manufacturer name and part number maintains one consistent format throughout the entire database (Figure 1).

Figure 1

Step 3 – Assign Noun-Modifier Pairing

Following the segregation and standardization of manufacturer names and part numbers, a standard part naming convention must be applied to each item. As illustrated in Figure 2, a standard noun-modifier dictionary is used to assign each item with a noun-modifier pair, where the noun is the primary identifier and the modifier is the secondary identifier. Each noun-modifier pair also contains an average of five to seven associated attributes, which further describe the characteristics of that item.

Figure 2: Noun-modifier dictionary

Step 4 – Populate Attributes

After standardizing and populating information provided in the customer’s raw descriptions, 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 parts information. Using these powerful tools, item descriptions are accurately and efficiently enhanced with information retrieved directly from manufacturer and original equipment manufacturer (OEM) catalogs.

Step 5 – Assign Classification Codes

Once all items have been correctly described by a noun, modifier and corresponding attributes, they now can be assigned United Nations Standard Products and Services (UNSPSC) and/or 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 6 – Identify Duplicate Items

After cleansing and classification is complete, duplicate items within the database are identified by direct duplicate (they have the same manufacturer name and part number) or by form, fit and function (they may have a different manufacturer name and part number, but are 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 7 – 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 every item follows proper format and nomenclature according to predefined customer standards, while verifying enhanced descriptions are accurate and complete.

Step 8 – 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 9 – Format Data to Customer ERP/EAM/CMMS

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’s specific enterprise system or computerized maintenance management system (CMMS) and exports it into a return file. The formatting stage is critical to achieving the desired end result as every enterprise system has its own unique layout, headers and character limitations. For instance, some systems have a 40-character limit on the short description, which presents a unique formatting challenge and requires a specific set of abbreviation standards during the cleansing process.

Step 10 – Return Cleansed File

Once the entire data file has been cleansed, standardized, enhanced, deduplicated, reviewed and formatted to the specified enterprise system, it is electronically delivered to the customer. At this time, the data can be uploaded to the live enterprise system.

THE RESULTS

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 (Figure 3).

The real benefits, however, 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 now identify and remove excess, obsolete and duplicate items, while improving search and reporting functionalities within the enterprise system.

BEFORE

AFTER

BEFORE

AFTER

BEFORE

AFTER

Figure 3

Key benefits include:

• 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.
• Improved Maintenance Efficiency
  • Efficient part searches.
• Maximum ERP/EAM/CMMS 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, though. 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.

No matter how expensive, functionality-rich, or industry recommended a CMMS may be, the reality is that the software is only as good as the data flowing through it. While data cleansing may add costs to an already expensive software implementation, the savings and long-term benefits far exceed the upfront investment. The results truly speak for themselves.