Data Integration - Jefferson County’s Journey To Compile, Track, And Report On KPIs


  • Aditya Ramamurthy PMP, ENV SP, Sean FitzGerald PE, Stephen King PE - Hazen and Sawyer

Jefferson County Alabama currently owns, operates and maintains approximately 3,100 miles of gravity sewer, 176 pump stations, and 7 major WWTPs. However, before 1996, the County only owned and operated 570 miles of trunk sewers and 33 pump stations. All other assets were owned and operated by 21 municipalities. A 1996 Consent Decree required the County to take over all of these systems and to eliminate overflows.

Many of these systems had not been maintained properly and were not constructed to proper standards. In subsequent years, the County spent a significant amount of money making system improvements and, with a downturn in the economy went into bankruptcy in 2011.
The significant reduction in capital funding and the need to manage the system through the bankruptcy created a unique situation that motivated the County to take a customized Asset Management approach based on early successes and measuring outcomes to continue to improve the system and address overflows during both wet and dry weather.

Simple data analytics reveal basic insights; more sophisticated analytics, applied to data that has been pooled into a “data lake” with data from external and enterprise sources, unearth deeper insights that will help water and wastewater utilities optimize their performance.

Because of the growing volume, complexity and strategic importance of asset management data, it is no longer desirable or even feasible for each departmental unit/division/function within a utility to manage this data by itself, or build its own data analytics capability. To get the most out of the new data resources, utilities are creating dedicated data groups that are potentially embedded within the core asset management program team that consolidate data collection, aggregation and analytics. Therefore, an additional responsibility of the asset management program is for making data and insights available across various organizational functions and business units.

Recently, advances in technology have revolutionized data and performance reporting in that users (with limited IT development expertise) can perform data mining and develop high impact visuals for performance reporting.

Jefferson County has implemented Microsoft Business Intelligence (BI) to track and report key performance indicators.

• Eliminates the reliance of core IT developers to develop and manage reporting frameworks as BI is now integrated with common applications such as Excel putting the non-IT user in a position to perform complex data analysis and develop visualizations
• Significantly reduced development cost and level of effort
• Through the concept of data “lakes”, data models may be constructed using data from various sources (CMMS, GIS, SCADA, project management, financial and customer information systems) relatively easily
• Eliminates the extensive costs and the need for complex and disparate system integration that is typically required to connect data for effective performance reporting
• What would take months to develop high impact visualizations can now be developed in hours or weeks.
• Completely transferrable to mobile devices for mobile use at meetings/workshops.

The setup of the program and the prioritization process coincided with and assisted with the removal the County from Bankruptcy. The result was a detailed plan of spending, which included specified spending in the collection system, rate increases and debt service payments. With the County out of Bankruptcy and a prioritized program framework in place the County needed to be able to implement their CIP without the ability to hire new staff. What was needed was a robust program management plan with strict controls, key performance metrics, periodic review, and robust schedule management. Multiple Key Performance Indicators are in place, to ensure both effective implementation of the CIP, and the collection system performance. This system promotes the goal of effectively managing projects, as well as ensuring that the right projects are selected to minimize SSOs. Monthly program reports track key performance indicators. This presentation and paper will cover the key aspects of the development and implementation of robust data analytics. Jefferson County has implemented Microsoft Business Intelligence (BI) data analytics tools to track and report on the performance of KPIs.

For more information, please contact the author at

Hear about new publications with our email newsletter

We will never share your details with anyone else.


Newsletter Newsletter

Horizons Fall 2017 (pdf)

Horizons showcases significant water, wastewater, reuse, and stormwater projects and innovations that help our clients to achieve their goals, and can help you achieve yours. Articles are written by top engineers and process group leaders, demonstrating and explaining the beneficial application of a variety of technologies and tools.

View previous issues »