Case Study 4: Enhancing Data Integration for Meter Replacement Strategy Across Combined Regions

Problem

The integration of two geographic regions, Couchiching (COU) and Orillia (OLA), presented a significant challenge for a utility preparing for a meter replacement field trial. The merger required a detailed analysis to address discrepancies in meter read data between the two regions. Accurate scheduling of meter reads was crucial to efficiently manage the workload on specific dates throughout 2023 and 2024.

Solution

To tackle this challenge, our team consolidated datasets from two distinct systems: PIQ and PEC. We first harmonized the data into a single dataset covering both COU and OLA. This involved a meticulous organization of the data by next meter read date (MRD) followed by meter read units (MRUs) to ensure precise data management and effective visualization.

The structured data was then imported into Power BI, allowing for advanced querying and analysis. We also converted geographical data from the original Excel files into KMZ files for visualization in Google Earth, which provided a detailed geographical perspective on meter locations within each region.

This comprehensive approach enabled us to not only identify but also visually represent the discrepancies between the existing datasets. It also facilitated a more efficient preparation for the meter replacement trial by providing a clear geographical layout of meter locations and a detailed timeline of meter reading schedules.

Result

The final output was a series of detailed visualizations, displayed in Power BI, which illustrated the distribution of MRUs by day, month, and year for each region. These visuals were initially created for each region separately and then combined to provide a unified view. The visuals included an advanced filtering mechanism to highlight data points where the count of MRUs was ten or higher, ensuring focus on areas with significant activity.

These visualizations effectively displayed the total meter reads required for each MRU group on specific days, using color coding to differentiate between the two regions and to highlight areas with the highest meter read demands.

The strategic data integration and visualization process not only streamlined the trial’s preparation phase but also equipped the utility’s management team with the tools needed to make informed decisions on resource allocation and scheduling. This led to improved efficiency and readiness for the upcoming meter replacement trial across the newly combined regions.