Translating a Power BI proof of concept into a production progress engine

The calculation work that the dashboard depended on did not exist as a written specification. It existed as a Power BI proof of concept that a domain expert at the partner organisation had built up over months. Period-based benchmarks (with different period counts for different courses, twelve for one apprenticeship, twenty-one for another), off-the-job hours, e-portfolio assessment progress, course component completion, and learner breaks all fed into a RAG status that tutors had been taught to read.

There were two related problems. The first was that the calculations had to run in production, in the dashboard's progress engine, against live consolidated data. The second was that Power BI was not going away; the wider organisation was going to keep using it for internal management information and tutor reporting, and the domain expert was actively iterating on the calculations there. Whatever we built had to honour the same logic Power BI did, without forcing Power BI to become a downstream consumer of the new engine, and without putting the domain expert in the position of having to read C# code to confirm a calculation was right.

The data side of the calculation made it harder. Some inputs were available in real time from Campus (PostgreSQL). Some only arrived in overnight OneFile CSV exports. Some lived in benchmark CSV files on SharePoint, maintained by hand. Some were tracked in per-learner ITP spreadsheets. The calculation logic itself did not care where the inputs came from, but the engine had to.

I treated the Power BI proof of concept as the validated specification rather than as a thing to be replaced. The domain expert's iterations there were the ground truth for the calculations. Anything the progress engine produced had to match Power BI's output for the same inputs, until and unless the domain expert agreed otherwise.

Inside the progress engine I separated the calculation logic from the rest of the engine deliberately. Three concerns lived in three different layers:

1. Ingestion: pulling data from Campus, OneFile, SharePoint and the ITP spreadsheets, and landing it in a consolidated learner record with explicit freshness metadata.
2. Calculation: taking that consolidated record and producing the period-based RAG state, off-the-job progress and the rest of what the dashboard needed, with no awareness of where any input had come from.
3. Persistence and exposure: storing the calculated results, exposing them to the BFF, and surfacing freshness alongside the data.

The split was load-bearing for two reasons. It made the calculation layer testable in isolation, which mattered both for confidence and for the sign-off mechanism described below. And it meant that when ingestion changed (as it did, drastically, when the OneFile pipeline had to be pivoted to source SFTP two weeks before contract end), the calculation layer was unaffected.

The sign-off mechanism was the key piece for the domain expert. For any change that touched the calculation logic, I produced a side-by-side: a representative set of learner records, run through Power BI's calculation, and run through the engine's calculation. Differences had to be either zero or explainable. The domain expert could verify the engine against Power BI without reading any C# code, by looking at numbers in the representation he was already fluent in. That kept the validation loop tight and let the calculation logic evolve without forcing the partner organisation through a code review process.

Finally, Power BI was kept in place for internal MI and tutor reporting rather than retired. The progress engine and Power BI both consumed source data; neither depended on the other. If the domain expert iterated the Power BI calculations, the engine would track the change after a sign-off cycle, and Power BI continued to be useful for the internal reports the team had been running for years.

The progress engine shipped as part of the wider MVP at the end of the engagement, with calculations that matched Power BI's output for the agreed reference set. Tutors had been seeing Power BI output ahead of the engine going live, so the numbers in the dashboard were the numbers they were already used to reading. There was no period in which the dashboard and Power BI told tutors different stories about the same learner.

The architectural separation between ingestion, calculation and persistence paid off twice. Once when the OneFile ingest pivoted (the calculation layer did not move). Once when the domain expert iterated on the calculations themselves (the ingestion and persistence layers did not move). I have come to think of that three-layer separation as the load-bearing decision in any consolidation engine where the calculation logic is genuinely owned by a domain expert outside the build team. Without it, every change at one boundary forces a change at the others, and the team ends up rebuilding the system every time the domain expert changes their mind.

The other outcome I will defend is the decision to keep Power BI alive rather than absorbing it. There is a temptation in this kind of engagement to "consolidate" by pointing every consumer at the new service. That looks tidy on the architecture diagram and creates a single point of failure for a calculation the domain expert needs to be able to iterate on quickly. Letting Power BI keep its life as an MI tool, with the engine as a parallel consumer of the same logic, was the right answer for this team and this domain. I would make the same call again.

The calculation, briefly

Each apprenticeship was divided into periods. The exact number depended on the course (twelve for one apprenticeship, twenty-one for another). Each period had an expected completion benchmark. A learner's progress was assessed against the benchmark for the current period, taking into account off-the-job hours logged, e-portfolio assessments completed, course component completion and any approved breaks that adjusted the period clock. The output was a RAG status (red, amber, green) plus the underlying numerical breakdown that fed the tutor and supervisor views.

The calculation was not arithmetically deep. It was operationally deep: there were many inputs, several edge cases, and the domain expert was actively refining the rules as the engagement progressed.

Three layers, one engine

The progress engine had three layers with explicit boundaries:

1. Ingestion. Adapters per source. Campus was queried in real time. OneFile arrived as overnight CSV exports (originally via an upstream Azure Blob pipeline, later directly from SFTP after the pivot covered in a separate case study). Benchmark CSVs were ingested from SharePoint. ITP data came in as per-learner spreadsheets. The output of this layer was a consolidated learner record with freshness metadata attached to each field.
2. Calculation. Pure functions over the consolidated record. No awareness of source systems, no I/O, no freshness model leaking in. The output was the RAG state and the underlying breakdown.
3. Persistence and exposure. The calculated results stored against the learner identity, exposed to the BFF with freshness propagated through, and made available to any future consumer.

The calculation layer was deliberately the thinnest of the three. That kept the boundary obvious and made the sign-off mechanism trivial to implement.

Sign-off mechanism

For any change touching the calculation layer, I generated a comparison: a reference set of learners, the Power BI output for those learners, and the engine output for the same learners. The domain expert reviewed the side-by-side, signed off on differences (zero, or "expected, here is why"), and the change was merged. That made Power BI the authority during the build, with the engine catching up on a deliberate cadence rather than fighting Power BI for ownership of the calculations.

A more conventional approach would have been to write a specification document and have the domain expert review it. That works on paper. In practice, asking a domain expert who has spent months building calculations in Power BI to verify a textual specification of the same logic is asking them to do their work twice in two media. Comparing one set of Power BI numbers to another set of engine numbers is the same task they were doing already, only with the engine added.

Why Power BI stayed alive

The temptation in any "consolidation" engagement is to point every consumer at the new service and retire the old tool. I argued against that here. Power BI was the working environment for the domain expert, and the place internal management information was already being produced. Forcing it to become a downstream consumer of the engine would have made every Power BI report dependent on the engine's availability, and would have forced calculation iteration through a code-deploy cycle rather than a Power BI refresh.

Keeping Power BI alongside the engine, with both consuming source data, kept the domain expert's iteration cycle intact and gave the engine a permanent reference point to validate against. The architectural cost was low. The operational benefit was high.

What I would do differently

I would invest earlier in an explicit, machine-readable representation of the period and benchmark configuration, separately from either Power BI or the engine. We ended up with the configuration encoded twice, once in Power BI and once in the engine, with the sign-off mechanism keeping them aligned. That worked, but it is a cost I would prefer to remove. A single source of truth for period definitions and benchmarks, consumed by both Power BI and the engine, would have been a cleaner answer. I did not push for it on this engagement because the deadline was elsewhere; on a future engagement of this shape I would.