Utility companies, especially water utility companies, face challenges such as implementing environmental policies, developing cost- and time-effective procedures, fostering responsive asset management, and providing personalized customer care. Water utility companies traditionally have technical teams that leverage asset management procedures, and administrative personnel for customer care, two seemingly distant worlds that provide information within the same workflow.
This was true at Water and Sewer Company of Thessaloniki S.A. (EYATH). A disconnect between teams created procedural bottlenecks, which increased response time and diminished the quality of customer service. EYATH needed to bridge this gap to provide a holistic view of water and sewer systems. By doing this, staff would streamline daily workflows, improve efficiency, and eliminate delays that impact customer service.
A Need for Shared Data
EYATH, a utility company located in the northern part of Greece, provides fresh water and sanitation to 1.2 million residential customers. The company has used GIS technology for the past two decades. Recently, it focused on connecting its GIS infrastructure to other information systems within the company to provide GIS services, exchange data, add spatial perspective to most of the processes, and embed geographic awareness into its operations for better understanding and implementation. An enterprise resource planning (ERP) data warehouse stores all customer-related data, ranging from financial data for billing to hydraulic data that measures consumption and encompasses service sectors such as personalized customer care, customer profile policies, and billing strategies.
GIS and ERP constitute the two main pillars of EYATH procedures: the system infrastructure and data warehouses. The technical warehouse containing GIS data and the customer database that manages financial and ERP customer data together embody most of the company’s procedures and data. They provide access and a holistic view of the entire water and sewer life cycle of assets, personnel, customers, and activities.
Traditionally, EYATH handled asset management via interaction between administrative and technical personnel, which was time-consuming. By connecting asset and customer data, EYATH expected to increase asset management efficiency by addressing more failures in less time, minimizing the response time to customer care requests by substituting paperwork with automated information flow, and improving hydraulic modeling workflow by granting direct access to consumption data.
Integrating GIS and ERP
Connecting the GIS and ERP databases proved to be a very complicated and time-consuming process because there was no direct joining point between the two systems. ERP had been storing data since the 1990s. The system uniquely identified customers and customer activity but also allowed users to input data without restrictions or editing rules, which resulted in nonstandardized addresses. In addition, interrelated processes—such as construction of a new water service line following an application submitted by a customer—were directed to ERP and GIS as separate procedures, without documented information within the systems that could allow future connection between the asset and the customer.
To achieve the bilateral connection of the databases, the GIS department needed to identify a common denominator between GIS data and customer data. This common denominator would be used to link procedures, serve both water and sewer networks, and function as a unique point of reference. After thoroughly scrutinizing the issue, staff recognized that the only link that would work was the asset address (such as a water meter) in the GIS database and the customer address in the ERP database.
The initial connection based on address was rather disappointing. Based on corresponding ERP addresses, only 9.4 percent of the 515,000 customers matched the meters in the GIS database. The cause: Address input in ERP had been unrestricted for almost three decades. A half million customer addresses in the GIS database had not been standardized when the database was populated.
Data Standardization and Validation
It was clear that the address structure for both systems needed to be standardized. Because EYATH’s GIS department had experience that could support the project, it was executed in-house. In addition, the project would require constant internal feedback to succeed, so using an outside consultant would be challenging.
The project was led by the GIS manager, Ioannis Kavouras, in cooperation with the IT department (including an external contractor supporting the ERP infrastructure) and staff in the Division of Consumers who used the ERP system. These parties held meetings to define the scope, set milestones, and address issues that came up throughout the process. The project lasted more than a year and was undertaken exclusively by GIS department personnel.
The methodology employed was categorized into three major phases:
- Standardizing address data
- Correcting asset and customer addresses based on standardized address data
- Ensuring that future customer address input complies with standardized address data
Standardizing Address Data
Standardizing address data was labor-intensive and time-consuming and required detailed correction of a high percentage of database records. This phase was undertaken by the GIS department. Four GIS staff members were dedicated to this process and spent approximately 3,000 working hours on it. Basemaps and address information were overlaid in ArcGIS Pro, which combined street information from different sources into a unique cartographic environment and eliminated ambiguities and cross-checked data sources.
In the case of missing or contradictory information, GIS personnel visited authorities responsible for street names and collected data in the field with the use of tablets and the ArcGIS Server web mapping application. This work resulted in a table of address rules as well as a complete, homogeneous, updated, and standardized address table.
Correcting Address Data
Using the standardized address data developed, the GIS team corrected data in the GIS database that stored address information such as water meter locations, geocoding data, and annotation. In addition, the GIS department created an SQL procedure to correct customer records coming from the ERP database. This procedure is used during the export of information to comply with restrictions on making large corrections to the database. Correcting data in this way preserves data integrity and limits the impact on crucial sectors such as billing.
Ensuring that Addresses Are Standardized
Although the ERP database was corrected, permanent correction of the ERP database remained a challenge. The cost for outsourcing holistic address correction was too high and did not ensure data integrity for massive data correction. Instead, it was decided that this step should be done gradually by ERP users as well as customers via the customer portal. The final goal was to ensure that future ERP transactions would both correct existing addresses and ensure that future addresses would comply with the standardized address table.
For that purpose, a procedure within ERP was established that forced every future change in customer data (e.g., new applications, meter changes) to comply with the standardized address table and—at the same time—ensuring that mapping functionality was provided for address validation.
The GIS department provided support to ERP users, empowering customer care personnel with geographic awareness. This process is daily and ongoing, both for the employee-user of the ERP database and for the customer-user of the portal. It has been approximately six years since the project began, and EYATH has succeeded in matching more than 400,000 of 515,000 customers in the ERP database with GIS assets. Within the next two years, EYATH expects the project will be completed. At that time, most of the customer data will be connected to the corresponding asset data, providing a holistic and complete solution to satisfy the need for shared data within the company.
Realizing the Benefits of Integrating Critical Systems
The project benefits are numerous and multidimensional: interconnecting processes; unifying activities; dissolving traditional business silos; and provisioning updated and holistic information to all involved parties regardless of their involvement sector.
Integrating the GIS and ERP data has provided field crews, engineers, and administrative and customer care personnel with continuous access to updated information on the life cycle of each business task. This makes customer care and asset management workflows more efficient and cost-effective.
Customer care personnel now have a geographic perspective on customer locations, which minimizes service time. The billing department saves an average of one working hour per day by geolocating consumers more quickly. Improved efficiency equals money saved for EYATH. Field crews have instant access to asset- and customer-related data, which improves response time. These improvements minimize workload and overtime expenses and have resulted in an average saving of seven working hours per day for all sewer and water network field crews.
“Now we have all the information we need with one click, out in the field, speeding up our work [and] making our life easier,” said Hatzisarrou Panagiotis, who is responsible for water network maintenance of the center of Thessaloniki.
There have been additional project benefits. Because asset data and a geographic perspective are embedded in the ERP environment, GIS services can run in the background to enhance customer support procedures and improve the customer experience.
Historic consumption data—essential for modeling, performing geographic analysis of consumption patterns, tracing water losses in combination with SCADA data, or identifying customers affected by a network failure—is now easily available to hydraulic engineers. This functionality has eliminated ad hoc exports and imports of data between systems and reduced staff time by an average of two hours per week.
EYATH customers are also benefiting from this systems integration. Customer claims triggered in the ERP environment, such as applications for a new connection, are linked to the physical asset (connection line) via the interconnected systems, allowing the recording of the full life cycle of the request (e.g., application for a new connection, construction of the connection line, and future asset management of the connection line).
Conversely, a customer request to fix a network failure is triggered in the call center environment, which is linked to the ERP system. The request is connected to asset management procedures, which are linked via GIS with the physical asset. This ensures the full life cycle of the request is recorded. This improved process has reduced the amount of time to complete customer requests. On average, the customer service department is saving one working hour per day.
In addition to the benefits already realized, the processes and capabilities that the project has added to EYATH’s working environment will be utilized by upcoming projects and technologies, such as streamlining the workflows needed to replace traditional water meters with smart meters. The project also provides the foundation for digital twin creation by providing EYATH with accurate and robust asset data.
“By aligning the location of water meters with customer data, we have minimized cases of unfound or misplaced meter location for meter indication reading, meter replacement, or meter installation, minimizing significantly office workload on a daily basis,” said Lefkas Aspasios, the head of the Meter Reading Office.
Through the project, GIS has provided the business framework for better services and optimization of procedures, minimizing the cost and response time for asset management, and improving the customer care experience.
