Utility companies, especially water utility companies, face a wide range of challenges, from environmental policies, cost- and time-effective procedures, and responsive asset management to productive and personalized customer care. Water utility companies traditionally have technical teams that leverage asset management procedures, and administrative personnel for customer care, two apparently distant worlds but providing information within the same workflow procedures. This was the case at the water and sewer company of Thessaloniki (EYATH). There was a disconnect between teams that 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 is a utility company located in the northern part of Greece, providing fresh water and sanitation to 1.2 million residential customers. EYATH has used geographic information system (GIS) technology for the past two decades and recently has focused on connecting the GIS infrastructure to other information systems within the company, providing GIS services, exchanging data, adding spatial perspective to most of the processes, and embedding geographic awareness for better understanding and implementation. An enterprise resource planning (ERP) data warehouse stores all customer-related data, which spans from financial data (billing) to hydraulic data (consumption), covering 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 (GIS data) warehouse and the customer database (financial and ERP customer data) embody most of company’s procedures and data, providing access and a holistic view of the entire water and sewer life cycle (assets, personnel, customers, and activities).
Traditionally, EYATH would handle asset management via interaction between administrative and technical personnel, which was time-consuming; customer care requests with asset infrastructure knowledge, which was inefficient; or hydraulic modeling via requests for consumption data, which were unproductive. By connecting asset and customer data, EYATH expected to increase asset management efficiency by addressing more failures in less time, minimize the response time to customer care requests by substituting paperwork with automated information flow, and improve 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, uniquely identifying customers and customer activity but also allowing users to input data without restrictions or editing rules (e.g., customer addresses not being standardized). Additionally, 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.
In order 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. Having scrutinized thoroughly all the aspects of the issue, staff recognized that the only link complying with the above-mentioned prerequisites was the address of the asset on the GIS database (for instance, the address of a water meter) and the address of the customer (the address of that same water meter) in the ERP database.
The initial connection based on address was rather disappointing: 9.4 percent of the 500,000 customers matched meters in the GIS database based on corresponding ERP addresses. This was because address input in ERP had been unrestricted for almost three decades and for half a million customers, while addresses in the GIS database were not standardized when it was populated.
Standardization and Validation of Data
It was clear that the address structure needed to be standardized within both systems. Because EYATH’s GIS department had experience that could support the project, it was executed in-house. Additionally, 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. The involved parties held meetings to define the scope, set milestones, and address issues that came up through the process. The project lasted more than a year and was undertaken exclusively by GIS department personnel.
A methodology was categorized into three major steps:
- 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 was labor-intensive and time-consuming and required detailed correction of a very high percentage of the database. This part of the project was undertaken by the GIS department, dedicating four members of the GIS staff and resulting in approximately 3,000 working hours.
Basemaps and address information were overlaid in ArcGIS Pro, combining street information from different sources into a unique cartographic environment, eliminating ambiguities, and cross-checking data sources.
In cases of missing or contradictive information, GIS personnel visited authorities responsible for street names and collected data in the field with the use of tablets and 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.
Using the standardized address data developed in step one, 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 export of information, complying with the restriction on making large corrections in the database. Correcting data in this way preserves data integrity and limits the impact on crucial sectors such as billing.
Although the ERP database was corrected as described in step two, permanent correction of the ERP database remained a challenge. Since the cost for outsourcing the holistic address correction was too high and did not ensure data integrity on massive data corrections, it was decided that this step should be done gradually by ERP users as well as customers via the customer portal. Therefore, the final goal was to ensure that future ERP transactions would both correct existing addresses and ensure that future addresses will comply with the standardized address table. For that purpose, a procedure within ERP was established, forcing every future change in customer data (new applications, meter changes, etc.) to comply with the standardized address table, while at the same time ensuring that mapping functionality is provided for address validation.
The GIS department provided support to ERP users, empowering geographic awareness to customer care personnel. 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 beginning of the project, and EYATH has succeeded in matching over half of the total customers in the ERP database with GIS assets (335,000 of the 515,000 customers are joined to meters in the GIS database based on the address). It is expected that in the next two years, the project will be completed, allowing the majority of customer data to be connected to 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 benefits of the project are numerous and multidimensional, allowing verticalization of procedures, interconnection of processes, unification of activities, dissolving of traditional business silos, and the provision of updated and holistic information to all involved parties regardless of their involvement sector.
Now we have all the information we need with one click, out in the field, speeding up our work [and] making our life easier.
Integrating the GIS and ERP data has provided stakeholders (field crews, engineers, and administrative and customer care personnel) continuous updated access to the life cycle of each business task, making customer care and asset management workflows more efficient and cost-effective. Customer care personnel now have a geographic perspective of the customer location to be served, minimizing service time. The billing department is saving an average of one working hour per day by geolocating consumers more quickly. The improved efficiency equals money saved for EYATH. Field crews have instant access to asset- and customer-related data, improving response times. This minimizes workload and overtime expenses, resulting in an average saving of seven working hours per day in total for all field crews for the sewer and water network.
- Asset data and geographic perspective are embedded in the ERP environment, allowing GIS services to run in the background, enhancing customer support procedures, and improving the customer experience.
- Historic consumption data—which is 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 easily available to hydraulic engineers. This has eliminated ad hoc exports and imports of data between systems, reducing working hours by an average of two hours per week.
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.
EYATH customers are also benefiting from the 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 (application for a new connection, construction of the connection line, and future asset management of the connection line). Or vice versa: 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 interconnected to asset management procedures, which are linked via GIS with the physical asset, allowing the recording of the full life cycle of the request. This improved process has reduced the amount of time to complete customer requests. The customer service department is saving one working hour per day on average.
In addition to the benefits already being realized, the processes and capabilities that the project has added to EYATH’s working environment will be utilized by upcoming projects and technologies—for example, 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.
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.
Stay Connected with Esri’s Water Team
ArcGIS is an extensive information system that enables modernization of workflows with easy-to-use applications for the field and office. Strengthen your organization with smart water solutions that will increase efficiency and provide insight for decision-makers. Visit the Esri Water Utilities web site for more information.
Join the Water Utilities Community
Subscribe to the Water Industry newsletter “Esri News for Water Utilities and Water Resources“