- Infrasync Technology Services
- Posts
- Infrasync Newsletter #21 - Did you Misplace your Utility data? How Embarrassing - Databases, Data Analytics, and Self Management for Water Utilites
Infrasync Newsletter #21 - Did you Misplace your Utility data? How Embarrassing - Databases, Data Analytics, and Self Management for Water Utilites
Ways to make sure you never lose the data you paid for
Andrew Swirsky
June 27, 2024
Has this ever happened to you? You just had a great project deployment, the vendor give a great job with exactly what you needed. things went great for years until the contract ended and you never gave it a second thought. Then one day you went back to grab some data and it was all gone?
You email the vendor asking for a copy of their data, they give you the runaround, they admit they don’t have it handy, or even worse the data is there but it’s locked behind an outrageous data collection and retrieval fee! It can feel like your data that you already paid for is being held hostage until you pay up to get it!
Utilities today are making a choice, whether they realize it or not. They are now in an age of digitalization and are either taking steps towards or away from control of their data. This means the focus for utilities is changing. They are moving away from individual software or function management into data or enterprise management.
Historical vs Data Driven
Historical Approach | Data Driven Approach |
We contract out our water model as part of the master planning contract. We trust them to look at all the model components, set it up correct, and make the engineering recommendations | We own our own water model and send data to firms for master planning. When they find issues, they work with us to update the underlying data, so their engineering recommendations are more accurate. |
We have all our plant controls under contract with a SCADA provider. It works well and whenever there are new instruments, we just call them and they handle it. | We do all our own SCADA maintenance and minor adjustments but contract out major projects and integrations |
We value GIS so we contracted out a firm to take all our record drawings and host our GIS system on a website we can access. | We control all the GIS data and self-host. We contract out major data additions and updates but self-manage the data quality and maintenance. |
Our pipeline conditions programs we just contract out every 5 years we mostly accept their recommendations and don’t use the underlying data. | We have our own asset management database and use contractors to inspect assets and provide us the data. We then use that data with multiple programs and engineers so they can make the best recommendations for pipeline or facility replacements. |
Much of this is because water utilities are increasingly recognizing the value of data-driven decision-making. By taking more ownership of their data in house, they can set up utility owned and managed databases and analytics. Very commonly a utility will want to do an initial internal data review or evaluation to see what they really need to invest time and resources into.
This empowers utility staff at all levels to access, analyze, and interpret data without needing advanced technical skills. This democratization of data can lead to more informed decisions, improved operational efficiency, and better service delivery.
You just need two things to get started as a utility. A database and a data analysis tool
Internal/External Database
This can be self-hosted by the utility, cloud hosted by one of the big providers such as AWS or Azure, or even contracted out to a specialty utility software provider. Just because a utility used a vendor doesn’t mean they lose control, but they do need to understand and have primary or owner access to the database and resources.
Costs for utilities ranges but a cloud hosted database of 1 TB and a SQL data structure ranges from $150-300 a month. The good news is that many cities and utilities already have an IT group that is familiar with this approach or at least knows where to start.
Data Analysis and Dashboarding AKA Business Intelligence BI tool
This is typically an off the shelf or specialized utility specific software. This provides powerful data visualization and business intelligence tool that integrates with a variety of data sources. Such as how does the water meters flow vary with SCADA pressure sensor data. Is there a relation? With a BI tool this provides the ability to bring in different data, compare it to other sources, and make decisions as a team. There are typically a few standard dashboards set up, but users also can use a drag-and-drop interface and customizable dashboards to make their own single purpose tools.
Costs vary but a license for Microsoft Power BI is typically $10 a month. So, if you have 10 people in your organization who need full access (not just viewing) than expect a monthly fee of around $100 or maybe less depending on your existing Microsoft agreement. Other options such as Looker, Tableau, or others can be used as well. The real costs here are the talent and hours required to initialize and set everything up for the utility, not as much the reoccurring costs.
ALTERNATIVE - Outsourced Hybrid Approach
Just because a utility can set it up all in house doesn’t mean they need to. There is a hybrid approach that combines the database and analytics together. This is typically a specialized vendor or service that provides both of these to the utility. There are large providers such as Xylem, Autodesk, Bentley, ESRI, or others that provide solutions for this. This can be a great option for less resourced utilities or for specialized analysis such as digital twins or live models.
The off the shelf tools from Microsoft or others are agnostic to your data source and what it’s showing. The water specific providers know and understand specific issues such as water loss, sewer overflows, pump mechanical efficiency. Many utilities utilize this for a specific problem or focus area at the utility as part of a larger program or initiative. The downside with this approach is many programs’ costs are comparable to another full time employee or more. Plus can be difficult to exchange data with other 3rd party providers.
Practical Application
When looking at vendors and resources ask a few questions including:
Where does the data live and who can access it?
Can we automate a data dump or connection to a utility owned database?
What is the quality of the data and what reporting will be provided with it?
If things don’t go well, what does an exit from the contract look like data wise?
If things go well, what does an expansion of the contract look like data wise?
It’s best to start with a direct use case and a simple structure. Find a specific use or need in your utility such as monitoring water loss, sewer overflows, or something else when live modeling isn’t as needed but it’s mostly focused on what happened based on existing data sources. This is a tentative data architecture for a water management database and analytics.
Water Monitoring Data Flow
Conclusion
Self managed data holds immense potential for water utilities, enabling them to harness the power of data to enhance operational efficiency, improve service delivery, and make informed decisions. By adopting the right tools, defining clear roles, and fostering a data-driven culture, water utilities can navigate the complexities of modern data landscapes and achieve significant operational benefits.
It can be a temptation to make decisions based on software features. It’s healthier in the long term to make decisions based on data governance.
If you are exploring this as a utility, there are several utility specific options available. We are vendor agnostic in that we get no referral fees or incentives to recommend specific vendors over others. Many utilities like to take a methodical approach of Evaluate – Design – Implement – Maintain. There is a longer breakdown on our website here, just scroll down a bit to see the approach
Cost Assumptions Notes:
For a utility with 50,000 sensors collecting data at 15-minute intervals (4 columns of data) and 1,000 sensors collecting data at 15-second intervals (10 columns of data), the total estimated data generated over a month would be approximately 137.3 GB. This calculation assumes each data point is stored as a 64-byte value. This can be hosted for $100-200 with many cloud database providers. This may sound like a lot of sensors but keep in mind the water meters themselves create 80-90% of that data volume.
Know someone who could benefit from this info? Forward this newsletter to them!
Make sense to connect?
Are you working to help your utility or technology company take the next step forward? If you want to talk through a challenge or share something interesting your team did please shoot me a note at [email protected] or schedule a utility technology review here.