Quick Facts

  • Bronson completed two sequential analytics engagements for Hydro Ottawa’s Business Performance and Reporting (BPR) team in early 2022, each addressing a distinct reporting capability gap.

  • The first engagement produced Tableau dashboards built on live GPS data, giving the BPR team a geographic visualization tool for monitoring and reporting employee productive time across Hydro Ottawa’s vehicle fleet.
  • The second engagement automated the Energy and Water Reporting and Benchmarking (EWRB) compliance process, replacing a manual workflow that took up to 21.5 days per request at peak with a structured automated pipeline capable of growing with future reporting volumes.
  • The EWRB solution was architected on Snowflake as the primary data warehouse, with Oracle as a supplementary data source and Boomi as the ETL and workflow integration engine, delivering live-connected Tableau Online reports at no additional licensing cost to Hydro Ottawa.
  • Two distinct report types were built for the energy engagement: an external customer-facing report in PDF and CSV format for building owners to use in regulatory submissions, and an internal staff report with graph, PDF, and CSV outputs for account-level verification and quality assurance.
  • Pre-provisioning logic built into the Snowflake layer supports efficient fulfillment of repeat customer requests across future annual EWRB reporting cycles

Project Description

Hydro Ottawa is the local electricity distributor for the Ottawa region, responsible for delivering power to hundreds of thousands of customers and meeting a range of regulatory reporting obligations that come with that mandate. Bronson’s work with Hydro Ottawa’s Business Performance and Reporting team unfolded across two call-ups in early 2022, each scoped to a different part of the team’s analytics environment.

The first call-up centred on fleet data. The BPR team was developing Tableau reports using GPS tracking data from Hydro Ottawa’s vehicle fleet, with the goal of identifying and reporting on employee productive time across field operations. The underlying GPS data and integrations were already in place, but translating raw vehicle movement records into meaningful geographic visualizations in Tableau required expertise the team wanted external support for. Bronson was brought in to work directly alongside BPR staff in collaborative building sessions, applying Tableau’s geographic data capabilities to the fleet use case.

The second call-up addressed a more substantial operational burden: the EWRB compliance cycle. Ontario regulation requires large building owners to report annual energy and water consumption and greenhouse gas emissions. As a local electricity distributor, Hydro Ottawa must supply aggregated electricity consumption data to building owners on request to support those submissions. At the time Bronson was engaged, every step of that fulfillment process was handled manually, from authorization verification through third-party data retrieval to report generation and delivery. Turnaround times reached 21.5 days during the January-to-May peak period, and regulatory and civic pressure was expected to push request volumes steadily higher over the following two years. Hydro Ottawa engaged Bronson to automate the process entirely, using the existing Snowflake, Oracle, and Boomi technology stack.

Business Challenge

Both engagements required working within the constraints of a regulated utility environment where data accuracy, process reliability, and compliance with reporting deadlines are conditions of operation rather than aspirational targets.

The specific challenges Bronson addressed:

  • Unlocking GPS data within Tableau’s geographic visualization layer. Raw vehicle movement data in the form of GPS coordinates and timestamps does not render into meaningful fleet productivity reporting without deliberate structuring, filtering, and visualization design. The BPR team had the data but needed applied expertise to translate it into actionable geographic views of employee productive time.
  • A manual compliance process with no capacity to scale. Every stage of EWRB request fulfillment was manual at the time of engagement. Staff verified authorization, contacted third-party energy providers, gathered consumption data across multiple accounts, and assembled output reports by hand. At peak, a single request could take over three weeks to close. The prospect of growing request volumes arriving into a process with that throughput constraint represented a real compliance and operational risk.
  • Three-technology stack integration with no new licensing. The solution had to work entirely within Hydro Ottawa’s existing Snowflake, Oracle, and Boomi environment. No new tools or subscriptions were permitted. This constrained the design but also clarified it, making Snowflake the natural aggregation and pre-provisioning foundation and Boomi the appropriate ETL and workflow layer.
  • Two audiences with different report requirements. External building owners need clean, address-level annual and daily consumption summaries in formats ready for regulatory submission. Internal BPR staff need account-level breakdowns with graph visualizations for verification before reports go out. Both audiences required dynamic filtering by address and date range, and both needed to be served from the same underlying data pipeline without duplication of effort.
  • Pre-provisioning for returning customers. A key design requirement was the ability to pre-populate the data layer for repeat customers so that returning annual requests could be fulfilled quickly rather than rebuilt from scratch. This introduced data architecture considerations around property continuity, account aggregation across a property’s history, and the handling of addresses where ownership changes or lot severances had occurred.
  • Delivery timed to the peak reporting season. The EWRB engagement ran from late January to April 30, 2022. The peak reporting demand period begins in March. For the automation to deliver real value within the first cycle, a working solution had to be in Hydro Ottawa’s hands before that window opened.
  • Knowledge transfer embedded throughout, not deferred to the end. The collaborative working session model used across both engagements meant the BPR team needed to understand and be capable of maintaining every component of the solution from the moment it was handed over. That placed additional structure requirements on how building sessions were run and how documentation was produced alongside the work.

Our Solution

Bronson organized delivery across both engagements in sequential, clearly structured phases, running collaborative working sessions with the Hydro Ottawa BPR team throughout:

Engagement 1: Fleet GPS Tableau Dashboard

1. Kickoff and Data Review

Bronson attended a project kickoff with the BPR team to align on GPS data challenges, desired reporting outcomes, and session structure. Bronson then reviewed the relevant data architecture, existing Tableau report examples, and GPS data schema to develop a clear picture of the source data characteristics and visualization requirements before any building work began.

2. Collaborative Tableau Dashboard Development

Bronson worked alongside Hydro Ottawa’s technical resources in screen-sharing building sessions, applying Tableau’s geographic data capabilities to translate raw GPS coordinates and movement timestamps into visual representations of fleet activity and employee productive time. The sessions were structured so the BPR team was building alongside Bronson rather than receiving a finished product, ensuring the team understood every design decision as it was made.

3. Technical Handover

A dedicated technical handover session transferred all designs, data files, workflows, visualizations, methodology notes, and supporting documentation to the Hydro Ottawa team, with Bronson available for follow-up questions 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 documentation, and schema materials provided by the BPR team. A structured set of clarifying questions was worked through with the 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

Bronson developed detailed design requirements for the full EWRB automation solution and presented a proposed architecture for client review and sign-off. The architecture placed Snowflake at the centre as the primary data warehouse and aggregation layer, with Boomi configured to handle extraction and transformation from Snowflake and Oracle sources and routing of structured outputs to Tableau Online. Keeping the entire solution within Hydro Ottawa’s existing technology portfolio was a design constraint that shaped every architectural decision from the outset.

6. Data Engineering Workflow Development

Bronson built the data engineering workflows required to extract, transform, and structure electricity consumption data for automated report generation. Workflows handled Snowflake and Oracle data sources, aggregated consumption at both monthly (12-month rolling) and daily (365-day rolling) levels, and produced structured output files for both report types. All workflow steps, assumptions, and known limitations were documented in parallel with development.

7. Automated Report Implementation

Bronson implemented the automated reporting solution, building two report types on Tableau Online with live connections to the Snowflake-backed data pipeline. The external report delivers annual and daily consumption summaries at the address level in PDF and CSV formats for building owner use in regulatory submissions. The internal report delivers the same consumption views at the account level with graph-based visualizations and account number summaries for BPR staff verification, exportable as graph, PDF, and CSV. Both report types support dynamic filtering by address and date range, defaulting to the prior calendar year. Pre-provisioning logic in the Snowflake layer supports efficient repeat customer fulfillment across future annual cycles.

Key Deliverables

  • Three-Year Fuel Site Optimization Action Plan – A site-ranked report documenting the operational and financial status of all 21 bulk fuel sites, with specific recommendations for optimization, modification, or closure within a three-year planning horizon.
  • Long-Term Fuel Infrastructure Forecast – A forward-looking planning document addressing anticipated changes to the fuel program over an extended horizon, including the effects of fleet right-sizing, capital asset lifecycle milestones, alternative fuel policy, and evolving departmental requirements.
  • Validated M5 Fuel Consumption Dataset – A verified, structured three-year extract from the City’s M5 fleet management system covering all 21 sites and 2,100-plus vehicles, with documented field definitions, known limitations, and resolved anomalies.
  • Quantitative Site-Level Cost-Benefit Model – A configurable model translating validated fuel consumption data into cost and benefit profiles for each of the 21 sites, incorporating direct program economics and contingent risk exposure from capacity shortfall scenarios.
  • Stakeholder Consultation Notes and Site Visit Findings – Confirmed records of consultation sessions across Road Services, Corporate Real Estate, transit, Hydro Ottawa, and Ottawa Police Services, alongside direct observations from fuel infrastructure site visits.

The Impact

  • The engagement gave the City of Ottawa a site-level analytical platform that had not previously existed for its bulk fuel program, connecting decades of operational data to forward-looking planning for the first time.
  • Individual cost-benefit profiles were established for all 21 bulk fuel sites, enabling direct comparison and prioritization across the full portfolio on a documented, reproducible basis.
  • The three-year action plan gave the City a structured sequence of near-term decisions covering infrastructure modifications, tank capacity adjustments at constrained sites, and a documented analytical basis for sites under consideration for divestment or repurposing.
  • Contingent risk quantification provided program managers with an explicit economic rationale for capacity investments at sites most exposed to shortfall conditions during snow clearing and other peak demand events, replacing qualitative concern with a costed assessment.
  • Stakeholder consultations captured forward-looking departmental intelligence (including planned facility changes, fleet transition timelines, and operational constraints) that the quantitative data alone could not have surfaced, materially improving the accuracy of long-term forecast assumptions.
  • The long-term forecast tied investment and divestment decisions to capital asset lifecycle milestones and fleet composition projections, giving the City a sequencing framework for program decisions over a planning horizon extending well beyond the immediate three-year window.

The engagement extended and deepened the work Bronson began with the 2017 scoping study, demonstrating the value of sustained analytical engagement with a program as operationally complex as a citywide bulk fuel network. Integrating three years of consumption data, a site-level cost-benefit model, cross-departmental stakeholder intelligence, and a dual-horizon planning structure gave the City of Ottawa a decision-making foundation proportionate to the scale and longevity of the program it supports.

Related Projects

Related
Projects

  • Canadian Blood Services Portal Strategy and Storyboard Development

  • Bulk Fuel Sites and Infrastructure Optimization Study for the City of Ottawa

  • Background Clearance Fee Structure Redesign and Cost Recovery Modelling for the Ottawa Police Services

  • Financial Management and People Management Data Strategy for Environment Canada