Trinnex recently conducted a webinar on building vs. buying inventories for utilities in the US. The speakers, Mark Zito (GISP, CTBME) and Jamie Persino (GISP), shed light on EPA’s requirements regarding the Lead and Copper Rule Revisions (LCRR) and service line inventory and provided an overview of the data management system (DMS) options. Here are the details.
Why the need for a data management system?
In its essence, a data management system provides multiple parties with “a centralized view of data and free[s] applications and end users from having to understand where data is physically located (Techopedia).” A DMS can make compliance and data management easier and, as a purpose-built solution, helps satisfy a set of needs and solve associated problems by:
- Providing easy access to pertinent data through a friendly UI
- Analyzing and mining data
- Visualizing data
- Simulating outcomes
- Catering to a specific audience to improve decision-making
This helps utilities improve productivity, reduce errors, strengthen operational efficiency, reduce complexity, increase business intelligence, and enhance collaboration.
A DMS acts as a single access point by:
- Working as a one-stop-shop for utilities’ needs
- Operating as a single source of truth
- Enabling knowledge transfer, data sharing, and collaboration
No one size fits all DMS
Utilities can choose from dozens if not hundreds of DMS options. But no perfect boilerplate exists for compliance. Each utility has different challenges, assets, processes, and data, and its own path toward LCRR compliance. System administration presents another challenge for utilities. Managing integrated data sources is tricky given that data sources are not static. Data platforms change, APIs change, technology changes, and utilities seldom have the knowledge and resources to manage all of these.
It takes a tremendous amount of time, money, and dedicated resources to create a DMS that keeps up with the times, leverages new technologies and methods, maintains interoperability and integrations, and doesn’t require experts to use or maintain.
EPA’s LCRR requirements and recommendations
The new Lead and Copper Rule — intended to better protect communities from exposure to lead in drinking water — was announced in December 2021 and will take effect from October 2024. As per the rule, utilities must develop and submit a service line inventory to their state primacy agency.
The inventory must include details of all service lines — public and private side — regardless of if they are active, going to an abandoned home, irrigation lines, fire lines, and so on. The details include specifics such as plumbing components, interior plumbing, lead status, solder, type of building, material diameter, installation date, material source, verification method, and so on.
Utilities must also make the inventory available to the public, especially if their population is over 50,000.
Hear Mark explain more in this clip.
Pathways to LCRR compliance
The LCRR compliance journey looks different for everyone, but the basic blueprint starts with an inventory, field verification to confirm the presence of lead, customer engagement for self-reporting or field verifications in homes, machine learning to optimize the identification of unknown service line materials, sampling to identify lead exceedance, and the ultimate replacement of lead service lines.
Here are a few options to compile data to satisfy the EPA requirements:
- Building GIS-based inventory: compile, format, transfer, and curate into a GIS
- Building Excel-based inventory: compile, format, transfer, and curate into EPA-provided Excel spreadsheets
- Building GIS- and Excel-based inventory with add-ons: leverage those two systems in tandem
- Buying an all-in-one platform: buy a purpose-built solution
Building GIS-based inventory
GIS is a great place to start, especially if your organization is already using GIS as the source of truth for your physical assets such as water mains, hydrants, valves, and meters. If you manage your service lines, typically the utility-owned only portion within the GIS, then building off the GIS is something to consider. To comply with the service line inventory requirements, you will likely need to do one or more of the following:
- Add service lines to your asset inventory
- Modify the schema to capture additional data such as connector material, presence of lead solder, premise plumbing, and so on
- Include the private side of the line in the inventory
- Add a field inspection form with a QA workflow step before updating the inventory. This can be a manual step, but for large inventories, which will be time-consuming
- Add a public engagement form for self-reporting
- Publish the data to a centralized and accessible server and set up role-based access so multiple users can contribute
- Build a reporting template; the ideal data schema will not be a flat table with fifty columns that matches the EPA’s template
The list continues, and implementation of all these will need a skilled GIS expert.
Building an Excel-based inventory
Several states have already adopted the EPA template (with minor revisions) for reporting, which is a good place to start if you’re not sure where to begin. Using Excel can work if your utility is small and has under 1,000 service lines. If you go beyond that, it will be a challenge to manage, especially with multiple people contributing to the development.
Pros of an Excel-based inventory:
- The schema already exists with all the fields you need, and it includes drop downs (data lists)
- Most people are comfortable working with Excel. It’s highly flexible
- You’re in a good shape to submit in 2024 if you use this template from the start
Cons of an Excel-based inventory:
- Multiple users can be a challenge, even when hosted on SharePoint or OneDrive
- Data collected in the field will need to be manually added to the spreadsheet
- It is prone to data quality problems even with data validation built in. Copy/paste can also go wrong
- Hosting a public map is technically doable through PowerBI, but not easy
- Microsoft forms can be used to engage the public, but you’ll need to get that data from multiple sources, so there goes your single source of truth
Buying an all-in-one platform
The final option — buying a purpose-built commercial off-the-shelf software platform, such as leadCAST, can help you do it all. All the components needed to manage the inventory development including machine learning, sampling program, replacements, and public engagement are included right in the package. An implementation phase is required to get your data prepped and loaded into the system, but once done, you’re off to the races.
Buying an all-in-one solution brings home several advantages:
- Data schema is pre-configured
- Field tools are included
- Access to your team and the public is pre-configured
- Reporting is built in
With only 18 months to go until the inventory is due, investing time into protecting public health is crucial than spending it on configuring a data management system.
The different stages of LCRR compliance progress
Utilities are at different stages of the LCRR compliance journey — early, middle, and advanced. Which option to adopt depends upon how far ahead you are in the journey.
The Early Stage
At an early stage, you have some data. Not all is digital. You have GIS, but no GIS layers for customers. You have minimal data in our billing system. Most of the service connection information is in tap cards, and you use a third party for sampling. You have data out there, but it's in different places. This is where a single source of truth comes in the picture. If you are only now starting your journey toward LCRR compliance, you must think, ‘How could I benefit from an all-in-one system?’
The Middle Stage
You're going to have your day-to-day management operations going on — inspecting hydrants, changing out meters, and so on. But you will still need to configure your GIS, get the billing system integrated, and add those extra fields — which will take time.
With leadCAST, you can load all the data at once, and be ready to tackle challenges head on.
The Advanced Stage
At this stage, you already have different systems in place — CMMS, billing, GIS, and so on — and they're all integrated. You have a good start on our inventory and have GIS and IT teams to brave adversities. Compliance, in this case, is a breeze.
In this scenario, the first thing you probably want to do is summarize that inventory — how many unknowns do you have? If you have a lot, then you're probably a candidate for machine learning. In this case, you can get leadCAST Predict.
Another thing to look at would be sampling. Are you sending people out in the field? Is that taking considerable amount of time? Do you have enough people?
If you have 28,000 lead lines, manually sending kits out to all 28,000 homes will be a challenge. Getting an automated system, such as leadCAST, is a solution here. leadCAST’s drop ship facility will help you send a sample to people's homes, then back to the lab, and then right into the system.
Helping you make a choice: Resources for your LCRR compliance journey
Irrespective of the stage you are in, our Buyer’s Guide will take you further in depth on the question of building versus buying the solution. This document lays out all the options and lets you make an informed decision to best meet your needs.
We also have a bi-weekly live demo of leadCAST, where you can see what leadCAST is about, check out its newly released features, and compare it to other solutions you’ve seen recently. Do visit our website to learn more about our products and reach out to our product experts. We are here to help you reach the next level of digital-first resiliency!