Configuring Trimble Cityworks Data for Water Loss Audits

Water loss audits are vital for any water utility. The City of Denton leverages Trimble Cityworks to achieve more accurate reporting with improved calculations and better data.

The City of Denton’s water utilities department maintains 2 water production plants, 2 pump stations, 646 miles of main, 5,452 hydrants, and 41,459 meters. Despite the challenges of aging infrastructure, increasing regulatory requirements, and inadequate resources, the city strives to keep water safe and affordable.

Why We Audit

Producing 6,476 million gallons of water per year requires an accurate management of water use. Each year, the City of Denton conducts a water loss audit, reporting how much water was produced and, of that produced water, how much reached our customers.

The water loss audit is a required annual report for many water suppliers. It’s a helpful and necessary report in water loss control, assisting in increasing water use efficiency, fiscal responsibility, system health, and long-term sustainability of water resources.

Our 2019 water loss audit revealed an Infrastructure Leakage Index (ILI) of 2.13 continuing within the 1<3 range since 2003. Indicators of our fiscal and system health calculated at 3.09 percent for apparent losses and 9.40 percent for our real losses. The audit is a driver for our meter testing and replacement programs and provides results from our operations and maintenance programs.

Improved Calculations

With system heath playing a pivotal role in the distribution system, we identified an area for improvement by accounting for system heath through an improved calculation of components in the water loss audit. Most notably, we improved the method in which we approach water losses that result from breaks and leaks. In years past, the audit relied on a catchall calculation—a one-size-fits-all approach—to find the gallons lost. With better identification of the water coming from breaks, the utility now has a tighter audit and more precise accounting of where all our water goes once it’s treated.

Denton’s field service workers record the main break events as they happen throughout the city, tracking the details of each break within Trimble Cityworks work orders. Customizations and refined data collection methods are associated with water loss within Trimble Cityworks by implementing a set of new custom fields such as dimensions, pressure, and estimated leak times.

We needed a way to run complex calculations based off data input by the field user while also having the data readily available for reports and dashboards. So, we programmed the calculation scenarios into a trigger through SQL Server Management Studio that pulled the custom field data captured through Trimble Cityworks work orders. As a result, we can calculate water loss volumes both individually per break and summed up for reporting purposes by month or by year.

We identified the necessary data requirements and created the fields in our main breaks work order template. Several fields such as failure type, condition, dimensions, operating pressure, and estimated leak time were included—some of which were set as required—ensuring that all data involved in the water volume calculation could not be left blank. Otherwise, blank values would yield a null in the water volume calculation and disrupt the process. The calculation scenarios prompted us to refine our main breaks into separate categories: hole, joint, split, straight-around, and other, giving us additional improvements over our past methods.

The scenarios were published by another municipality in Texas in which we retrieved from the Texas Water Development Board’s website and built into our process, with the referenced SQL trigger supplying the heavy lifting of the process. Its result was a “calculated total gallons lost” custom field on the work order that populated the calculated water loss volume from the main break once all the data points were entered.

More Accurate Data

Since the improvements, we began to see a significant difference in the quality of data. Our old ways of measuring lost flow were proven to be less accurate. For example, the calculations for 2018, using our one-size-fits-all approach, reported 2.8 million gallons in lost water. Within the first week of using the improved calculation, we captured a main break of 484,805 gallons. With that one break, our utility had already met one-sixth of the loss estimated from the previous year. Obviously, not every break is going to be that high, but it demonstrated that we could calculate our losses with a higher degree of accuracy. We reported 40 million gallons of lost water for our 2019 year with no significant difference in the number or type of leaks.

We were able to build on top of the platform provided by Trimble Cityworks, which allowed customization and the ability to improve our operations and service commitments. The results spoke volumes—37 million gallons, to be exact.

Water lost? More like water found!