Big Data: Backlog Analysis



The benefits of data mining in equipment asset management can include the ability to better manage resources responsible for the maintenance of the assets. A clear picture of what the planned workload is for the year, what has been completed to date and the current and projected backlog are essential to making accurate staffing decisions.

The data underlying this information is found in the CMMS or work management system. It is embodied in the PM program definition of projected tasks, labor hours, last performed dates and intervals. Another important key is the connection between the tasks and the assets. The monthly planned workload, the information deliverable, can be formed and provided from the specified data. An awareness of how well the crews are doing with respect to completing the planned work is also needed. The data supporting this information is found in the work order or work order history components of the CMMS. The data in the CMMS will become useable when the estimated and actual labor and the target and actual completion dates for the PM work orders are properly and consistently entered.

The data for this example required the creation of a somewhat involved SQL union statement that joins projected PM work order information with existing work order data to provide a complete picture for the entire year. The good news is that once that query is established, it can be used until the structure of the data is changed.

Figure 8 is an example of the fact that there is more data in the data system than can be conveyed to the analyst by those requesting information and initially specifying the criteria.


Figure 8
Figure 8 shows a preliminary attempt at providing the labor hours by month based on the estimated hours in the planned work order and the actual labor hours reported in the completed work order. The obvious problem with this early June graph is that the actual hours for January overwhelm the rest of the data. This turned out to be due to a blanket work order that is opened in January and used for the entire year to capture hours for certain non-labor activities. Once the criteria are adjusted to address that issue an improved graph based on "cleaner" data provided the work order count view in Figure 9.


Figure 9
This view provides a look at progress against the planned work. The "All" (dark red) columns represent the total planned work orders by month. The "FINISHED" (blue) columns represent work that has already been completed. We can see that some work has been done that had target completion dates much further out in the year than the current early June date of this graph. The graph also makes it clear that there is an issue with completing planned work. The extent of the issue can be seen more clearly by calculating the rolling sums for the total, completed and pending labor hours and plotting them on a line graph as shown in Figure 10.


Figure 10
For this large organization it is clear that a significant backlog of about 15,000 labor hours has accumulated as of the June 1 date of the graph. For those managing the maintenance program, this is a clear indicator that the projected work needs to be reviewed and staffing levels assessed. Again, this information prompts one to ask many questions. Is the maintenance plan optimized for resources and reliability? Is the work that is being delayed or cancelled related to critical equipment reliability issues?

Answers to such questions are easy to obtain IF the work to understand the equipment, its failure modes and the consequences of failure has been done and incorporated into the data system through the use of equipment criticality classification and failure mode and cause coding of work orders. Without those, the visualization of the risk implied in the backlog is more difficult to discern. There are other understandings that the information "client" may desire from this data. The data could be reduced further to reflect the backlog in terms of "full time employees" (FTE) needed to work off the backlog by the end of the current budget year. Very likely a breakdown of the data by work centers, departments or geographic regions would be helpful to those determining the course of action and certainly to those who are managing the areas mentioned.

A key to the data mining investment is to view it as an investment. Once this kind of backlog analysis has been established, it can be generated routinely with very little effort. The staff that provided this valuable management tool can move on to creating the next one, or upgrading others where data improvements or clearer question definitions have made that worthwhile.

Next: Data Mining and the Business Process.
Back to Abstract.

Back to Services.