This article is a little longer than others on this site, but it covers the very important topic of applying basic principles to the use of the database in maintenance computer systems. A lack of understanding of these principles contributes to the failure of some systems and reduces the value in others.
Maintenance computer systems are an example of database applications which provide the ability to manipulate large volumes of information in any number of ways. However, to take advantage of this functionality to create value the process of implementing the system must take into account the principles described here.
These principles are frequently not well understood by maintenance system project teams or system users.
The logic behind database principles is described in this article. For a summarized list of these principles see the “Database Principles List“.
Developing database principles
As the first step the “product” or outputs from the system must be defined, in detail. There are very many of these outputs, including:
– work orders
– work schedules
– purchase requisitions
– purchase orders
– stock issue requests
– stock catalogues
– equipment register
– spare parts lists
– tags, labels
– plant standards (safety, quality, environment, etc)
and of course all reports, Key Performance Indicators (KPI’s), working screens, etc, required to manage these outputs.
One important example is a list of work orders, sorted and filtered as required to manage the maintenance backlog for a department. This example will be used to illustrate the process for developing database principles.
The work order list will be used by area experts who are familiar with each work order to keep control over all maintenance work and to develop work schedules which are in line with the department’s objectives. These experts would normally include the Operations Supervisor, the Maintenance Supervisor and perhaps a Planner (or the equivalent positions in both industrial and non-industrial organizations). For this purpose they do not need to see the details of the work order because they are, or should be, familiar with the work. They need to see just enough information to clearly identify the work order plus the information required to make scheduling decisions.
From the data in the system, the work order records are first filtered to include only those work orders for the department in question. For each work order, the following information needs to be displayed:
- The work order number.
- A short description of the work to be done.
- The equipment location number.
- A short description of the equipment location.
- The date the work was requested.
- The urgency of the work.
- The identity of the group (mechanical, electrical, etc) that will have responsibility for executing the work (although the list may be filtered to include only the work for a specific group).
- (Perhaps, as described later) the frequency of routine preventive maintenance work orders.
- The status of the work order (e.g. is all material on site?).
- Any equipment or plant shutdown needs or other scheduling limitations (e.g. does the equipment have to be operating under full load, producing a particular product, etc). NOTE – in practice, shutdown and non-shutdown work are usually managed in separate backlogs, using this field as a filter.
- The downtime required (if any).
- The number of tradespeople of each skill designation required (including support from other crews), and the number of hours for each.
- Other information which may affect scheduling decisions (e.g. weather, coordination with other work, a need for special equipment such as large cranes, etc).
This information will be displayed on a screen and eventually will be used to produce a work schedule by filtering to include only those work orders which have an appropriate status such as “Ready to Schedule” or “In Progress”. This schedule will be printed and distributed to all interested people, such as supervisors, tradespeople and area operators.
The table of work orders needs to be presented in a format that displays all relevant information in a way that makes it understandable to all readers. For this example of backlog management the 13 columns described above must all be visible, as shown below.
Because printed pages and computer screens have a limited width, the format of each column must be carefully determined. This requires that all other possible uses of the information in each column (field) must also be defined. For example, the “short work order description” will also be displayed on the work order form, on shutdown work lists, safety backlog reports, etc.
In the table shown above, there are two blank columns (14 and 15), because the business process for regularly sorting the backlog into the order of work order target start dates for scheduling has shown that these columns are required without hiding any of the other information.
Lets look at some columns of the backlog list:
Column 1 – Work order number
All that is needed is a unique number which identifies the work, and it should be as short as possible. There should be no need to show the year it was originated, leading zeroes or any “smart” digits.
Column 2 – Work order short description
The format of the short work order description depends on several factors, including
- a) does the maintenance computer support text-wrapping in allof the outputs in which the short WO description will be displayed?
- b) can the field where the short WO description is entered be configured to limit the number of characters?
(To avoid truncated descriptions such as “Please send an electrician to go and repair the ….”)
This leads to the first principles:
Principle 1 – All inputs must be designed to be compatible with all outputs.
Principle 2 – Ensure that all data entry is in a format that is supported by the system’s functionality wherever it is used.
- c) is the equipment location number and description displayed in all outputs where the short work order description is displayed?
If this, recommended, standard is adopted, then the work order short description does not need to include a description of the equipment. So instead of “Repack the south cold water booster pump”, the WO description can be shortened to just “Repack” because “South cold water booster pump” is already shown in the equipment location field.
Principle 3 – Where the system depends on users to follow standards for data entry, these standards must be documented, emphasized during training and always followed.
Column 3 – Equipment location number
It is not unusual to see lists of work which display an equipment location number with no description. It is only a very few people who are likely to recognize these numbers and such reports are of very limited value.
Principle 4 – Wherever a reference number, such as an equipment number, stock number, employee number or work order number is displayed, a readily recognizable description must also be displayed.
There is an exception to this principle. If the equipment location number has a prefix that identifies a department, and there are few departments, it can be expected that everyone close to the operation will quickly learn to recognize their department numbers. Using the example below, the abbreviation for equipment location number 41-1435 “#1 KM L’BOY O’LAP CARR’GE” could be shortened to “L’BOY O’LAP CARR’GE” if the “41” prefix in the equipment location number is always recognized as being “No. 1 Kraft Machine”.
The numbering of equipment locations and stock items is a separate subject to which a special set of principles apply. For more details please go to “Asset ID and numbering“.
Column 4 – Equipment location short description.
To minimize the space required for equipment descriptions, full use should be made of abbreviations while keeping the name recognizable to all the people who will read this information in any output.
However, if the maintenance system has the usual functionality for searching for equipment by description, abbreviations may make this search very difficult. For example, a pulp mill has an equipment location called “No. 1 Kraft Machine Layboy Overlap Carriage”. In their equipment register, this is abbreviated to “#1 KM L’BOY O’LAP CARR’GE”, and this is the only description field in the record for this equipment. Anyone searching for this equipment using keywords would need to know that:
– “Layboy” was entered in the database as “L’BOY”
– “Overlap” was entered in the database as “O’LAP”
and so on.
Searching databases containing only abbreviated descriptions is very unreliable and frustrating, just as it would be difficult to find abbreviated names in a phone book. Where abbreviations are used for reporting purposes (as they should be) then a separate field containing the unabbreviated description is necessary for reliable searching. This leads to the next principle.
Principle 5 – A distinction must be made between “display” fields and “search” fields. Where display fields are abbreviated, the unabbreviated form must be included elsewhere in each record for search purposes.
Where and how this is done depends on the search functionality of the system, but it must be done. If there is only one description field available, then the abbreviated form may be used followed by all expanded search words, and the display fields may be appropriately truncated for display purposes. This requires an additional level of discipline when data is entered. Systems with flexible multi-string, multi field search capabilities greatly reduce these data-entry problems.
This principle applies, in particular, to equipment names and the names of stock items.
(NOTE, searching by key words for equipment and spare parts is far easier and more reliable than the common “drilling down” through a hierarchy, because it is difficult to build a hierarchy that seems logical to all users. Searching is most reliable where a good naming standard has been used and where the system has strong multi-string, multi field [“Google-like”] search functionality.)
For a discussion on the distinction between “search” and “identification” information, see “Naming parts”
Columns 6, 8 and 9 – “Urgency”, “WO Status” and “Shut required”.
These are examples of fields where the person entering the data will normally be required to select one value from a list of possible values (a “drop down” list).
The use of an illogical format for these value lists is the most common problem found in maintenance computer databases.
The basic principles to follow here are that each “characteristic” of a work order (or any other subject) must have its own dedicated field, and that the list of values that can be selected for each field must be designed so that an experienced and knowledgeable person can always select one and only one that applies.
For example, consider the simple example of the characteristics of a set of wooden blocks of different shapes and colours. An unstructured “value list” of block “types” may include “blue”, “square”, “round”, “red” and “triangular”. Obviously, for a red, square block either “red” or “square” could be selected.
In this example there should be one field for the characteristic “Shape” and another field for the characteristic “Colour”, and these fields should be named “Shape” and “Colour”, not “Type”, “Class” or “Category”. The “Colour” field would contain the options “Red”, “Blue”, etc and the “Shape” field would contain the options “Round”, “Square”, “Triangular”, etc. In this way the list can be structured so that one and only one of the possible values will always apply.
The following actual example of a list of 15 “Work Order Type” codes, a mandatory field from which only one option could be selected, shows how the mixing of characteristics can make reporting of little, if any, value.
Consider selecting the correct code from this list for a work order for the emergency capital replacement, by a contractor, of a machine tool that has been the cause of an accident. Would the correct selection be “Capital”, “Contractor”, “Emergency”, “Machine/Tooling” or “Safety”? Each of these options is valid because the list describes a mixture of characteristics. The characteristic described by each value in the above table is shown below. Each of these characteristics should have its own field with appropriate value lists.
For accurate reporting, the meaning of each “value” for each field must be carefully defined. For example, in a work order field for the “Action to be taken” characteristic, if the list includes both “Service” and “Lubrication”, the value “service” must be defined to exclude lubrication service.
In many cases the “process effects” of each value should also be defined. “Process effects” are the way in which the system will manipulate the information to provide the required output. For example, for the “Urgency” field, if “Emergency” is the selected value, the process effect may be that the system will assign a work order number immediately, will allow the purchase of materials for this work order without approval and will include the work order on an “Emergency work order” report to responsible managers.
The principles around these fields are:
Principle 6 – Each “characteristic” of a work order (or any other database record) must have its own field, and the field name must describe the characteristic.
Beware of any field named “Type”, “Class”, “Category”, etc.
Principle 7 – The value list for each field where only one value may be selected must be designed so that an experienced and knowledgeable person will always be able to select one and only one value that applies.
Principle 8 – The contents of value lists must be very closely controlled to ensure that Principles 6 and 7 are not compromised. Value lists should never be open for editing by unauthorized people.
For a list of the fields which should be considered for work orders, suggested lists of values and their processing implications are in the article “Work Order Coding“. We can assist in quickly building value lists for the characteristics that apply to stock items and all other maintenance-related tables.
Columns 5 and 8 – Request date and PM frequency
These are included because the primary purpose of the backlog list is to ensure that all work is considered when jobs are selected for scheduling. While the date of the work request and the PM task frequency are not essential for scheduling, they do assist the supervisors and planners who make scheduling decisions, so they do have value.
Principle 9 – In all displays, show all the information that is of value for the purpose for which the display is intended, and no other information.
Column 12 – Labour skills and hours
There are many ways of displaying these columns, and they are used to a) show the resources required for any work order and b) to enable a list of work orders selected for scheduling to be matched to the available resources.
System functionality will determine the format of these columns. If there are a large number of different skills or resource types, assigning a column or columns to each one may produce a very cramped display or report. There are options, such as a row for each resource required for each work order, or to use logic statements (such as Excel “IF” and “LOOKUP” functions) based on the resource type to generate scheduling information. Some systems lack the functionality required to effectively manipulate tables of information and many users export data from their system to a more flexible and powerful tool, such as a spreadsheet, for this purpose.
NOTE – If the list has been filtered to include only the work for which a specific group (e.g. mechanical) is responsible, all other groups to which support must be provide, or which must provide support must also be shown on the table.
Principle 10 – Understand, in detail, the functionality of the system you are using before finalizing the design of all system outputs.
Principle 11 – Always use the best available tool for managing maintenance information.
Column 13 – Scheduling notes
The purpose of this column is to collect information to assist with scheduling work, such as the required equipment condition, weather limitations or the need to coordinate with other work. Columns (fields) like this that allow the entry of free-format text are frequently mis-used. For example, this column may be used to show drawing reference numbers or instructions to tradespeople because it is convenient.
The only way to control such misuse is through careful training, documentation and frequent monitoring. Misuse of fields such as this will cause errors, in this example through creating difficulty in separating valid scheduling information from other information.
Principle 12 – The use of each and every field, and every value in every field with a value list, must be described in the relevant business process, be included in training and regularly monitored.
Principle 13 – There must be clear responsibility for the management of business processes and the related database functions, and these responsibilities should be included in position descriptions.
Miscellaneous principles
Principle 14 – Anyone who enters information on a form or directly to the database should understand and have feedback on the value of that information.
Do not expect tradespeople (and others) to provide accurate information if they do not understand its purpose and value. It should always be possible to demonstrate to these people how the information that they provide is being used to manage and improve the organization’s performance.
Principle 15 – In Maintenance systems where the majority of Storeroom and Purchasing transactions are for maintenance materials, supplies and services, Maintenance, Purchasing and the Storeroom should share a common system to avoid the complexities and compromises that inevitably result from interfaces.
Principle 16 – Use the database to do things that people can not do.
An excellent example of this was seen in a Finnish plant, where each stock item was assigned a re-order quantity, and also a lower plus an upper re-order point. The upper re-order point was set just above the lower re-order point. The system was programmed so that when any item reached its lower re-order point it automatically searched for all other items from the same primary vendor which had reached their upper re-order point, and included all these items, with appropriately adjusted re-order quantities, on the same purchase order. The result was a 60% reduction in the number of purchase orders for the replenishment of stock.
Principle 17 – As far as possible, enter data once only.
An example, also from Finland, is the integration of work schedule information and time-keeping records. In one plant, the maintenance computer system produced daily work schedules which showed, in tabular form, the work orders assigned to each tradesman, by hour, for the day. The information on the work schedule therefore included:
– the date
– the time
– the work order numbers
– the tradesperson’s identification
– the hours to be worked on each work order
This fields are identical to those required for reporting for keeping accurate records of labour costs against work orders.
The supervisor adjusts the work schedule during the day to record actual progress, unexpected absences and other details. At the end of the day, the he validates the revised schedule with his password, and this provides the necessary time-keeping records without the need for time cards or other entry by tradespeople.
Principle 18 – Do not collect or report any information that can not be demonstrated to create real value to the organization.
It is very easy to use the power of a data base to produce all sorts of “dashboard” reports or KPI’s but each should be very critically examined to ensure that it is creating value that goes to the bottom line. See “Measuring Maintenance Performance – the hazards in KPI’s”
To return to the articles index, click here.
© Veleda Services Ltd
Don Armstrong, P. Eng, President
don.armstrong@veleda.ca