Operations Analytics Layer
One modelled data layer and one metric definition, replacing parallel spreadsheets that disagreed with each other.
- Sector
- Operations
- Discipline
- Analytics
- Technology
- SQL, Python, PostgreSQL, Pandas, Power BI
The problem
Operational reporting had grown into a set of spreadsheets, each maintained by a different person, each defining core measures slightly differently. Meetings started by reconciling numbers instead of acting on them, and the reporting stopped whenever its owner was away.
What we built
We modelled the underlying data once, wrote each metric as a single tested definition, and built dashboards on top of that layer. Definitions live in version control next to the tests that guard them, so a change to a measure is a reviewed change rather than an edit inside a workbook.
Architecture
How the system fits together.
- 01
Define
Metric definitions written down and agreed with the people who use them, including edge cases and exclusions.
- 02
Model
Source data shaped into analytical tables with explicit grain, keys and documented assumptions.
- 03
Test
Automated checks on uniqueness, referential integrity, null rates and value ranges, run on every refresh.
- 04
Publish
Operational and management dashboards reading exclusively from the modelled layer.
- 05
Refresh
Scheduled refresh with alerting when data is late, incomplete or fails a test.
Outcome
What the system delivers.
Capability delivered, stated plainly. We do not attach business metrics to this work that we are not in a position to verify.
- One tested definition per metric, in version control rather than in a workbook
- Reporting that refreshes without a named person running it
- Data quality failures surfaced as alerts instead of appearing as wrong numbers
- Dashboards traceable back to the model and assumptions behind them
- SQL
- Python
- PostgreSQL
- Pandas
- Power BI