Data integration for infrastructure asset management in small to medium-sized water utilities

Water utilities (WU) manage a large number of assets (e.g., pipes, valves, pumps), many of which were installed several decades ago and are therefore reaching the end of their expected useful life. These deteriorating assets may lead to problems such as leakages, burst pipes and service disruptions, which increase the systems' maintenance requirements and operational costs. WU face the challenge of keeping their systems operational, efficient, and reliable to provide good-quality service. Therefore, a WU should adopt infrastructure asset management (IAM) processes not only to plan asset interventions (e.g., repair or replacement), but also to ensure its own sustainability (Alegre & Coelho 2011).

According to ISO 55000:2014, asset management is the coordinated activity carried by an organization to realize value from its assets involving a balancing of costs, risks, opportunities, and performance benefits. In the context of water services, the concept of IAM is adopted instead and defines the framework to pursue and achieve sustainable infrastructure, in which utilities manage their assets and the associated performance, risks and costs over the lifecycle (Alegre & Coelho 2011).

In this context, many methodologies for urban water IAM can be applied. In Portugal, the National Water and Waste Services Regulator (ERSAR) has published two technical guides, one recommending a methodology for water supply infrastructure and the other for wastewater infrastructure. Both present the same methodology organized into three levels of management and planning (i.e., strategic, tactical and operational). They are structured as a loop comprising the following stages: (i) definition of objectives and targets; (ii) diagnosis; (iii) plan production, including the identification, comparison and selection of alternative solutions; (iv) plan implementation; and (v) monitoring and review (Alegre & Coelho 2011).

A key aspect for a WU in the application of any IAM methodology is having reliable data about their assets. These data can be roughly divided into two groups, namely: (i) cadastral and operational information (e.g., components' technical specifications, location, condition and information about failures in the various assets) and (ii) billing and account information (i.e., network-related maintenance and operational costs, energy and personnel costs, amortizations and interest, revenues, among others) (Alegre & Coelho 2011). Therefore, good data is the foundation for the application of any IAM methodology.

In Portugal, the provision of water services is divided in two: bulk activities, called ‘high service’, comprise water abstraction, treatment, and transmission; retail activities, called ‘low service’, comprise water storage and distribution. The responsibility for high service is shared between the 308 municipalities and the state through the public holding company group Águas de Potugal (AdP). The low service is exclusively the responsibility of the municipalities, which can manage the service either directly or indirectly through concessions. According to the 2018 Annual Report on Water and Waste Services, there are 15 high service utilities providing water to 73% of the population, and 306 low service, mostly managed directly by the municipalities (i.e., 202 ≈ 66%) representing about 52% of the population (ERSAR 2019). Municipalities can create municipal services that operate independently, or municipal companies, or intermunicipal services with other municipalities. So, the diversity of WU is pronounced, due to the institutional and organizational framework and the geographical context, among many other aspects (Amaral et al. 2017).

Over the last few decades, many municipalities have made investmented significantly in implementing information systems (IS) to address the increasing complexity of their daily control, operation, management and planning tasks (Halfawy 2008), namely, enterprise resource planning system (ERP), geographic information system (GIS), customer relationship management (CRM) and supervisory control and data acquisition (SCADA), among others (Alegre & Coelho 2011).

These IS collect, store, and manipulate data which, after formatting, are transmitted, making the information available to specific recipients and users located, generally, in different departments or divisions.

Digitalization in the water industry has brought more sophisticated solutions, such as the internet of things (IoT), big data, artificial intelligence (AI), augmented and virtual realities, and digital twins, which help them to manage their activities (Laranjo & Martinho 2019). All these solutions face some barriers, such as lack of integration and interoperability of the different existing IS, people's resistance to changing their cultural mindset, and cybersecurity issues (Sarni et al. 2019). To solve the integration and interoperability problem some middle software such as NAVIA may be acquired, which focuses particularly on system operations and maintenance (Tavares et al. 2019).

Information management efficiency in municipalities often falls short of desired results since management is usually structured according to local government models. In these municipal WU, and due to lack of technical capacity and human resources, it is not usually possible to generate the critical information through daily routines (e.g., well planned and organized service orders that can ensure useful, good-quality information). According to Silva et al. (2019) 52% of low service WU have low infrastructural knowledge indices, which means that most of the WU do not know the assets they are managing. Additionally, available data may not benefit the various sectors of the utility (e.g., billing data are not linked to cadastral information) (Silva et al. 2012). As result, insufficient planning leads to a lack of integration, which affects information fluxes and its uses.

Despite that, IS have been further developed in order to promote efficient information management, with direct implications for the improvement of the utility's efficiency and effectiveness. These extraordinary developments in IS are motivated by the growing need to make WU more resilient and flexible, with greater transparency and rationality in the decision-making process, as well as enabling them to respond to challenges more quickly and efficiently. Nevertheless, and regardless of these developments, engineers and managers make their decisions based on information that is often incomplete, inaccurate, or out-of-date. One of the main challenges faced by engineers and managers in most WU (and particularly in those with more diversified data) is integrating the several, often conflicting, sources of information available on the infrastructure, its condition, its performance, and the various predictive analyses to assist in prioritizing asset interventions.

The DECision support platform for urban water InfrastructurEs (DECIdE) is a Portuguese R&D project aimed at the development of an information technology (IT) platform that integrates data from different IS for small and medium-sized municipal utilities. Additionally, this platform includes tools to support decision-making regarding IAM (e.g., performance system assessment, water and energy balances). This paper presents the project's global framework, the main findings of the WU characterization and the survey of its existing IS. Important issues are highlighted regarding data and information management required to successfully integrate data from these different systems, ultimately aiming at IAM.

Five WU, four municipalities and one municipal company participated in the DECIdE project. The project's first task of was to characterize the five WU's IS and collect their needs, which were intended to be solved with the platform. The degree of maturity of the IS management was different between the five, essentially due to the management model adopted, and the WU's geographical context and financial capacity. Regardless of their size or maturity, utilities perform similar tasks both at an operational and accounting level. In the less mature utilities, some of the IS was still be based on paper records or extensive spreadsheets, while in utilities of great maturity more sophisticated IS were used.

In 2016, Reis et al. (2017) carried out a survey of 171 of the 309 Portuguese WU (representing 55% of the total), and showed that 80% had a GIS and 89% had a cadastral record of their assets. In addition, about 89% of the surveyed WU used customer management systems and 95% used ERP systems for the accounting and financial aspects, as well as to manage human resources. According to the same survey, some IS were used by less than 50% of the WU surveyed. SCADA systems were present in only 43% of the WU surveyed, with maintenance support systems in 24% and energy management systems in only 6.4%.

In a municipality, water services are just one of the several services to be managed, namely, parking, transport and its infrastructure, urbanisation, conservation and green spaces, municipal equipment management, social action, administration, consulting, and evaluation, among others. Considering this vast number of services to be managed, the human, financial and technological resources are usually scarce and unevenly distributed among services, with the aggravating factor that leaders are elected every four years, making it difficult to establish long-term priorities such as the acquisition and implementation of IS.

The diversity of IS management is therefore pronounced. Some utilities lack certain IS while others have duplicated IS in different departments (e.g., some municipalities have different GIS for water services and urban planning services and sometimes only one of the departments has GIS specialized personnel). The former is due to a lack of need, since either they serve few customers or they manage small systems, but in most cases it is due to the management model adopted. The latter is due to lack of inter-departmental communication and pre-established goals.

As stated above, the DECIdE project characterized five different low service utilities, from small to medium sized, in order to cover a range of utility maturities (see Table 1). In order to highlight the representativeness of the five utilities selected, it should be stated that in 2018 (ERSAR 2019), of the total of 306 low service WU existing in Portugal, approximately 184 are managed directly by municipalities, 19 are municipal services, 48 are managed by parish councils, 23 are municipal corporations, 2 are public-public partnerships, 1 is state delegated, 28 are municipal concessions and 1 is an intermunicipal concession. Of those WU, about 84% are small to medium sized (i.e., approximately 256 of the total). Of the five WU that participated in this project, WU 1–4 (2% of the total) accounted for services provided directly by the municipalities, and WU 5 (4% of the total) is a municipal corporation. Despite this small number of participants, they can be considered representative of the diversity of governance models in the water sector in Portugal.

Since most of the assets in a WU are buried, geospatial information (i.e., asset location and characteristics) plays a very important role in any IAM methodology. Thus, GIS is indispensable to manage spatial data in any WU. The survey carried out showed that GIS was used in all five WU but with different degrees of implementation. WU 1 and 5 had complete information about their assets on a GIS, motivated by several cadastral surveys that had been carried in recent years. In both WU, the GIS was maintained and regularly updated by a dedicated technician. The use of a GIS by WU 2 was a recent practice and as such a technician dedicated to the management of geospatial information had not yet been assigned. As consequence, data was not routinely updated as it should have been and, as consequence, data was out of date. WU 3 and 4 used an open-source GIS whose data were collected during a project aimed at a systematic and standardized cadastral survey of the water supply and sanitation assets of 13 Portuguese municipalities. In both WU, the available information was quite complete but, due to the lack of a dedicated technician to manage and update the geographic information, data have become outdated.

Water services require high investment costs for the construction and maintenance of their assets, as well as significant and continuous operational costs (i.e., costs related to energy consumption, water and wastewater treatment or failure repairs, among others). These costs are covered by the tariffs charged to users. In order to guarantee the quality and continuity of the provided service, WU rely on ERP systems and CRM to manage their financial and billing-related data, respectively. The survey conducted revealed the generalized use of ERP and CRM systems to manage and store financial and billing data, with all WU having a dedicated department for this task. In WU 2, 4 and 5, the same IS was used to manage both financial and billing-related data, while the remaining utilities use separate IS.

Assets deteriorate with time, and anomalies or failures will happen. Analysis of the failure history may reveal recurrent problems due to unforeseeable events. Therefore, utilities should use service work orders (SWOs) to record and manage their interventions. The survey revealed that the use of an IS to manage the SWO-related data was restricted to WU 1 and 5. In both utilities, the SWO-related data was uploaded in situ via mobile devices to its GIS. As a result, the detailed database of their assets' anomalies and failures was maintained (e.g., location, cause, duration, date and time, material, among others). In the remaining WU, instead of GIS, a paper form was filled in in situ. This form was subsequently archived in digital and paper formats, in WU 2 and 3 respectively, which hinders straightforward access to the data as it always requires human intervention. In WU 4 the paper forms were transcribed directly onto spreadsheets designed for the purpose. Although a major part of the essential information was recorded, the full location of the SWO may not have been recorded since no association was made with the element that had been repaired in GIS. As such, only a partial location was recorded (e.g., name of the street). Additionally, this method is more vulnerable to data becoming outdated and to insertion flaws (e.g., data may be inserted twice).

SCADA systems are used by utilities to monitor their systems and to control processes through real-time acquisition of data from several sensors (e.g., flow, pressure, energy consumption, pH). The survey showed a generalized use of SCADA systems to manage the input water volumes or flows in their systems, with different degrees of implementation. WU 1 and 5 had their system completely monitored, as result of years of investment in SCADA systems. In the remaining WU a different paradigm was applied, as only their primary systems or district metered areas (DMAs) were monitored.

System hydraulic anomalies and failures are usually not directly and easily identified. Sometimes, utility managers or engineers realize there is a problem because of indirect signs (e.g., user complaints of lack of water pressure). The detailed diagnosis of defects by direct inspection are usually too costly, leading to the need for modelling and analysis tools. Simulation models are tools that allow, with an estimable margin of error, the analysis and prediction of the hydraulic behaviour of a system, based on the characteristics of its assets, its operation mode and the time-varying demands. Thus, hydraulic simulation models allow the rapid and efficient assessment of system hydraulic behaviour for given conditions and with a reasonable margin of error. The survey revealed that only WU 1 and 5 had hydraulic simulation models of their complete systems. WU 4 had a simulation model for their main system while the remaining WU did not have any kind of hydraulic simulation model.

In order to gain a better understanding of their assets and systems' performance through data science and analytics, WU need to somehow aggregate all data into a single system. Thus, data integration and interoperability are fundamental processes for a water utility.

The terms integration and interoperability, although used interchangeably, have different meanings. Integration refers to connecting different IS so that data from one system can be accessed by the others (Roberts 2017). As such, it involves a third-party software called middleware that translates data and makes it readable for the receiving system. Interoperability refers to the basic ability of an IS to connect and communicate with another IS promptly, even if they were developed by different manufacturers (O'Connor 2017). When systems are interoperable, they can not only share information, but also interpret incoming data and present it as it was received, preserving its original context (Roberts 2017). The complexity of data integration and interoperability is especially the case at the levels of data storage, structure, and the levels at which the data can be integrated and operated as a single entity (Kadadi et al. 2014).

The application of an IAM methodology may require the use of different analitical techniques for system diagnosis (e.g., water and energy balances, system performance assessment, simulation of hydraulic behaviour, and more complex AI techniques). These techniques use on data stored in multiple IS and often distributed across several decentralized databases. The required integration and interoperability of the different IS faces many barriers, such as the spread and variety of data sources and formats, resulting from diverse software systems and departments, as well as the outdatedness of major technological trends (i.e., use of old technologies based on spreadsheets). Allied to the lack of machine communication protocols, common data formats or the common meaning of exchanged content, the application of information and communication technologies in WU is often restricted, restricting the ability to share data and knowledge and hence interoperate between the people and software involved (Howell et al. 2016).

Several software solutions have been developed to solve the data integration and interoperability and the analytics problems in WU. The development of this type of solution may start within the utility itself, if the required human and technology resources are available to develop a system suited to their needs. If no human resources are available at the WU then the typical option is to acquire commercial software.

In Portugal, a limited number of utilities (i.e., the biggest) have the resources to develop their own solutions. This capacity is not, however, achievable for the majority of WU. At least two WU have developed their data integration and analytics through GIS-driven solutions, in which it is possible to easily access data related to infrastructure, fleets, water demand, billing, among others (Silva et al. 2017; Sousa et al. 2017). Another Portuguese utility developed a platform aimed at integrating data gathered from different sources (i.e., sensors, GIS, equipment automation signals, among others), as well as the results from hydraulic simulation models, aiming ultimately to produce predictive models of network hydraulic behaviour and decision support tools (Vieira et al. 2018). Some of the largest water companies operating in Portugal have designed and developed their own platform, connecting the different IS to support data management and information analysis, aimed at better IAM (Feliciano et al. 2015). Nonetheless, the use of these platforms is restricted to the utility itself.

Different commercial software (e.g., Baseform, WaterSmart) claim to provide comprehensive, actionable analytics to assist WU-specific efficiency issues and key business outcomes. Although integration of real-time sensor data is possible with these solutions, the integration of raw data from the scattered data sources may not be possible or requires processing treatment to import and integrate these data, which may not be justified.

IAM is considered by the Portuguese regulator (ERSAR) to be a key issue to increase the efficiency of WU. This fact has been transposed into Portuguese legislation, which requires WU providing a service to populations above 30,000 inhabitants to have IAM plans. Additionally, ERSAR evaluates the quality of the water service provided by all WU annually. A performance assessment system is used, requiring WU to send a huge amount of data in spreadsheets. As mentioned above, most of Portuguese utilities are municipalities that lack resources.

Any WU needs to collect vast data sets from their assets to manage them more efficiently, gain insights into their problems and plan their interventions. These data are transformed into useful information only when they are put together into a single technology (e.g., platform) on which deep analyses can be carried out. For example, pipe failure could be caused not only by age, but also by the pipe material, surrounding soil, water pressure, and street traffic.

In this context, DECIdE was undertaken, between 2018 and 2020, to develop a platform to integrate data from the different IS of the five participating WU. This data integration was to help WU to report data to the regulator more quickly and efficiently. Since WU also need to reduce water losses and energy costs, the platform includes tools to carry out water and energy balances (Alegre et al. 2000; Mamade et al. 2017). These balances use a broad set of data (e.g., input volumes and energy consumption, billed and unbilled consumptions, elevations). If sectorial balances need to be calculated, the complexity of data collection and management carried out by the utility manager can be overwhelming. As such, WU need a system to assess and compare the performance of their water supply systems. The basic question is therefore: how can we provide a unified approach to integrate and compare data organized and stored in a plethora of IS?

The selection of the integration method is of utmost importance as several features or analysis techniques depend on the data upload and update regularity. Taking into account the available technology and state-of-the-art techniques, the integration module could have been developed as a continuous connection between utility's IS databases and the platform databases (i.e., remote database access), or through a raw data file submission and setup module (i.e., manual raw data file upload and semi-automatic mapping between database attributes). Although the first option could lead to a better platform usability, as data is constantly and automatically uploaded and updated, it requires initial setup and continuous assistance by a utility's IT technician. It was concluded that remote database access could not be adopted in the short term due to the unavailability of IT technicians in four WU. So a compromise solution based on a raw data file submission and setup module was considered, since it is comprehensive and would allow the platform to be used by all WU.

Therefore, the platform can parse uploaded files and update the database with the parsed data. The challenge was to implement a file parsing mechanism, through a common interface and a semi-automatic process that minimized user intervention. File parsing starts by trying to determine file content. Identification is based on file extension and content analysis. Previous knowledge obtained by analysis of partner WU sample data files is included in the detection algorithm. After detecting the file type and content, the platform can identify the related data model elements, database tables and formats to target. An initial mapping is created between data model elements and file content. The user can correct and/or validate the detected mapping. The validated mapping is registered in the system and used the next time a similar file from that entity is uploaded. After validation, the import process starts.

The data model is set up to maintain a historical record of infrastructure and other time-sensitive data. When updating such data elements, the previous state is timestamped and kept in the database, allowing historical analysis of data evolution. As such, WU can assess the status of the several water distribution network components (e.g., valve status) for a given past date.

Data integration into the DECIdE platform may allow utilities to carry out advanced AI analysis for solving several problems, namely, pattern recognition and classification (Henriques et al. 2015), water demand prediction modelling, burst pipe detection and location (Covas & Ramos 2006) and anomalous event detection (Marchionni et al. 2016). In this context, another R&D project aimed at the development of algorithms and models that allow the extraction of knowledge from the data, supporting the WU in decision-making and thus improving the management of their systems. This project has been undertaken to continue the development of the platform.

The DECIdE project provided some interesting findings about water utilities data management. Firstly, the collected data are stored in different IS in several departments (e.g., water department, financial department, urban planning department). These specific data depend on specialized human resources for the required treatment and validation process. As an example, one of the surveyed WU needs two engineers full-time for almost a month to collect and process the required data for the spreadsheet for an ERSAR report. As many municipalities lack human resources, that activity takes their attention away from other important activities (e.g., geospatial information updates). Additionally, some data has a high degree of uncertainty associated with them. As an example, SWO-related data should be collected by an operational technician at the field. However, most of the times they has no time to collect all the information. Additionally, this technician is often not aware of the importance of collecting those data.

One surprising finding is about IS that they are contracted for a particular objective, such as the billing system. These systems use cloud-based databases that are not controlled by the utility. When data access is required, a requisition must be made to the developer company, which leads to questions relating to the ownership of the data. Should the developer company or the utility own the data? Additionally, if a utility wants to apply advanced algorithms to those data for detailed analysis, using for instance AI techniques, they do not have direct or straightforward access. Another point is the time required to access and retrieve specific data. Due to obsolete network infrastructure, it can take about 30 min to obtain the billing data for about 45,000 clients. If a more complete analysis is to be carried, it can take a day or more to retrieve the required data, making the computer inoperable during this time.

Regarding SWOs, they should be linked to GIS and should be as detailed as possible about the cause that originate that order. Some utilities have restricted categorizations of their interventions in a certain IS, which can distort a broader assessment of the real problem.

Currently, long-term monitoring and systems modelling are required for a better understanding of the utilities' systems. However, these data obtained through the sensors installed in the network present some problems, namely, null values, outlier values, irregular acquisition frequency and non-calibrated sensors. These data needs to be treated (i.e., identified and corrected) preferably in an automatically, namely by removing the nulls and the outlier values, eliminating nonreliable data, and filling the gaps created. So, the data integration process should also consider statistical data and analysis methodologies for evaluating these huge data sets. The platform's future developments aim to integrate timeseries, considering a treatment and validation process.

This paper presents an overview of the IS used by Portuguese utilities as well as a reflection on how data from different IS can be integrated. The diversity of IS in a WU is challenging for bringing data together into a single technology (e.g., platform) for analytics. With this in mind, the DECIdE platform was developed for data integration, in collaboration with a representative set of Portuguese WU. Data integration on the DECIdE platform is done through a two-step import process. The first step consists of the import of all the necessary files to integrate the GIS data and the data related to water meters. After the parsing of all these data, the user is informed about the possible missing information, which is needed to calculate the set of metrics. After that the user has the option of validating or completing the missing data. This two-step validation process will help WU with fewer data to continue to take advantage of the platform even though they cannot provide all the information.

Once the assets' physical data have been integrated (e.g., pipe, pumps, water meters), monthly water and energy measurements can be imported and assigned to the respective assets. Additionally, SWOs can be integrated, assigned to a specific DMA or system.

Due to inadequate resources (e.g., human, financial), data integration faces several limitations in most WU. Some WU lack skilled professionals but have financial resources, and choose to acquire expensive software that claims data integration. However, in reality commercial software has several limitations and may not match WU needs. Data integration may also require mapping components to a new schema that will make the existing ones uniform, with new attributes. Nonetheless, the new database may have scalability problems depending on the chosen technology.

The DECIdE platform was the first step in the development of a reference tool to support IAM in urban water services. Future work will focus on data science, with a special focus on the detection, categorization, and location of anomalous events.

The authors want to acknowledge Fundação para a Ciência e a Tecnologia, Programa Operacional Regional Lisboa 2020 and Programa Operacional Regional Alentejo 2020 (grant number SAICT-POL/24135/2016 | LISBOA-01-0145-FEDER-024135 | ALT20-03-0145-FEDER-024135) for supporting the DECIdE project. The authors also thank the engineers from the five participating water utilities for their contribution.

All relevant data are available from an online repository or repositories. The link is https://github.com/DECIdE-RDProject/Platform-test-utility.