While data quality may seem both simple and obvious, many companies overlook this area due to a misunderstanding on how the CMMS and data are used. In some cases, companies are well aware of their data quality issues, yet don’t understand the affects that poor data can have on their organization. In other cases, companies believe that an expensive new CMMS will be the answer to all of their problems, only to realize six months down the road that they cannot conduct basic part searches and transactional reports.
Improving and maintaining data quality is an ongoing process that plays a critical role in an organization’s overall cost savings strategy and the success of a CMMS implementation. A typical data cleansing project begins by establishing client-specific data standards that will consistently be used to structure data moving forward. The Standard Operating Procedure will address client-specific CMMS configuration, naming convention, abbreviation, and classification requirements.
Before migrating legacy data to the new CMMS, each and every item needs to be cleansed, standardized and enhanced where possible, according to pre-defined client standards. During the cleansing process, manufacturer names and part numbers are segregated, existing information is parsed and standardized, spelling mistakes are corrected, and text is converted to upper or proper case. Each item is assigned a new corporate part number, linking all sites of the organization in a “virtual warehouse”, while legacy item/ID numbers remain in tact for future reference. After the cleansing process is complete, duplicate items are identified by direct match and fit-form-function similarity. Direct duplicates include two or more items possessing the same manufacturer name, part number and description, whereas, fit-form-function duplicates include two or more items that possess different manufacturer names and part numbers, but have the same description and specifications. Dependent on client preference, items can be classified by various international and client-specific codification systems, enabling deeper reporting capabilities. The final deliverable is then formatted to comply with specific configuration requirements of the chosen CMMS, including field name, type and character limitation.
To illustrate the results of data cleansing, let’s take a look at a few samples.
The first sample shows what a client’s raw data looked like before and after it had been cleansed, standardized and enhanced.
Before – Raw Client Data
| Plant | Plant 1 |
| Stock Number | 222-113-509 |
| Manufacturer Name | SKF USA |
| Manufacturer Part Number | 23022CCC3W33 |
| Description | 23022 CC/C3W33SKF Bearing, ABC Co. 12345 |
| Vendor Name | ABC Company |
| Vendor Part Number | 12345 |
After – Cleansed, Standardized and Enhanced Data
| Plant | Plant 1 |
| Material Number | 1000001 |
| Manufacturer Name | SKF |
| Manufacturer Part Number | 23022 CC/C3W33 |
| Material Description | BEARING, ROLLER, 110MM ID, 170MM OD |
| PO Text | BEARING, ROLLER, 110MM ID, 170MM OD, 45MM WD, SPHERICAL, SELF-ALIGNING, C3 CLEARANCE, SKF, 23022 CC/C3W33 |
| Vendor Name | ABC COMPANY |
| Vendor Part Number | 12345 |
| Old Item Number | 222-113-509 |
| Corp Number * | 1000001 |
The second sample illustrates the consistency within a specific product group before and after cleansing.
Before – Raw Client Data
| MFG Name | Part Number | Description |
| Timken Co. | 25821 | BEARING TAPERED |
| Timken | 33821 | BEARING ROLLER TAPERED |
| Timkin | H913810 | BEARING TAPERED 5 3/4” x 1” x 0.130” |
| Timken | M88010 | BEARING TAPERED 2 11/16” x 11/16” x 0.060” |
After – Cleansed, Standardized and Enhanced Data
| MFG Name | Part Number | Description |
| Timken | 25821 | BEARING, CUP, TAPERED ROLLER, 2.8750” OD, 0.7500” WD, 0.0310” RAD, STRAIGHT OUTER |
| Timken | 33821 | BEARING, CUP, TAPERED ROLLER, 3.7500” OD, 0.8750” WD, 0.0930” RAD, STRAIGHT OUTER |
| Timken | H913810 | BEARING, CUP, TAPERED ROLLER, 5.7500” OD, 1.0000” WD, 0.1250” RAD, STRAIGHT OUTER |
| Timken | M88010 | BEARING, CUP, TAPERED ROLLER, 2.6875” OD, 0.6875” WD, 0.0620” RAD, STRAIGHT OUTER |
As you can see, the transformation is astonishing and the benefits of data cleansing are clear. What was once inconsistent, incomplete and inaccurate information is now clean, standardized and enhanced data that, in this case, has been formatted specifically for SAP. Using the cleansed Item/Material Master, organizations can now identify and dispose of excess inventory, reduce equipment downtime, eliminate false stock-outs, prevent maverick purchases, and leverage accurate search and reporting functionalities for improved maintenance efficiency.
Through trial and error, organizations are quickly learning that 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 cost to an already expensive CMMS implementation, the savings and long-term benefits far exceed the upfront investment.
