From Data to Action: Bridging EAM and SCADA Data on AWS for Next-Generation Proactive Pump Management in Lift Stations

Introduction: Pump failures in wastewater collection systems disrupt operations, escalate maintenance costs, and pose environmental risks. Addressing these challenges requires a detailed understanding of failure patterns, identifying high-risk areas, and predicting future needs to enable proactive decision-making. This study presents an integrated framework designed to clean and enrich Enterprise Asset Management (EAM) work orders (WO), integrate Geographic Information System (GIS) data, and link them with Supervisory Control and Data Acquisition (SCADA) pump status data. The framework generates predictive insights to identify high-risk areas, analyze failure patterns, and support proactive decision-making. The framework, built on modern data architecture principles using Amazon Web Service (AWS) data lake and advanced business analytics techniques, enables scalable, efficient data processing and utilization. Objective: These are main goals of this study: 1. Create a comprehensive list of pump failures by cleaning EAM WO and integrating it with GIS, and SCADA data. 2. Define and calculate pump failure metrics including failure frequency, repair time, and risk. 3. Cluster failures to major and minor using repair time 4. Predict future budgets, labor, and maintenance needs based on historical trends. 5.Identify temporal and spatial failure patterns to find high-risk areas and periods. Data Preparation and Methodology: The EAM dataset filtered to focus on pump-related failures, using keywords such as 'breakdown' and categories like 'PMP (mechanical)' and 'ELEC (electrical)', 'NO-COM (no communication)', to name a few. SCADA data validated EAM failure events and improved the accuracy of repair time calculations by verifying the pump off duration during the EAM WO. For matching entries, SCADA timestamps indicating when the pump turned on were used instead of the less reliable EAM completion timestamps to calculate repair times. Metrics such as the repair period (time interval between EAM event creation and SCADA pump restoration) and failure frequency were derived to evaluate failure trends. Failures were classified as major (repair time > 1 day) or minor (≤ 1 day). Pump risk was defined by counting its past failures that required repair and by analyzing failure frequency. Eventually, GIS data enriched the dataset with spatial identifiers for lift stations, enabling spatial analysis, and risk map was generated. The framework incorporated AWS data lake capabilities to unify and process large volumes of data. AWS ensures the solution is scalable and reliable, supporting both current analysis and future expansions. Using exploratory data analysis, we identified temporal and spatial failure patterns, along with failure costs, failure counts, and the type of expertise required for pump repairs based on predicted pump failures. Results and Key Findings: Temporal patterns were analyzed to identify the days of the week with higher failure risks, enabling crew scheduling optimization. Spatial analysis using GIS data revealed hotspots of failures over time, highlighting areas that experienced recurring failure issues. The risk map was generated, and high risk hotspots were tracked to understand how failure distributions shifted across the city overtime and to identify locations requiring more focused maintenance efforts in the future. The analysis provided the following insights: - System performance: Pump failures have decreased year by year, indicating improved reliability. - Failure types: Mechanical and electrical issues dominate, emphasizing the need for more specialized labor in these fields. - Temporal patterns: Certain day of the week consistently exhibit higher failure rates, highlighting the higher failure probability on that day and need for targeted scheduling. - Hotspots: Spatial mapping identified shifting high-risk areas over the past few years, necessitating dynamic maintenance strategies. Predictive insights based on forecast key metrics, including: 1. Annual budgets for repairs and replacements. 2. Labor requirements for mechanical and electrical failures. 3. Failure trends and repair versus replacement probabilities. 4. High-risk locations for future failures. 5.High-risk day of the week to optimize crew scheduling. Conclusion: This framework integrates EAM WO, GIS, and SCADA data with AWS capabilities to deliver a scalable and efficient solution for analyzing and predicting pump failures in wastewater systems. It provides actionable insights into risk management, budget planning, workforce management, failure prevention, and maintenance scheduling. Spatial and temporal analysis enhance system reliability and resilience and reducing costs. The framework's adaptability makes it applicable to broader infrastructure management, supporting long-term optimization and sustainability.