Quick Summary
Bronson delivered two analytics engagements for Hydro Ottawa’s Business Performance and Reporting team, moving from a GPS-based fleet Tableau dashboard to a fully automated energy and water consumption reporting solution.
The fleet engagement built Tableau dashboards leveraging live GPS data to identify, monitor, and report on employee productive time across Hydro Ottawa’s vehicle fleet.
The energy reporting engagement automated a previously entirely manual data gathering and reporting process, reducing a peak-period fulfillment time of up to 21.5 days per request through structured data engineering workflows.
The energy reporting solution was built on a Snowflake data warehouse as the primary data repository, integrated with Oracle data sources and Boomi as the ETL and workflow engine, delivering live-connected reports with no additional licensing cost to Hydro Ottawa.
Bronson developed two report types for the energy engagement: an external customer-facing report (PDF and CSV) and an internal staff report (graph, PDF, and CSV), both hosted on Tableau Online with dynamic filtering by address and date range.
Project Overview
Hydro Ottawa is a local electricity distribution company serving the Ottawa region, responsible for delivering power to hundreds of thousands of customers and meeting a range of regulatory reporting obligations. Bronson’s engagement with Hydro Ottawa’s Business Performance and Reporting (BPR) team spanned two sequential call-ups in early 2022, each targeting a distinct analytics capability gap.
The first engagement addressed fleet data visualization. Hydro Ottawa’s BPR team was building Tableau reports to leverage GPS tracking data across its vehicle fleet, with the primary objective of identifying, monitoring, and reporting on employee productive time. The team had the underlying data and integrations in place but needed expert guidance on how to effectively leverage GPS data within Tableau’s geographic visualization capabilities. Bronson was engaged to work directly alongside the BPR team in collaborative working sessions to build the Tableau reports.
The second engagement addressed a more complex operational challenge: the annual Energy and Water Reporting and Benchmarking (EWRB) compliance cycle. Under applicable regulation, large building owners must report their energy and water consumption and greenhouse gas (GHG) emissions annually. As a local electricity distributor, Hydro Ottawa is required to provide aggregated electricity consumption data to building owners on request to support this reporting. At the time of engagement, every aspect of this process was manual, cumbersome, and time-consuming, with peak-period requests taking up to 21.5 days to fulfill. With reporting volumes expected to grow steadily over the following two years, Hydro Ottawa engaged Bronson to automate the data gathering, analysis, and report generation process, using the organization’s existing technology stack anchored by a Snowflake data warehouse.
The Challenge
Across both engagements, Bronson was working with a technically capable client team in a regulated utility environment where data accuracy, process reliability, and compliance with reporting deadlines are non-negotiable.
- GPS data complexity in Tableau. Geographic and time-series GPS data requires specific handling within Tableau to produce meaningful fleet productivity visualizations. Raw vehicle movement data needs to be structured, filtered, and rendered in a way that surfaces actionable insights on employee productive time without overwhelming the report consumer. The BPR team had the data but needed expert guidance to unlock it.
- A fully manual compliance process under pressure. The EWRB reporting process was entirely manual at the time of engagement, requiring staff to verify authorization, reach out to third-party energy providers, and aggregate consumption data across multiple accounts for individual property addresses. Peak demand runs from January through May each year, and the existing process could take over three weeks to fulfill a single request.
- Growing regulatory volume with no scalability. Local regulatory and civic pressure was expected to drive steady growth in EWRB reporting requests over the following two years. A manual process with a three-week turnaround had no capacity to absorb this growth without significant staff burden or compliance risk.
- Snowflake as the foundation, with Oracle and Boomi in play. The solution needed to accept Snowflake as the primary data repository while also handling Oracle data sources and flat files as needed. Boomi was required as the ETL and workflow integration tool. Building a reliable automated pipeline across this stack, while keeping outputs in formats that Hydro Ottawa’s own developers could connect to Tableau, required careful data engineering and close collaboration with the client’s technical team.
- No new licensing costs permitted. The solution was required to operate entirely within Hydro Ottawa’s existing technology portfolio. This constrained the design to what could be achieved with Snowflake, Oracle, Boomi, and Tableau Online — but also made the Snowflake data warehouse the natural architectural centrepiece for aggregation, pre-provisioning, and live reporting.
- Two distinct report types with different audiences. The energy reporting solution needed to serve two audiences simultaneously: external building owners requiring clean, address-level consumption summaries in PDF and CSV formats for regulatory submission, and internal staff requiring account-level breakdowns with graph-based visualizations for verification and quality assurance. Both report types required dynamic filtering by address and date range, annual and daily aggregation views, and live connections to the underlying data.
- Pre-provisioning for repeat customers. A key efficiency requirement was the ability to pre-provision data for repeat customers so that returning requests could be fulfilled rapidly rather than rebuilding from scratch each cycle. This introduced additional data architecture considerations around property continuity, account aggregation, and the risk of pre-provisioned data becoming stale when properties are sold or severed.
- Tight delivery timeline. The EWRB engagement ran from late January to April 30, 2022, with the peak reporting season beginning in March. Delivering a working automated solution before the peak was critical to realizing the efficiency benefits of the engagement within the first reporting cycle.
Our Solution
Bronson structured both engagements around collaborative working sessions with the Hydro Ottawa BPR team, ensuring that knowledge transfer and capability building were embedded throughout delivery rather than reserved for a final handover.
Engagement 1: Fleet GPS Tableau Dashboard
1. Kickoff and Data Review
Bronson attended a kickoff meeting with the BPR team to align on the GPS data challenges, scope, and desired reporting outcomes. Following the kickoff, Bronson reviewed the relevant data architecture, example Tableau reports, and GPS data structure to develop a clear understanding of the source data and visualization requirements before building began.
2. Collaborative Tableau Dashboard Development
Bronson worked alongside Hydro Ottawa’s technical resources in screen-sharing working sessions to build the Tableau reports leveraging GPS vehicle movement data. The engagement focused on translating raw GPS coordinates and timestamps into meaningful geographic visualizations of fleet activity and employee productive time, applying Tableau’s geographic data capabilities to surface the patterns most relevant to the BPR team’s reporting objectives.
3. Technical Handover
A final technical handover session transferred all designs, data files, workflows, visualizations, methodology, and documentation to the Hydro Ottawa team, with Bronson available to answer technical questions and provide further guidance on any aspect of the GPS Tableau implementation.
Engagement 2: EWRB Energy Reporting Automation
4. Kickoff, Data Architecture Review, and Requirements Clarification
Bronson attended a kickoff meeting and reviewed example data, data architecture, and data schemas provided by the Hydro Ottawa team. Bronson worked through a structured set of clarifying questions with the BPR team to confirm report specifications, output formats, aggregation requirements, filtering logic, refresh frequency, and the pre-provisioning requirement for repeat customers before proceeding to detailed design.
5. Detailed Design and Solution Architecture
Working from the high-level project requirements and the clarified report specifications, Bronson developed detailed design requirements for the solution and presented a proposed architecture for client sign-off. The architecture centred on Snowflake as the primary data warehouse, serving as the aggregation and pre-provisioning layer for all consumption data. Boomi was configured as the ETL and integration workflow engine, managing data extraction from Snowflake and Oracle sources and routing structured outputs to Tableau. This architecture kept the solution entirely within Hydro Ottawa’s existing technology portfolio while establishing Snowflake as the scalable, reliable foundation for all current and future reporting.
6. Data Engineering Workflow Development
Bronson developed the data engineering workflows required to extract, transform, and structure electricity consumption data for automated report generation. Workflows were designed to handle Snowflake and Oracle data sources, aggregate consumption data at both monthly (12-month) and daily (365-day) levels, and produce the structured data files needed to feed both report types. All workflow steps, assumptions, and limitations were documented, with each workflow’s purpose clearly described.
7. Automated Report Implementation
Bronson implemented the automated reporting solution, producing two report types hosted on Tableau Online with live data connections: an external report delivering annual and daily consumption summaries by address in PDF and CSV formats for building owner use in regulatory submissions; and an internal report delivering the same views at the account level with graph-based visualizations, account number summaries for verification, and PDF and CSV export. Both report types support dynamic filtering by address and date range, defaulting to the prior calendar year. Pre-provisioning logic was built into the Snowflake layer to support repeat customer efficiency across future reporting cycles.
8. Documentation and Technical Handover
A detailed technical handover session transferred all designs, data files, Boomi workflows, Tableau visualizations, methodology, analysis, and documentation to the Hydro Ottawa team, with training provided on all facets of the solution.
Key Deliverables
- Fleet GPS Tableau Dashboard – A geographic Tableau dashboard visualizing vehicle movement and fleet activity from GPS data to support identification, monitoring, and reporting on employee productive time across the Hydro Ottawa fleet.
- EWRB Automated Reporting Solution – A fully automated energy and water consumption reporting pipeline built on Snowflake, Oracle, and Boomi, replacing a manual fulfillment process with structured, automated report generation for both external building owner and internal staff use cases.
- External Consumption Report (Type 1) – A Tableau Online report delivering annual (12-month) and daily (365-day) aggregated electricity consumption in PDF and CSV formats for building owner use in regulatory energy and water reporting submissions, with dynamic filtering by address and date range.
- Internal Consumption Report (Type 2) – A Tableau Online report delivering the same consumption views at the account level with graph-based visualizations and account number summaries for internal staff verification, exportable as graph, PDF, and CSV, with dynamic filtering by address and date range.
- Snowflake Data Architecture and Pre-Provisioning Layer – A structured Snowflake data warehouse configuration serving as the aggregation and pre-provisioning foundation for the EWRB reporting solution, enabling repeat customer requests to be fulfilled efficiently across future annual reporting cycles.
The Impact
Bronson’s two engagements delivered measurable operational improvements to Hydro Ottawa’s reporting capabilities across both fleet management and regulatory compliance.
- The GPS Tableau fleet dashboard gave the BPR team a practical, geographically rich reporting tool for employee productive time, unlocking the value of existing GPS infrastructure that had previously been difficult to leverage in Tableau.
- The EWRB automation solution eliminated the entirely manual fulfillment process for energy and water consumption reporting, directly reducing a peak-period processing time of up to 21.5 days per request and giving the organization the capacity to absorb the steady growth in reporting volumes expected over the following two years.
- Anchoring the solution in Snowflake as the primary data warehouse gave Hydro Ottawa a scalable, structured aggregation layer for consumption data that serves not just the current EWRB reporting requirement but positions the organization for future self-serve reporting capabilities and expanded analytics use cases beyond the immediate engagement scope.
- The pre-provisioning logic built into the Snowflake layer means that repeat customer requests in future reporting cycles can be fulfilled in a fraction of the time previously required, reducing staff burden during the March-to-May peak period.
- Both solutions were delivered within Hydro Ottawa’s existing technology portfolio, adding zero new licensing or subscription costs while substantially expanding the analytical capabilities available to the BPR team.
- The live Tableau Online connections to the Snowflake-backed data pipeline mean that both report types always present current data, eliminating the manual refresh burden that characterized the prior reporting process.
- The collaborative working session model for both engagements ensured the BPR team understood and could maintain every component of the solution, with comprehensive technical handovers leaving no capability gaps at project close.
The two engagements reflect Bronson’s ability to combine Tableau development expertise with serious data engineering capability across enterprise stacks. By treating Snowflake not merely as a data source but as the strategic foundation for Hydro Ottawa’s reporting architecture, Bronson delivered a solution that addresses today’s compliance obligations while building the infrastructure for tomorrow’s self-serve analytics ambitions.
