Harnessing the Power of Data at Metro Water Recovery

Wastewater treatment plants around the world are utilizing the power of data to reduce costs and improve operations. However, working with data at large organizations like Metro Water Recovery presents numerous challenges. Employees in different departments may not be aware of what data is available or what calculations are being performed across the organization. This can result in duplicate efforts or failures to integrate valuable data into important process and business decisions. Even if calculations are published and available, the logic and assumptions behind them may be hidden or undocumented. ​

Across Metro, there are many opportunities to improve data utilization, and Metro’s Technology and Innovation Department (TID) is actively working on this effort through the Data Utilization and Integration Focus Area within the Continuous Improvement Program. One of the goals of this program is to standardize, document, and automate calculations and to make these calculations available to data users throughout Metro.

Streamlining Data with Alteryx

In 2020, TID staff piloted a software product called Alteryx that specializes in integrating data from various sources into a visual workflow environment where calculations are performed using drag-and-drop tools. Calculation results can be written back to the original sources, downloaded to a local file, or published to a visual analytics platform such as PowerBI or PI Vision, which has become a vital dashboarding and trending tool for our operations and wastewater process engineering staff. This approach allows data users to easily understand where data comes from, how calculations are configured, and what assumptions are being made.

One of the key benefits to using Alteryx to automate this report is the consolidation of the many manual steps and the ability to ensure that the calculations are documented in a visual workflow that can be followed and understood by anyone that inspects it. The Alteryx Discharge Monitoring Reports (DMR) workflow dynamically imports data from Labworks, PI Vision, ODS, and SharePoint. The required calculations are then performed and mapped to the standard report templates required by the Colorado Department of Health and Environment (CDPHE). 

The available built-in tools allow Metro staff to blend data from different sources and even predict data trends using machine learning.  Custom program scripts can be directly inserted for more specialized calculations. Once the calculations are reviewed and approved, they can also be used to provide data to consultants for studies and designs or by operations for process assessments, ensuring all data users are using the same data across the organization.

One of the test cases for this software is auto-generation of the DMR that Metro is required to submit to CDPHE as part of our discharge permit on a monthly, quarterly, and semi-annual basis. Currently, Environmental Services Department staff spend significant amounts of time preparing these reports, using Excel as the primary tool. This method has manual aspects that are very time-consuming due to the need to pull data from several databases across the organization, and it requires very diligent and onerous quality control procedures to ensure that the data provided is correct. The formulas that are necessary to process the data in Excel are long and difficult to follow for anyone who is not an Excel aficionado. An example of the part of the workflow that calculates monthly statistics for the DMR is shown in Figure 2. While it appears complicated, it aligns all efforts and assumptions related to these calculations into a single streamlined and documented workflow. The auto-generated DMRs are nearly ready for roll-out as the team works through final calculation verification. Next, staff will start working on applying this tool and the documentation criteria established during this test case to the reporting required to support our biosolids land application program.

Outcomes and Next Steps

Some data produced in Alteryx is already integrated into PI Vision pages and is available to employees across Metro. TID will continue to explore opportunities to streamline and optimize data workflows throughout the organization. This includes auto-generation of standard reports, developing online analytics to provide real-time information about the wastewater process and other key business processes. TID can also develop advanced models to predict events before they occur so we can make timely and informed decisions across Metro.

Note: This story was originally published in December 2021 issue of the Innovation Quarterly Report, a document provided to Metro Water Recovery’s Board of Directors. Follow our Currents blog for more stories like this in the future!