Effective CMMS Database Development

A maintenance management system is a tool used for managing and controlling work, materials, and equipment throughout the oil and gas production chain, from offshore platforms to refineries and pumping stations. Many maintenance management systems are becoming computerized, allowing for a central storage location for the majority of the asset information and data. Computerization of the maintenance system allows users to have access from multiple locations and, if properly set up and maintained, provides an effective tool to track activity over the life of the asset. Computerization of the maintenance system also enables linking of the company’s document management system, warehouse inventory system, and procurement and logistics systems, to name just a few. This linking capability empowers the computerized maintenance management system (CMMS) to reach its full potential.

The Problem

Operations Readiness is a key success factor in both greenfield and brownfield projects. An important factor in achieving Operational Readiness is the development of the Maintenance Management System. Often, however, the Maintenance Management System is an afterthought on a project, and is quickly thrown together. As a result, misinformation is loaded into the system and/or vital information is left out of the system due to time constraints.

Creating an Effective CMMS

One of the most critical aspects related to developing a CMMS is data population and validation, since the quality of what goes into the system will be the determining factor for correct and efficient system utilization. The data population and validation phase of CMMS development can take the longest time if not performed systematically by experienced personnel.

Development of a CMMS should ideally begin at the commencement of detail design to allow adequate time to gather the necessary data from vendors and engineering teams, which is a large undertaking.

Another key aspect of a successful CMMS program is working with the asset owner to develop and implement the maintenance and spare parts philosophies to create an asset hierarchy, and agree on integrity and preventive maintenance (PM) routines. This requires a dedicated implementation team, ideally including staff with experience in various areas such as integrity management, systems engineering, chemicals management, standard operating procedure development, and commissioning. By understanding these interfaces, a more comprehensive and user-friendly maintenance system can be achieved.  

Building Blocks of an Effective CMMS

In order to develop an effective CMMS, the following core building blocks are required:

  1. Develop the Hierarchical Relationships
  2. Develop Templates for Standard Attributes
  3. Include PM Routines, Warranties and Regulatory Requirements
  4. Establish the Equipment Bill of Materials
  5. Validate Data with Quality Assurance and Quality Control (QA/QC) Tools
  6. Coordinate with the Project and Operations Team

Develop Hierarchical Relationships

The CMMS hierarchy development is kicked off with the CMMS Development team becoming familiar with the asset and the CMMS software that will be used to develop the “parent-child” hierarchical relationship. This is followed by the building of attributes and identification of boundaries for the depth of the data to be entered into the system. 


With a greenfield development, an equipment tag take-off from P&IDs is just the beginning of the asset hierarchy. The system is standardized both in nomenclature and in the attributes collected for each equipment tag. This allows for continuous and consistent data development for the terms, data fields and forms used in the system.

In comparison to a greenfield, the updating of a brownfield CMMS begins with an asset walk-down, MOC review and P&ID updating.  It is important that a review of past maintenance activities is performed to ensure a comprehensive update of the system.  By analyzing the past preventive and corrective maintenance activities, we can update the PM frequencies, task lists, spare parts and specialty tools.

Develop Templates for Standard Attributes

Templates for standard attributes to be collected for different types of equipment should be developed. These attribute templates will assist operations in gaining complete knowledge of the equipment upon initial examination. In addition to the attribute collection for each item of equipment, supplementary data related to the equipment, such as P&IDs, PFDs, detail and location drawings, manufacture/vendor books and SOPs can also be integrated into the system and linked to the asset owner’s document management system to ensure that the latest version can always be accessed.

Include PM Routines, Warranties & Regulatory Requirements

Integrity and PM routines are key to extending equipment life. Manufacturers’ recommended PM guidelines are also incorporated into the CMMS to ensure that warranties for all equipment are kept updated, accurate, and according to schedule. Integrity and regulatory requirements are incorporated into the CMMS, results from monitoring and inspection activities are documented, and any subsequent findings and actions are recorded.

Experience indicates that the development of standard maintenance tasks will expedite the process of developing a PM plan, and assist the asset owner to deliver standardization across all equipment types. This approach will facilitate maintenance routines and equipment hierarchies, and ensure that these are accompanied by detailed procedures that are integrated directly into the CMMS. 

Establish the Equipment Bill of Materials

Proper tracking of equipment maintenance, required spare parts, and specialty tools is crucial. Spare part inventory management is particularly crucial for international assets that may not have the infrastructure to support local availability, and so suffer long equipment lead times. Therefore, the development of Bills of Materials is a critical feature of the CMMS. 

GATE has a similar systematic approach to spare part development/material management and handling, which will be covered in a separate GATEKEEPER.

Validate Data with QA/QC Tools

A very effective tool that is used by GATE is the development of a library based on standard maintenance tasks for standard equipment. This tool clearly shows the asset owner the hierarchy for each equipment, the associated spare parts, equipment attributes and PM requirements.  Performing QA/QC using the specialized CMMS tools that are on the market today, such as SAP, can be cumbersome; however, these systems are typically populated by importing data from a simple Excel spreadsheet.

By utilizing these Excel spreadsheets along with Excel UserForms and PivotTables, GATE has developed a cross-platform tool to allow a QA/QC review of the same data that will be used to populate the CMMS. An example of the tool is shown in Figure 2. This tool reproduces the look and content of the CMMS, showing the asset owner what the system will look like, what information is available and what information is lacking. This is accomplished using software that everyone has access to, unlike CMMS programs that require special licensing and an expert to navigate.

The GATE QA/QC cross-platform tool allows the information to be evaluated and validated easily by using a simple platform that the entire team can understand.


Coordinate with the Project & Operations Team

It is recognized that involvement of both the project and operations teams makes a tremendous contribution to the accuracy and efficiency of the CMMS process. Consequently, it is a principle goal to establish open communications and coordination with the operations team at the start of a CMMS initiative. This promotes dialog and feedback from the operations team throughout the CMMS development process. Subsequent buy-in and acceptance of the CMMS strategy by Operations will assist in maintaining and keeping the system updated, which is vital for a successful implementation.


When properly developed, a CMMS tool can ensure that detailed equipment information is readily available, improve reliability, reduce equipment and facility downtime, extend equipment service life through effective and improved utilization of close monitoring, support predictive and PM, and optimize decision making. This, in turn, results in decreased operating expenditures (OPEX) and increased return on investment. A properly developed CMMS system will also ensure compliance with BSEE, Safety and Environmental Management System (SEMS) and RP75 requirements.

Effective CMMS Database Development (PDF for Print)