Anglian Water is one of the UK’s leading water and water recycling utilities, serving close to seven million people across the East of England. Covering the largest geographic area of any water company in England and Wales, the organization operates tens of thousands of kilometers of water mains and sewers, supported by a substantial field workforce and a wide network of alliance partners. Day-to-day operations depend on a portfolio of specialist applications, including PACE, a system used to capture and manage core operational activity across the business.
The objective was to modernize PACE reporting by extracting operational data from Microsoft Access into Azure Databricks, preserving the integrity of existing SSRS report logic, and delivering validated, analytics-ready datasets to Power BI. The organization wanted a reliable, well-documented path from PACE to modern reporting, with automated refresh and failure monitoring built in from the start.
Energy and Utilities
Azure Databricks, Power BI, Power Automate
Data Engineering, Reporting and Analytics Modernization
Existing operational reports depended entirely on Microsoft Access and SSRS. Before any migration could begin, the team had to fully understand the relationship between .accdb database files, .rds shared data sources, and .rdl report definitions.
Years of business logic, SQL queries, joins, filters, calculations, and parameters were embedded directly in RDL report definitions rather than documented separately, making the true reporting requirements hard to see at a glance.
The source tables and columns required for reporting were not explicitly documented anywhere and had to be reverse-engineered from the SSRS report logic.
With operational data continuing to live in Access while a parallel copy was built in Databricks, the two environments needed ongoing validation to ensure they stayed synchronized and fit for reporting purposes.
Legacy report datasets needed to be re-platformed so modern analytics tools and Power BI could consume them, rather than keeping them locked inside SSRS.
The new reporting solution needed a dependable dataset refresh process, with monitoring and automated notifications if a refresh failed.
Analyzed the existing PACE reporting architecture and documented the complete data flow from PACE Application → Microsoft Access → SSRS → Databricks → Power BI, giving the client a clear reference map of the entire ecosystem.
Reviewed .rdl files in detail to extract the SQL queries, source tables, joins, filters, parameters, and calculations embedded within each legacy report.
Used the extracted report logic as the authoritative reference for identifying exactly which datasets needed to be migrated into Databricks, avoiding guesswork around scope.
Supported the extraction and availability of the required PACE data within Azure Databricks, establishing the foundation for analytics and reporting workloads.
Validated Databricks tables and metadata against the existing Access environment where required, confirming that the migrated data faithfully represented the source system.
Developed reporting datasets compatible with Databricks SQL and made the validated data available for consumption by Power BI.
Supported the build-out of Power BI reports and validated their outputs directly against the existing SSRS reports to confirm parity before cutover.
Implemented and supported scheduled dataset refreshes using Power Automate, including failure notification handling so issues could be caught and resolved quickly.
Produced technical documentation and work instructions to support knowledge transfer, ongoing maintenance, and future report migration efforts.
Established a clear, end-to-end understanding of the existing PACE, Microsoft Access, SSRS, Databricks, and Power BI reporting architecture.
Identified RDL files as the key source for understanding legacy report dependencies, source tables, SQL queries, joins, filters, and calculations, enabling a structured approach to migrating and validating reporting logic in Databricks.
Supported the transition of validated datasets toward Power BI-based analytics and reporting, improving the organization's ability to trace data end-to-end from the PACE operational system through to analytics reporting.
Established a foundation for automated dataset refreshes and failure notifications using Power Automate, along with documentation to support future report analysis, migration, validation, and maintenance.
Tell us what you're looking for and we'll get you connected to the right people.