CASE STUDY · MUNICIPAL GOVERNMENT · CANADA
Rebuilding a city's reporting when the old platform hit its ceiling
A Canadian municipal corporation had outgrown Qlik Sense. The reports were not wrong — the platform simply could not let anyone ask the next question. I led the migration to Power BI and Microsoft Fabric, rebuilding the data model, the business logic and the way the reports could be used.
6
Qlik Sense reports rebuilt in Power BI
2
developers led to a shared standard
100%
output parity proven before sign-off
2
source types unified — APIs and SharePoint
1
governed model behind every report
Built with Microsoft Fabric · OneLake Lakehouse · Dataflows Gen2 · Fabric Data Pipelines · Direct Lake semantic model · Power BI · DAX · REST APIs · SharePoint · Git integration and deployment pipelines
Role Migration lead — solution architecture, data model re-design, conversion standards, DAX re-implementation, parity validation, and leading two developers.
PROJECT AT A GLANCE
Business problem I solved
Technology stack
Industry
Municipal corporation — public sector reporting
Location
Canada
Project Type
Platform migration — Qlik Sense to Power BI and Microsoft Fabric
My Role
Migration lead — solution architecture, data model re-design, conversion standards, DAX re-implementation, parity validation, and leading two developers.
Technology
Microsoft Fabric · OneLake Lakehouse · Dataflows Gen2 · Fabric Data Pipelines · Direct Lake semantic model · Power BI · DAX · REST APIs · SharePoint · Git integration and deployment pipelines
Scale
6 Qlik Sense reports rebuilt on one governed Power BI semantic model
Users
Directors, department managers, analysts and finance — each seeing only what their role permits
SECTION 1 · WHAT BUSINESS PROBLEM I SOLVED
The reports were not broken. They had stopped being useful.
A municipal corporation runs on evidence. Figures go into council papers, budget submissions, service reviews and public reporting, and every one of them has to be explainable. The organisation had years of reporting built on Qlik Sense that produced correct numbers and then stopped. A director could see that a figure had moved. They could not see why, and finding out meant asking someone else and waiting.
The platform could answer, but it could not explore
Reports showed what had been designed into them. There was no meaningful drill-through to the records behind a total, limited cross-filtering between views, and no way for a user to follow a question sideways. Every report was a destination rather than a starting point.
Every new question became a development request
Because users could not explore, they raised tickets. A question that should have been answered during a meeting instead entered a queue and came back days or weeks later — by which time the decision had usually been made without it.
The business logic was locked inside the platform
Years of rules lived in load scripts and platform-specific expressions that only a small number of people could read. For a public body that is a governance problem as much as a technical one: figures published in the organisation’s name depended on logic almost nobody could independently verify.
Data was being assembled by hand
Information arrived from system APIs and from SharePoint lists maintained by individual departments. Pulling it together was a manual, repeated effort, which made the reporting cycle slow and made it fragile whenever the person who normally did it was unavailable.
The data model had grown, not been designed
The underlying model had accumulated years of workarounds layered on top of each other. It worked, but it had become the reason nothing new could be added quickly — and it was the single biggest obstacle in the whole project.
What I was actually asked to deliver
The brief sounded like a conversion: move six reports from one tool to another. It was not. Delivering the same six reports with the same limitations would have solved nothing. The real requirement was to rebuild the foundation so the organisation could explore its own data — while proving, figure by figure, that nothing had changed in the process. In government, a migration that quietly alters a published number is a failure regardless of how good the new platform is.
SECTION 2 · WHICH TECHNOLOGY I USED
Chosen to serve two audiences at once
Staff who needed to explore data without training, and a public body that needs every figure traceable, governed and defensible.
| Layer | Technology | Why it is there |
|---|---|---|
| Source | REST APIs | Core municipal systems are read through their APIs on a schedule, rather than by anyone exporting files by hand. |
| Source | SharePoint lists and files | Departments maintain their own trackers and reference data in SharePoint. Rather than fighting that, the platform reads it directly and treats it as a governed source. |
| Ingestion | Fabric Data Pipelines | Orchestrates API extraction with paging, retries and scheduling, so a failed call is handled by the system rather than noticed by a person. |
| Ingestion | Dataflows Gen2 | Brings SharePoint content in through a reusable, refreshable definition, so the same source can feed more than one thing without being rebuilt. |
| Storage | OneLake Lakehouse | One copy of the data for all reporting, layered from raw through cleaned to reporting-ready. Nothing is duplicated per report. |
| Transformation | Fabric notebooks and SQL | Where the loose source structures are reshaped into a proper dimensional model. This is where most of the project’s real work happened. |
| Data model | Power BI semantic model, Direct Lake | A star schema reading straight from OneLake. It gives import-mode speed without an import refresh cycle, so there is no nightly window to miss. |
| Business logic | DAX measures and calculation groups | Legacy expressions re-implemented as named, documented measures — defined once, reused by every report, readable by anyone who knows the tool. |
| Security | Row-level security | Departmental and role-based access enforced in the data, so a shared model can serve groups with different entitlements. |
| Change control | Git integration, deployment pipelines | Every change is versioned and promoted through development, test and production. With a team of three building in parallel, this is what prevents work colliding. |
| Validation | Purpose-built parity reports | Reports whose only job is to compare new output against legacy output across many filter combinations. Parity was proven, not asserted. |
| Governance | Workspace and domain structure, sensitivity labelling | Public-sector reporting needs clear ownership, controlled access and a visible audit trail of who changed what. |
SECTION 3 · HOW I BUILT IT — THE METHOD
A re-architecture with a parity obligation, not a conversion
Most platform migrations fail in one of two ways. Either the new system is a copy of the old one and nothing improves, or it improves but the numbers move and nobody trusts it. The method below is designed to avoid both at the same time.
The approach
I treated this as a re-architecture with a parity obligation, not a conversion. That framing drove every decision: rebuild properly underneath, and prove at every checkpoint that the output the organisation had been relying on had not changed.
How the data travels
Two very different kinds of source had to end up in one structure: system APIs that return records on request, and SharePoint content maintained by people. Both now land in the same place and are shaped by the same process.
The hardest part: the data model
This was the real project, and it is the part a straight tool-to-tool conversion would have got wrong. The legacy platform joins tables automatically wherever field names happen to match. That is convenient at first and corrosive over time: relationships form by accident rather than by design, and when they misbehave they get patched with workarounds that themselves become permanent. Layer several years of that on top of each other and you have a model that produces correct answers by habit, but that nobody can safely change.
Power BI does not work that way, and that is a feature. It requires relationships to be declared, grain to be defined, and filters to flow in a known direction. So the model could not be copied across — it had to be worked out again from the business meaning underneath it. That meant tracing what every table actually represented, deciding what each fact measured and at what level, separating the things being measured from the things used to slice them, and rebuilding the whole structure as a star schema with single-direction filtering.
Reading the APIs: why pagination was the hard part
APIs rarely hand over everything at once. They return data in pages, and the caller has to keep asking for the next one until there is nothing left. On a small dataset that is a detail. Across years of municipal records pulled on a schedule, it becomes the part of the ingestion that determines whether the data is complete — and incomplete financial or service data is worse than no data, because nobody can see that it is missing.
Several things make it harder than "loop until empty"
The data moves while you are reading it.
A naive page-by-page read using offsets assumes the underlying set is frozen. It is not. If a record is inserted while you are on page four, everything shifts down by one — you read a record twice and miss another entirely. Neither shows up as an error. The extract simply comes back slightly wrong, every time, in a way that is very difficult to spot later.
Every API signals "last page" differently.
Some return a continuation token, some a next-page link, some just fewer rows than the page size, and some an empty array. A pipeline that assumes one convention silently truncates when it meets another.
Deep paging gets slower the further in you go.
Offset-based reads make the source system skip everything before the requested position, so page 900 costs far more than page 9. On a large history this is where extraction jobs quietly start timing out.
Long runs hit limits.
Rate limiting, throttling responses and authentication tokens that expire mid-extraction all become real once a job runs for a sustained period rather than a few seconds.
Failure at page 800 must not mean starting at page 1.
Without checkpointing, one transient network fault costs the entire run.
How it was handled
- Keyset reads rather than offsets wherever the API supported it, ordering by a stable key so shifting data cannot cause records to be skipped or duplicated.
- Continuation tokens and next-page links followed natively by the pipeline's pagination rules, with the end condition defined explicitly per API rather than inferred.
- A high-water mark on modification date, so each scheduled run reads only what changed — with a deliberate overlap window to catch records back-dated after the previous run.
- Landing raw, then de-duplicating on the natural key, so an overlap or a retried page is harmless by design rather than by luck.
- Retry with backoff on throttling and transient failures, with token refresh handled inside the run so long extractions do not expire partway through.
- Checkpointing and restartability, so a failed run resumes from where it stopped instead of re-reading everything.
- Row count reconciliation against the source where the API exposes a total, so a short extract is caught by the system rather than discovered weeks later in a report.
The result is that extraction is boring, which is the goal. Nobody has to watch it, nobody has to re-run it by hand, and nobody has to wonder whether last night's numbers are complete.
Proving nothing changed
Rebuilding the foundation creates an obvious risk: if a published figure moves, the organisation has to explain why — possibly in public. So parity was treated as a deliverable in its own right rather than as testing at the end. I built reports whose only purpose was comparison, placing legacy output alongside new output across many combinations of filters and time periods. Differences were investigated rather than adjusted: in several cases the difference exposed an undocumented rule in the old logic that then had to be deliberately decided on rather than silently inherited. Nothing was released until its figures matched and a business owner had signed the comparison.
What the rebuild unlocked
Once the model underneath was sound, capabilities became possible that the old platform could not offer at any level of effort. This is the part users noticed.
SECTION 4 · WHAT THE ORGANISATION GOT OUT OF IT
A migration is only worth doing if the organisation can do something it could not do before
Questions get answered in the meeting
The gap between noticing something and understanding it collapsed from weeks to seconds. A manager who sees a number move now clicks it and sees the records behind it, instead of raising a request and waiting for a cycle that has already passed.
Outcome · exploration replaced the reporting queue
The business logic came out of the black box
Rules that existed only inside platform-specific scripting are now named, documented measures in a model that any competent practitioner can read and verify. For a public body accountable for the figures it publishes, that shift from tribal knowledge to documented logic is a governance outcome as much as a technical one.
Outcome · key-person risk removed, figures independently verifiable
Numbers survived the move
Every report was proven against its legacy output before release and signed off on the evidence. Users moved to a new platform without being asked to accept a change in the figures they already relied on — which is the difference between a migration people adopt and one they resist.
Outcome · full output parity, and trust carried across
Manual data collection stopped
APIs and SharePoint content now flow in on a schedule, orchestrated and retried automatically. The reporting cycle no longer depends on a person remembering to pull a file, and no longer stops when that person is unavailable.
Outcome · a repeatable process instead of a recurring task
Departmental data became organisational data
SharePoint trackers that had been the private working files of individual teams now sit alongside core system data in one model. Questions that span departments became answerable for the first time, because the information finally shares a structure.
Outcome · joined-up reporting across departments
No refresh window to miss
Because the model reads OneLake directly, there is no nightly import cycle that can overrun or fail. One copy of the data serves all reporting, which also removes the duplication that grows every time a new report is added.
Outcome · current data, lower running cost, less to go wrong
A foundation that can be built on
The old model could not be extended without risk, so it froze. The new one has declared structure and documented rules, is versioned through Git, and is promoted through controlled environments. Adding the seventh report is now routine work rather than a project.
Outcome · reporting can keep developing after handover
SECTION 5 · SUMMARY
In business terms, and in technical terms
In business terms
- A municipal corporation had reporting that produced answers but could not be questioned. It can now be explored by the people who need it.
- Six reports moved platform with their figures intact and proven, so users kept their trust in the numbers through the change.
- Business rules that lived inside one platform and a few people’s heads are now documented and independently verifiable — a real requirement for a body accountable in public.
- Manual data collection from systems and departmental SharePoint files was replaced by an automated, scheduled process.
- Departmental information and core system data finally sit in one structure, making cross-department questions answerable.
- Adding new reporting is now routine rather than a project, so the investment keeps returning after handover.
In technical terms
- REST APIs and SharePoint ingested through Fabric Data Pipelines and Dataflows Gen2, with paging, scheduling and retry handled by the platform.
- OneLake Lakehouse as a single layered store — raw, cleaned, reporting-ready — replacing per-report copies of data.
- Loosely structured associative model re-architected into a star schema with declared grain and single-direction filtering.
- Legacy platform expressions reverse-engineered and re-implemented as documented DAX measures and calculation groups.
- Direct Lake semantic model reading OneLake, removing the import refresh cycle entirely.
- Row-level security for departmental and role-based access on a shared model.
- Git integration and deployment pipelines across development, test and production.
- Purpose-built parity reports comparing new output to legacy across filter and period combinations, as a release gate.
- Conversion standards, review checkpoints and promotion criteria defined for a team of three.
My role. I led the migration end to end — solution architecture, the re-design of the data model, the conversion standards the team worked to, re-implementation of business logic in DAX, and the parity validation that gated every release. I led two developers through the build, defining the review checkpoints and the evidence required before any report could be promoted.
What this project demonstrates. That I can take reporting built on someone else's platform, understand the business logic inside it well enough to rebuild it properly rather than copy it, prove to a cautious public-sector client that nothing has changed — and leave them with a foundation they can extend without me.
NEXT PROJECT
Retail Analytics
US · Footwear