The city of Saint John, located on the Bay of Fundy in New Brunswick, is Canada’s oldest incorporated city. Saint John Water (SJW) operates three water systems and seven wastewater systems, serving a population of approximately 65,000 residents. Like many water and wastewater system operators, city staff was struggling with managing large quantities of water quality and operational data from multiple sources, including a newly installed SCADA system, handheld field meters and internal and external laboratories.
In the spring of 2007, SJW reviewed its data management and reporting systems and completed a requirements analysis for managing its water quality, production and industrial billing information. The analysis documented the processes involved and the tools used in generating and maintaining data and producing reports, the functions they performed, what requirements they met and how they interacted with each other.
It was found that mission-critical data was being maintained in separate paper-based files and desktop computer applications such as Microsoft Excel or Access. For example, one spreadsheet was being used to record microbiological, organic and inorganic analyses results of both raw and treated water while a different spreadsheet was being used to record chlorine readings in the distribution system. Furthermore, water treatment staff were receiving faxes with the microbiological test results from a contract lab, physically reviewing the results to confirm compliance with drinking water standards and then manually transferring the data from the fax to an Excel spreadsheet for further analysis.
The review also noted that the city’s water and wastewater utilities handle two distinct categories of data: 1) water quality data such as lab test results of both raw and treated water, and distribution system chlorine residual readings, and 2) water production data such as raw water pump rates from the two source water lakes, water consumption measurements and the amounts of chlorine and fluoride used at the water treatment plant. A range of city employees, including the water and wastewater manager, the operations manager, the finance department, the water resources and quality department and the city lab generated, maintained or needed access to the raw data of one or both categories.
The conclusions of the city’s requirement analysis made it clear that the city’s old data management methods could be greatly improved in terms of efficiency, accuracy, risk reduction and effectiveness. It noted, for example, that duplicate data entry increased the possibility of transcription error and was also a highly time-consuming endeavor. Significant time was also being spent maintaining multiple spreadsheets, paper files and independent Access databases. The risk for critical data loss was very high because data files were being stored on personal computers in different physical locations and not always being backed up. The dispersion of data in various paper files and electronic tools that had no adequate reporting capabilities also meant that it could take weeks to consolidate, validate and reformat the data for various reports.
Based on these findings, the key recommendation was for SJW to adopt a centralized data storage solution that provided automated interfaces for consolidating data from multiple sources (internal and external labs, SCADA and field readings) and intelligent reporting capabilities.
The analysis also provided a summary of 19 business, functional, user, reporting and security requirements, which included the following:
The ability to historically archive the lab analysis and all parameter readings collected on any time-fixed basis (hourly, daily, weekly, monthly); Production of reports for customer and internal queries regarding water quality, with access to historical data, the ability to graph, analyze, summarize and manipulate the data concerning water quality and water production for presentation and trending purposes and for the Annual Water Report; and The ability to store all the data collected indefinitely.
Finding an Alternative
City staff were reviewing traditional options when the deputy commissioner of SJW discovered WaterTrax at ACE07. WaterTrax provides Internet water and wastewater data management services through what is known as the software-as-a-service (SaaS) model. SaaS is a method of offering software over the Internet, usually at a subscription price, as opposed to traditional software which is paid for entirely up front and installed on a client’s computer. Since the 1990s, the stability and ubiquity of the Internet has allowed SaaS providers to offer cost-effective alternative information management solutions that are industry-specific, hosted in central locations and serve multiple customers via the Internet.
After completing a gap analysis, SJW subscribed to the WaterTrax Service to manage its drinking water quality information in December 2007 and expanded its subscription in May 2008 to include its wastewater systems. SJW chose the WaterTrax SaaS solution because it was specifically designed to manage water and wastewater system data, it required no additional hardware or internal IT support and it met most of their functional requirements, including:
A centralized and consolidated database of all water/wastewater monitoring information that provides simultaneous and easy access to all data by the various city employees;
Direct upload of water quality test results from both internal and external labs, which eliminated time-consuming and error-prone manual data entry; Automatic comparison with water quality standards and generation of alerts for poor water quality, which eliminated the time- consuming manual task of checking every water quality test result for compliance; Ability to produce ad-hoc reports at a fraction of the time previously required; and Up-to-date security features, including frequent and multiple backup, thereby eliminating the high risk of critical system data loss.
In addition, the solution could be implemented (setup and training) in one day and the annual subscription cost meant a lower risk of investment.
Since implementing the WaterTrax SaaS water quality data management solution, SJW has gradually converted old data recording processes to WaterTrax. This includes all contract lab data, internal lab data, daily operating parameters for each facility, daily/weekly results at various locations in the distribution system and information and results regarding disinfection of mains, cleaning and lining of mains, plus customer/citizen requests for water analyses.
Having all data stored in one database has streamlined many tasks, as it necessitated a review and update of existing procedures. It has also resulted in a much faster turnaround time for report generation. Staff at SJW are now comfortable using the software and recognize that it is easy to add new locations, modify and correct historical data and alter log sheets as required.
Data is the foundation for key decision making in day-to-day operations and short- and long-term planning. A proper data management system that is centralized, secure, intuitive, easy to access and use and allows multiple users to simultaneously produce reports and conduct analysis will reduce risk of critical data loss and save municipalities significant time and money in consolidating, validating and reporting out on system-critical information.