Public Works Department Merges Data into a Central Management System

By Jim Brandley, City of Everett, Washington

Reprinted courtesy of Public Works Magazine, March 2001

Since the mid 1980s, the City of Everett, Washington Public Works Department (CEPWD) has been using computers to help keep track of its business. Our department needed all the help it could get because it was responsible for the operation and maintenance of the water, wastewater, drainage, and street systems for a 50-sq mi area. The department’s 220 employees serve over 400,000 people. Like most public works departments across America, when we began using computers, we used individual programs for targeted tasks. By the early 1990s, the department was using a DOS-based maintenance management system that had several modules: water, sewer drainage, work order processing, and warehouse. Three different locations maintained individual databases, and these databases could not be integrated with other applications.

For instance, when customer inquiries came into the department, they were logged into one application, but that information would have to be typed into another application if a work order was issued as a result of the inquiry. The multiple entries were repetitive and prone to data entry errors. Furthermore, the system did not allow a customer inquiry to be tracked from the time it entered the department until it was resolved.

A NEED FOR CHANGE
As the department’s work load grew and computer technology advanced, it became clear that CEPWD needed a centralized approach compatible with Microsoft’s new Windows® operating system. In addition, we were beginning to use a geographic information system (GIS), and we were having some problems because the data files were so large. So, by the mid-1990s, we began to investigate alternative solutions.

The most challenging aspect of selecting the best solution was the early planning. If one is not able to define expectations exactly, chances of success are minimal. The biggest single issue we had to decide was whether or not we wanted to retain total-time tracking within the maintenance management program.

For many years, CEPWD tracked each maintenance worker’s time in the maintenance management program. This included wrench-on-bolt time, training, safety, as well as sick leave and vacation. Every two weeks, the information was exported to the city’s payroll system and used as a basis for paychecks.

FINDING HELP
We went through a lengthy selection process before hiring RJN Group, Inc. (Wheaton, IL), an engineering and information technology firm. During the past ten years, RJN has established computerized infrastructure management systems (CASS WORKS®) for a wide variety of municipal and public agency clients, such as the Sewerage and Water Board of New Orleans, Miami-Dade Water and Sewer, Orange County (California) Sanitation District, and Anne Arundel County (Maryland) Department of Public Works.

In addition to having a proven track record, RJN impressed the selection committee with its flexibility and willingness to modify its basic programs to meet the needs of CEPWD. The committee discovered that some companies wanted to sell what they had, rather than what CEPWD wanted.

PROGRAM GOALS
CEPWD’s Implementation Committee decided to modify RJN’s software to meet our business practices rather than change business practices to match existing software. We wanted to have a single database that any employee could access. This would mean that information would only need to be entered once, yet any project or customer inquiry could be followed from beginning to end.

CEPWD also chose to retain total-time tracking capabilities of all work within the new computerized maintenance management system (CMMS) program. This is important because it allows the department to keep all time-related information in one database. As a result, time records can be queried to quickly determine a wide range of relationships, such as the percentage of an individual’s, crew’s, or department’s time spent on different tasks. This information can be used to assess staff utilization, identify staffing needs, and report maintenance accomplishments.

In addition, we wanted the new programs to tie in seamlessly with the graphical information system on which we had been working. RJN started working with the modifications in 1996, and it became clear that the programming tools for the Windows operating system, which was new at that time, were not stable enough to function at an acceptable level. As a result, we delayed the conversion while the development tools caught up with the emerging technology.

IMPLEMENTING THE PROGRAM
Our department worked closely to RJN to customize and implement its programs. The customization had to be performed and tested off-line because we did not have the additional staff required to run the old and new programs concurrently. The heart of the system is the database that contains all of the raw facts about CEPWD, and the program that describes the relationship among all those facts.

This relational database contains facts about everything from the pay grade of each employee to the size of every pipe fitting in the warehouse. Not everyone in CEPWD needs or wants the same information, so eight individual modules of CASS WORKS (Warehouse, Plant, Water, Sewer, Storm, Pavement, Physical Inspection, and Image) were customized and tested to make data entry and retrieval of information in the relational database easier and faster. After the database and its operating programs had been tested thoroughly, a day and hour was chosen to take the system live. In November 1998, RJN personnel worked through a weekend and part of the next week at CEPWD headquarters to convert some 100,000 items of information in the old system and move that information to the new system. Although a few problems were encountered, all in all the switchover went extremely well.

THE CURRENT RESULTS
The CASS WORKS system has been operational on a daily basis for over two years. We still have a few challenges, but overall, the system is working well. It is certainly convenient to have only one database to maintain. In the past, each module had its own database, and we maintained totally separate systems at three different sites. Now, no matter where you work, you can access the central database.

We have accomplished all our initial goals. Our system is running with a graphical user interface, and a project can be tracked seamlessly from the time it enters our system until it Is completed. We have eliminated duplicate data entry and have merged the GIS into the maintenance management system. We are able to record and track all time records within the new system and have full integration of that information with the mainframe Cobol payroll system.

A major benefit of the centralized database is the ability to review every detail of any job in a single program. Details include every physical item and every minute of time required to complete each project. In the past, reports had to be pulled from several different programs and merged manually.

Accurate cost records are certainly a benefit. CEPWD bills its customers between $400,000 and $500,000 each year for work such as connecting water lines and making repairs for which customers are responsible. In the past, we had to use average rates for many functions such as heavy equipment operation or utility labor. Now it is possible to know exactly who worked on a job, their classification, pay rate, and hours worked including overtime.

The longer the computerized management system is used, the more asset history we will accumulate. This history is extremely important to crews assigned to a project. Before starting work, the crew can find exactly what has been done to that asset in the past and what materials were used.

THE FUTURE
Now that we have stability within the new system, we are looking for ways to improve the way we access information. Perhaps a browser-based map viewing program would help get maps to field crews faster. We may be able to improve planning and tracking of capital improvements projects. Although the currentsophisticated system puts us on the cutting edge of this technology, we anticipate it will evolve, so we are working to keep abreast of new technology.

Without computer software and hardware, it would be much more labor intensive to track everything in a typical public works department. At the same time, computer technology is moving so fast it is challenging to identify and track applicable technology. The PCs now at CEPWD are hundreds of time more powerful than those we started with, just a few years ago. Without upgrading, CEPWD would have fallen behind in managing assets. With the upgrades, and a partner like RJN, we will be able to manage our maintenance projects and assets more cost-effectively.

<< Back