Cutting Infrastructure Cost by 44%: A BI Cleanup Story

The meeting I walked into was, on paper, about buying more. A regional grocery retailer had a Power BI capacity — the dedicated slice of Microsoft's cloud you rent so your reports don't queue behind everyone else's — and it was straining. Refreshes were overrunning, the morning dashboards were sometimes still yesterday's, and the obvious, budgeted, already-half-approved answer was to size up: buy the next capacity tier and make the problem go away with money. I asked for six weeks before they signed anything. At the end of them, they didn't size up. They sized down, and the infrastructure bill came in 44% lower than where it had started — with not one report retired and not one user worse off.

This is the story of those six weeks, because the lesson generalises far past this one retailer: most "we need a bigger capacity" problems are actually "we've never cleaned up the capacity we have" problems, and the second one is enormously cheaper to solve.

Why a capacity fills up

First, the plain-language version of what you're actually paying for. A Power BI Premium capacity is a fixed pool of memory and compute. Every dataset you publish — the modelled tables behind a report, which Microsoft now calls a semantic model — sits in that pool, and every scheduled refresh spends its compute. When the pool is too full or the refreshes collide, things slow down or fail. The instinct is to assume the pool is genuinely too small for the work. Sometimes it is. Far more often, the pool is full of waste — and nobody has ever been given the job of noticing.

That was the situation here. Nobody was negligent. The capacity had simply accreted three years of BI the way an attic accretes boxes: one useful thing at a time, none ever thrown out.

What six weeks of looking actually found

I didn't start with a tool or a rearchitecture. I started with an inventory — a boring, complete list of every dataset and report on the capacity, how big each model was, how often it refreshed, and, crucially, when it was last actually opened by a human. That last column is the one nobody ever has, and it's the one that changes everything. Here's the shape of what we found:

None of that is exotic. It's the ordinary sediment of self-service BI working exactly as intended — everyone empowered to build, nobody responsible for tidying. I've argued before that handing everyone Power BI was never the finish line; this capacity was that argument's invoice, arriving three years later.

How to actually get the "last opened" column

The one column nobody has is the one that does all the work, so it's worth saying exactly where it comes from, because "just look at what's unused" is easy to write and non-obvious to do. The usage signal you need isn't in the capacity's size metrics — it's in the activity data. Two sources give it to you:

• The usage metrics on each report. Power BI keeps per-report usage — views, distinct viewers, over the last stretch of days. It's fine for spot-checking a single suspicious report, but tedious across hundreds.
• The tenant activity log, pulled in bulk. This is the real tool for an audit at scale. The admin-side activity log records every view, refresh, and edit across the tenant, and you can pull it programmatically — a scheduled PowerShell job against the admin API, dropping the events somewhere you can aggregate them. Roll a few weeks of that up per report and you have, at last, the "last actually opened by a human" column for the entire estate in one table.

Join that activity data against the list of datasets and their refresh schedules, and the waste stops being a matter of opinion. A report with a daily refresh and zero views in six weeks isn't a judgement call — it's a row in a table, and rows in a table are much easier to act on than hunches. The whole audit, in other words, is itself a small data project: inventory as the fact table, activity as the measure, and the cleanup decisions falling out of the join. Build it once and the four moves below more or less choose themselves.

The four moves that did the work

The fixes were unglamorous and, in order of payoff:

1. Retire the abandoned. The single biggest lever, and the one that needs the most nerve, because deleting someone's report feels dangerous. So we didn't delete — we un-scheduled the refresh and watched for a month. A report nobody opens and nobody notices going stale is a report nobody needs. Almost a third of the compute was being spent keeping the dead warm.
2. De-duplicate the models. Where four report authors had each published their own copy of "the sales model," we built one certified shared model and pointed the reports at it. One well-built dataset, refreshed once, instead of four mediocre ones refreshing four times. This is the move that also quietly improves trust, because now the four reports can't disagree — they're reading the same numbers.
3. Right-size the refreshes. A dataset behind a once-a-day report does not need to refresh hourly. Matching refresh frequency to how the data is actually used — not to how nervous the original author felt — reclaimed a startling amount of headroom on its own.
4. Switch full refreshes to incremental. Incremental refresh means loading only the slice of data that changed — last night, not the last three years — while leaving the history in place. For the big fact tables this was the difference between a refresh that ate an hour and one that took four minutes.

The number, and the honesty around it

Stacked up, those four moves didn't just fix the overrunning refreshes — they emptied so much of the pool that the retailer could drop to a smaller capacity tier and still have room to spare. That's where the 44% came from: not a discount, not a clever licence trick, but simply needing meaningfully less machine because we'd stopped paying to compute waste.

Two honest caveats, because a cleanup story with no counter-view is a sales pitch. First: this works when your bill is dominated by accreted waste, which is common but not universal. An organisation whose capacity is full of genuinely used, already-lean workloads won't find a 44% hiding in it — for them, sizing up really is the answer, and the six-week audit will tell you that just as clearly. Do the inventory precisely so you know which situation you're in. Second: a cleanup is only durable if someone owns the tidying from now on. We paired the one-off cleanup with a standing habit — a quarterly review of the last-opened column, and a rule that new shared models get certified rather than copied. Without that, the attic just refills.

The cheapest capacity upgrade is the one you don't buy because you finally looked at what was already running. Nobody gets promoted for deleting an unused report — which is exactly why the unused reports pile up until they cost you a tier.

The move that isn't technical

The technical fixes above are the easy part; any competent BI engineer can run an incremental refresh. The hard part was permission — getting an organisation comfortable retiring things, consolidating duplicates people felt ownership of, and admitting that some of what they'd built was no longer earning its keep. That's a conversation, not a query. It needed the retailer's own people in the room agreeing what could go, so that "we deleted your report" was a decision they'd made, not one done to them.

Which is the real lesson, and the one I keep relearning: the 44% was sitting there in plain sight for anyone willing to do the unglamorous work of looking, and then to have the slightly awkward conversations that looking turns up. The bigger capacity was the comfortable answer because it required no one to change and nothing to be thrown away. The cheaper answer required both — and it was better in every respect except how it felt to propose. Before you buy more machine, spend a fortnight finding out what your current machine is actually doing. More often than not, a good chunk of it is keeping the dead warm.