Engineering around three missing primitives — incremental Power BI refresh on a 2022 stack.

A daily-fresh operations-exception dashboard for a national postal and logistics operator, shipped when the platform had no orchestrator, no writable database, and no native partial-refresh in Power BI. Eight alarms, one shared report, one Azure AD app I had to build so the pieces could talk. Still in production four years later.

Before this dashboard existed, the operations-quality team published the same information as a SharePoint page of screenshots. Someone on the team ran the SQL queries each morning, exported the results to Excel, captured screenshots of the eight alarm tables, and updated the SharePoint page by hand. Daily, business days only. Logistics operations run twenty-four hours a day. The night shift, the weekend, and anyone deciding outside the morning window were looking at what had happened, not what was happening.

The brief

The director of operations quality asked my own director for an embedded analyst: someone to optimise the SQL queries already running against the lake replica and rebuild the operations-exception view as a Power BI report. I was assigned, and I went, deliberately. I was already on the Data and Analytics team; the embed was a chance to work closer to the end consumers of my reports and grow business acumen on the operational side.

The audience was operations management, upper and middle. They were expected to watch the dashboard for situations they could act on: packages that needed re-prioritising for next-day delivery, exceptions accumulating in particular regions, alarm counts trending the wrong way. Push notifications and routing-to-named-people were not in scope. The dashboard was the surface, and a refresh that kept up with the operational day was the deliverable.

The team I was embedded with wasn't a data team. It was an operations-support business team. The most data-capable person on it had RPA experience and was learning SQL. The team had also taken on, of their own initiative, the central data team's philosophy of standardising KPI definitions, applied to operational metrics: a goal of getting operations and reporting on the same definitions so conversations about a missed SLA or an alarm count meant the same thing on both sides of a meeting. The alarm dashboard ended up being one of the canonical surfaces where those team-level standardised definitions lived.

What was there before

The same eight alarms had been published for some time as a SharePoint page of screenshots. Each morning someone on the team ran the queries, exported the results to Excel, screenshotted the relevant cells, and updated the SharePoint page. The page was the dashboard. The screenshots were the visuals. That set of mechanics carried a stack of problems the new system had to dissolve, not paper over:

• Daily, business days only. Logistics is twenty-four-seven. Most of the operational week ran on stale information, and there was no way for the night shift or weekend coverage to see the current state.
• Screenshots are static. A regional manager who wanted to see just their region had to ask for an export and wait, or learn to read someone else's regional cut. Self-service didn't exist.
• No history layer. Each refresh overwrote the previous state. There was no way to track whether a given alarm was trending down, up, or oscillating week-over-week.
• Manual labour put the whole pipeline on one person's calendar. If that person was sick, the page was stale. The pipeline had a bus factor of one.
• Eight alarms scattered across SharePoint sections. No overview, no comparison, and the cognitive cost of jumping between sections was a tax on the people who most needed the information.

The constraints that shaped everything

CTT runs a federated data model. A central Data and AI team owns the platforms (Azure Data Factory, the full Databricks workspace, Azure Synapse, SSIS where it fits, Grafana for pipeline observability) and the standardised KPI definitions at the organisation level. Business teams consume from governed data sources (what lives where, who has access) and build their own tooling on whatever they can get permission for. The solution I built wasn't sanctioned, wasn't standardised, wasn't even widely discussed. It was a bespoke invention to solve a specific operational need within what this team could actually touch.

What the team had access to:

• Databricks SQL. Governed read access to a recent-months replica of the lake (raw-to-prepared views, not the full lake).
• Power BI. Existing licenses and existing usage; the team already consumed reports.
• SharePoint. Universal, no friction.
• Power Automate. Which my colleague and I specifically requested access to after concluding it was the best orchestration path on the table.

Three primitives I needed and didn't have inside that accessible toolkit shaped every design choice from here.

1. No orchestrator that this team could run

The proper orchestrator (Azure Data Factory) sits with central D&AI by design. Operating it requires data-platform skill the support team didn't have, and the D&AI team didn't have bandwidth to build and operate a custom pipeline for this use case at the cadence the operations side needed. Power Automate was the orchestration we got permission to use. It is built for office workflows (approvals, file moves, email triggers) rather than data pipelines: the wrong abstraction layer for what I needed in the abstract, and exactly the right one for this team in practice, because the RPA-experienced colleague could maintain it after I left.

2. No writable database that this team could write to

Databricks SQL was the governed source, read-only. Notebook entitlement, Databricks Jobs, write-back into Databricks SQL, and Synapse write permissions all sit on the central-platform side of the line. The historic layer the brief required, so management could track improvement on alarms over time, had nowhere to live inside what this team could touch except file storage. SharePoint document libraries became the database by default.

3. No partial-refresh primitive in Power BI

This one is the only company-wide platform gap. A full refresh of the reporting dataset took more than thirty minutes. An individual alarm query took one to ten. To deliver any single alarm fresh under the native model, the whole dataset would have to be re-pulled, which stacked thirty minutes of dataset overhead onto every ten minutes of useful work. Power BI itself, in 2022, exposed no native way to refresh a single table inside a dataset. That primitive is what would have made this trivial, and it didn't exist.

The architecture

Power Automate is the orchestrator. Inside each per-alarm flow, six action steps run end to end. The built-in Power Automate connectors cover four of them (trigger refresh, run DAX query, create CSV table, write to SharePoint). The two they didn't cover at the time (read a dataset's refresh status, partially refresh a dataset) I built as a custom Power Automate connector, backed by an Azure AD app I registered for this. The connector exposes those two verbs in the Power Automate action picker alongside the built-ins, so any future flow in the tenant can use them without re-implementing the REST plumbing.

Eight alarms, eight flows, each on its own schedule, each writing to its own file in a SharePoint document library, all converging on the same Power BI reporting dataset. For each alarm the flow runs as follows:

• Schedule trigger. Power Automate fires at the time of day relevant to that specific alarm.
• Refresh the writer dataset. Built-in Power BI action triggers a refresh on the alarm's dedicated writer dataset.
• Poll for refresh status. My custom connector's first action checks the Power BI REST API every 30 to 60 seconds until status leaves Unknown. The built-in connector at the time didn't return refresh status, which is why this action had to exist.
• Run the DAX query. Built-in Run-a-query-against-a-dataset action executes against the refreshed writer dataset, returning the alarm's current row set.
• Write the result as a CSV. Power Automate's Create CSV table action serialises the rows; the file is written to a SharePoint document library, one file per alarm.
• Partial-refresh the reporting dataset. My custom connector's second action POSTs to /datasets/{id}/refreshes with an objects body. This is the Enhanced Refresh API, which had just shipped from Microsoft in 2022, and it lets you refresh a single table inside a dataset instead of the whole thing. On the first and middle flows of the day the same call also refreshes the dimension tables, which change rarely enough that twice-daily is sufficient.

Why per-alarm writer datasets

Two reasons made this the right shape, given the constraints. First, each alarm had its own time of day. The example alarm (packages that didn't reach destination by end-of-day yesterday) made sense to refresh first thing in the morning, when the next-day prioritisation decision was actually being made. Others were pertinent mid-morning, after lunch, mid-afternoon. The cadence wasn't uniform across the eight; tying them to a single shared writer dataset would have forced all of them onto whichever schedule the shared dataset ran.

Second, failure isolation. A slow query or a failed refresh on one alarm shouldn't take the other seven down with it. Per-alarm writer datasets meant the worst-case impact of a failure was localised to one of eight surfaces in the dashboard, and the rest of the day's flows kept running.

Why the reporting dataset is decoupled from the reports

The reporting dataset and the consuming reports were published separately, with the reports live-connecting to the dataset rather than embedding their own copy of the model. Three reasons made this the right shape.

First, corruption resilience. Early in my time at CTT we'd had a single-pbix file (dataset and visuals embedded together) corrupt in a way that took both layers down at the same time. Decoupling them put the model and the report on independent failure paths: a problem with one no longer cascaded into the other.

Second, deployment-cadence decoupling. Visual changes (a chart, a layout, a colour, a tooltip) could be published without re-publishing the dataset. On a single-pbix file every report change re-uploads the dataset and re-triggers its refresh cycle, which makes visual iteration slow and expensive to ship. With the dataset decoupled, the visual layer iterated at its own cadence without paying refresh cost on every change.

Third, model reuse. A second or third report could live-connect to the same reporting dataset without re-modelling. For this build that ended up being one report rather than several, but the pattern kept the option open at no extra cost.

When one alarm outgrew the CSV-write action

One of the eight alarms returned a result set large enough to exceed the CSV-write action's row ceiling. The Create CSV table action in Power Automate truncates past a certain size, which means the obvious failure mode (the dashboard shows nothing) is the safe one. The dangerous failure mode is the one I had to engineer against: the dashboard shows what looks like complete data but isn't.

The pattern I used is the canonical community count-and-page recipe (the one Curbal documented):

• Count the alarm's total row count via a separate DAX query.
• Initialise Min Rows = 0 and Max Rows = batch size (I used a conservative 15,000).
• Do Until loop until Min Rows >= total.
• Inside the loop: modify the DAX query to filter to the Min..Max slice, write that slice to its own CSV chunk, increment Min and Max.
• Append all chunks into a single CSV after the loop exits.

It's the kind of pattern you build when the platform you're on hasn't anticipated your data shape. The value of citing the community recipe rather than reinventing it is partly speed and partly debug-ability: when something goes wrong months later, the named pattern is searchable.

The failure path

Each Power Automate flow had retries on the API calls. On persistent failure, after retries exhausted or the poll loop exceeded Power Automate's own max-execution time, the flow sent an email to my team.

The consumer-facing surface had no failure indicator. If a writer flow failed, the corresponding table in the reporting dataset held yesterday's data, and the dashboard rendered as if nothing was wrong. From the operations-management perspective, the data looked current.

That asymmetry (observable failures in my team's inbox, invisible failures on the dashboard) is the most honest piece of debt in this design. A modern rebuild would surface refresh state in the dashboard itself; a 2022 rebuild on Power Automate without modifying the dashboard layer would have meant adding a "last successful refresh" column to every alarm's data source, which would have created new failure modes of its own.

Trade-offs

Asked latency vs delivered latency

The brief wanted data as fresh as possible after each operational event. The thirty-minute full-refresh ceiling on the previous Excel-and-screenshots workflow had already been a previously negotiated improvement. What I delivered was a per-alarm refresh once per day, scheduled to the time of day each alarm's underlying operational decision was actually being made. The example alarm (packages that didn't reach destination by end-of-day yesterday) makes sense to refresh first thing in the morning, because the decision the dashboard supports (re-prioritise for next-day delivery) happens once, in the morning. The others land mid-morning, after lunch, mid-afternoon, depending on the operational rhythm of each.

That isn't real-time, and it isn't even hourly. It is fresher than the previous solution by construction (the previous solution was business-days-only, manual, screenshot-based), and it is the right cadence for the actual decisions the dashboard supports. The gap between asked latency and delivered latency was acknowledged inside the team and didn't get pushed back on, because nobody could point to a decision the operations side was making intra-day that required the same alarm refreshed twice.

The product assumption that didn't fully survive

The design assumption was that operations management would prefer a visual report tracking improvement over time (trends per alarm, regional breakdowns) to receiving Excel lists by email. The actual consumption pattern, observed over years: the dashboard is consistently one of the most-viewed reports in the organisation, and most users export to Excel anyway, to filter and slice into their own scope. The visualisation is the entry point; the Excel export is the working tool.

Upper management does watch the trend visualisations in their scope, which means the historic layer isn't unused. It's just narrower than I designed for. The next iteration the team is now building (direct Excel delivery to named people) reflects this honestly: the dashboard becomes less central, the data-as-Excel becomes more central, and the engineering underneath (per-alarm queries, historic CSVs, scheduled refresh, the custom connector) still does the work. I think they're right to be moving in that direction.

Outcome

The dashboard went into production in 2022 and is still running. It is consistently the #1 or #2 most-viewed Power BI report across the organisation, depending on the day. The eight alarms it surfaces are still the same eight (the categories of operational exceptions that warrant management attention haven't shifted). The underlying engineering (writer flows, CSVs on SharePoint, the custom Power Automate connector, the partial-refresh path through the Enhanced Refresh API) is still operating in the background.

The dashboard also contributed to the team's KPI-standardisation work. It became one of the canonical surfaces where the team's standardised operational metric definitions lived, which made cross-team conversations about a missed SLA or an alarm count resolve faster because everyone was looking at the same computation. That's a softer impact than the engineering substance and harder to point at as a discrete deliverable, but it is the kind of secondary contribution that compounds over years.

What I'd build differently

Back then, with hindsight (same constraints, same access boundary)

If I were rebuilding this for the same team on the same accessible toolkit, with the benefit of having watched the original run for four years, the shape would change in six places: five user-facing, one architectural.

• A single alarms page. All eight alarms surfaced at a glance with their counts, instead of one page per alarm with a separate summary page at the end.
• Customised email digests. Each user gets the lists relevant to their scope sent to them, instead of opening the dashboard and exporting and filtering manually. This is the direction the team is moving in anyway; it should have been the design from the start for the middle-and-lower-management audience.
• Intra-day refresh per alarm. The lists shrink as the day progresses as situations get resolved. That visible operational progress is something middle and lower management would have noticed and used.
• A separate slim facts table for trends. Just amount-of-packages-per-alarm at start-of-day and end-of-day, decoupled from the per-alarm working lists. Sources the upper-management trend view without coupling it to the operational surface.
• Push notification on refresh. A short message to the relevant team channel or inbox when an alarm's data updates, with the new count. Closes the loop the original brief explicitly didn't include but the operational rhythm would have welcomed.

The architectural change: collapse the eight near-identical per-alarm flows into a single parameterised flow driven by a configuration table (alarm name, schedule, query, target table). A unified flow is more elegant, easier to maintain when something changes uniformly across all alarms (a new retry pattern, a new logging hook, a new dimension refresh rule), and lets a new alarm get added with a config row instead of a cloned flow. The per-alarm framing made sense at the time because it matched the rest of the architecture cleanly, but the DRY violation is real.

Today, on the modern stack

In 2026 the right answer is a different architecture entirely, and it's the one the central D&AI team is now building on the proper stack.

The modern version is streaming-first. Events flow from the operational source systems into Bronze tables continuously, alarm logic runs as streaming aggregations in Silver, alarm facts (current state plus history) materialise in Gold tables. Power BI connects via DirectQuery or native incremental refresh, with no custom connector needed because the platform has caught up: refresh-status reads and partial-refresh actions are now first-class API verbs and built-in connector actions. Push notifications fire at the alarm-event level on the streaming side, via Teams or email connectors, not via Power Automate polling the dataset state after the fact. Observability runs through Grafana on the pipeline side and through Power BI's native refresh-state indicators on the consumer side. The maintenance owner is the central D&AI team, which is the architecturally correct home for real-time operational monitoring at this scale.

Real-time operational monitoring belongs on the streaming side of the medallion, not on the consumption side. The Power Automate version was the right answer for 2022 inside this team's access boundary; it isn't the right answer for 2026 from the platform side. Both can be true.

What I'd take into the next role

The interesting thing about working at the consumption end of a governed data platform is that you learn very quickly what the platform is actually for. Not what the marketing pages say it's for, not what the architectural diagrams imply it's for, but what the specific tools you can touch will and won't let you do. Power Automate isn't built for data pipelines. Used as one, with care, it has been running a daily-fresh operations-exception dashboard for four years and counting. When there's a real operational need and a constrained toolkit, the engineering is mostly about reading the toolkit's grain and working with it instead of against it.