Ritchie Design ServicesData & BI consultancy Book a call
Case study

From fragile reports to a production data platform

How a UK engineering and project-services group got a Microsoft Fabric platform the business can trust, audit and afford.

Sector
Engineering and project services, UK
Platform
Microsoft Fabric & Power BI
Scope
Platform, modelling, monitoring, cost, AI pilot
The situation

Data in five systems, held together by hand

The group's data was spread across several business systems:

Sage · finance Primavera P6 · scheduling Proarc · documents Net2 · building access Legacy project-tracking database

Reporting depended on pipelines and models that had grown up over time in Microsoft Fabric.

  • No safe route to Production. Changes were made directly where they ran.
  • Silent failures. Loads could fail without anyone being told.
  • Weekly breakage. Connections were tied to individual people's logins, which expired every week under the company's security policy.
  • Unknown spend. There was little visibility of what the Fabric capacity (the part they pay for) was being used for.
How it fits together

One platform, one release path

What I did

Seven pieces of work

Built a proper release process

Three environments (Development, Test and Production), every item held in source control, and an automated deployment pipeline with a manual approval gate before Production. Changes are reviewed as pull requests. Development and Test read Production's data through read-only links, so testing uses real data without copying it or putting it at risk.

120+ pull requests reviewed on this platform

Modelled the data so reports agree

A layered warehouse (raw, cleaned and report-ready "gold") built with dbt, with automated data tests on every run. Business rules live in one place instead of being rewritten inside each report.

Projects warehouse: 18 cleaned models, 25 gold tables

Made failures loud

Each pipeline alerts on failure, and a scheduled sweep catches anything that slips through, including jobs that silently stopped running. A Power BI "Platform Monitor" report shows findings, job history and capacity use in one place. A companion report shows who uses which reports, so unused content can be retired with evidence.

Took people's logins out of the plumbing

Scheduled loads and connections moved onto dedicated service accounts (service principals), including certificate-based access to SharePoint. That ended the weekly breakage, and the platform no longer depends on any one employee's account.

Measured cost before cutting it

I read capacity use per item, per hour, straight from Microsoft's metrics data. The platform was running well within its capacity. The waste was in specific places: a load step that ran on every execution but never processed a single row, and refreshes that ran more often than the business needed.

Roughly 29–53% of daily capacity budget in use

Piloted AI with evidence, not a demo

I set up a Microsoft Fabric data agent over the projects model, so people could ask questions of the data in plain English. Instead of a showcase demo, I built a scored question bank with a known answer key and measured both accuracy and cost per question.

That turned "should we roll out AI?" into a budgeting decision rather than a guess.

≈3,000 capacity units per answer · ≈57 answers a day on F2

Kept it maintainable

A live known-issues log with dated notes, a log of working mistakes with a rule to stop each one recurring, and written runbooks for deployment and disaster recovery. The client can see what is fixed, what is open and why.

≈300 issues tracked and closed
The outcome

What the business has now

  • Releases go through review and an approval gate. Nobody changes Production by hand.
  • Failed or stalled loads raise an alert, rather than being discovered from a wrong report.
  • No more weekly connection breakage from expiring personal logins.
  • Fabric capacity spend is measured per item, so savings are targeted, not guessed.
  • AI adoption has a measured cost and accuracy baseline before any rollout.
  • The client owns everything: code, documentation and runbooks sit in their own repository.
Microsoft Fabric: Warehouse, Lakehouse, Data Pipelines, Notebooks, Dataflows Gen2 dbtT-SQLPython / PySpark Power BI (Direct Lake)Azure DevOps (Git, Pipelines) fabric-cicdMicrosoft Entra service principalsFabric data agents

Running on Fabric, or planning to?

I measure before I change anything, and I leave you with a platform you own and can run. Let's talk.

Book a free 30-min call