Your Report Isn't Slow. It's Misdiagnosed.

Three weeks of treatment. One overdue consult.

The message arrived at 7:42 AM on a Monday: "The Ops Master Report won't load. Quarterly review at 10 AM. Please advise."

I knew the report. Everyone knew the report. The Operations Master Report was 212 columns wide and eight years deep. Every transaction, every status, every timestamp, every free-text note field since 2018, landing on a single unfiltered page. It was the kind of report that didn't have users so much as survivors. Opening it was a ritual: click the link, go get coffee, come back to a gray rectangle politely informing you that the visual has exceeded the available resources.

The PBIX weighed in at 1.3 gigabytes. The nightly refresh took four hours and failed two nights out of seven, and they were always the nights before someone important needed it. And now it had stopped loading entirely, ninety minutes before a quarterly review.

I'd like to tell you that the first thing I did was ask who actually used this report and why. It wasn't. The first thing I did was open DAX Studio — because I am a technician, and when a technician meets a struggling patient, he orders tests.

Treatment One: A Bigger Warehouse

The refresh was hammering our Snowflake warehouse, so I did the obvious thing first, which is to throw compute at it. We're gonna need a bigger warehouse. Small became Large, the refresh got meaningfully faster, and so did the bill (this was a detail I planned to explain at some future date, ideally after the report was fixed).

Better. Not fixed.

Chart Notes: Warehouse sizing helps when the transformation is compute-bound. It does nothing for what happens after the data lands in your analysis model. Scaling up to feed a bloated dataset isn't optimization.

Treatment Two: Query Folding

Then I found what was actually ailing the import. Early in the Power Query chain — step four of sixty-one — someone had added an index column. Tiny. Invisible. And from that step forward, query folding was broken, which meant our data warehouse wasn't doing any of the work. Eight years of raw transactions were being dragged across the wire and transformed row by row in the mashup engine, like importing the ocean to make a cup of tea.

I rebuilt the chain, pushed the heavy transformations upstream into dbt where they belong. Versioned, tested, shared, and now utilizing the upscaled Snowflake compute that was built for exactly this. The refresh dropped from two-and-a-half hours to seventy minutes.

Better. Not fixed.

Chart Notes: Right-click any applied step in Power Query and check View Native Query. If it's grayed out, everything after that step is running locally instead of in your warehouse. Certain steps break folding quietly — index columns, some merges, changing types too early. And the deeper fix: heavy logic belongs in your transformation layer (like in dbt), not in sixty-one steps of M that only one laptop owner understands.

Power Query steps are a great place to figure out what your data model wants to be when it grows up, but don't forget to graduate it up the pipeline once the dust settles.

Treatment Three: Incremental Refresh

Next up, incremental refresh. After adding two parameters (RangeStart / RangeEnd), partitioning by month, and setting the refresh to only a few trailing days instead of reloading eight years nightly, the refresh dropped from seventy minutes to six. I sent a modest, dignified message to the ops channel that in no way resembled a victory lap.

Tuesday, 7:42 AM: "Still won't load."

Of course it wouldn't. I had spent two weeks treating my symptoms — the refresh — and not one minute on theirs. Users don't experience your refresh. They experience how the model is expressed within the visual layer, and the model was still 212 columns of everything: a timestamp precise to the second, three different GUID keys, a free-text notes field — high-cardinality columns quietly strangling compression, and every visual scanning the whole mess just to draw a bar chart.

Better. Not fixed.

Chart Notes: Incremental refresh needs two things to earn its keep: parameters named exactly RangeStart and RangeEnd, and a source query that still folds — if the date filter can't be pushed down to the warehouse, you're scanning eight years just to load three days. Expect the first refresh after publishing to take the full hit; that's the partitions being built, once.

And don't forget to read the fine print: incremental refresh shortens your refresh window, not your users' load time. It changes how the data arrives, but not how much of it is sitting in memory when someone opens the report.

Treatment Four: Model Surgery, Interrupted

So I started cutting. Truncated the datetimes to dates, dropped the GUIDs, disabled auto date/time, and then hit the wall every analyst hits when they try to put an 'everything' report on a diet: which of the 212 columns do you remove?

Every single one had a defender — in theory. "Someone might need it."

That phrase is the 'everything' report's immune system. Any column you propose removing is hypothetically load-bearing, and nobody will consent to the surgery, because nobody knows what the report is actually for. Including me, I realized, somewhere around column 140.

Three weeks in, I finally ordered the one test I'd been avoiding. I talked to the patient.

The Patient Consult

The usage metrics told the first half of the story: eleven viewers in ninety days, three of them regulars. Two hundred and twelve columns, refreshed seven nights a week at genuine expense, for three human beings. I booked fifteen minutes with each and asked the only two questions that ended up mattering: What decision does this support? And: Walk me through what you actually do after it loads.

The regional ops manager filters to her region, waits, and exports to Excel. She uses nine columns to plan staffing. "Honestly, it's easier once it's in Excel," she said. Just then, somewhere in the world, an analyst died – it happens every time an accurate, beautiful, hard-won report ends up chopped and exported into an Excel doc for further processing. Our flagship enterprise BI tool-stack was functioning as a slow, ceremonial on-ramp to a spreadsheet.

Chart Notes: This is where that second question really pays off. Understanding the full life-cycle of the report may reveal some of those pesky extra steps that turn an efficient automated report back into a set of slow, manually executed steps.

The second regular opens it monthly, reads a single card — on-time percentage — and screenshots it into a slide deck. One card. The gray rectangle he waited for each month contained one number.

The third data consumer runs a monthly reconciliation against finance and needs a detailed extract: fourteen specific columns, fixed, every month, forever.

Twenty-four columns and one KPI card. That's what 212 columns, 1.3 gigabytes, and four hours of nightly compute were actually supporting. The report wasn't sick because it was slow. It was sick because it had been scoped by fear: Someone might need it. Fear knows no column limit.

The Cure

What we shipped a week later wasn't clever. One certified dataset — a proper star schema, thirty-odd columns, every one of them earning its place. Two slim reports on top of it: a regional staffing view that loads in about two seconds, and a monthly ops summary with the sacred card front and center.

And for the reconciliation? A scheduled fourteen-column extract, delivered on the first of the month. The honest answer to "I just export it to Excel anyway" is to build the desired export path on purpose, not to force a 212-column model to cosplay as a CSV.

The numbers, because you'll want them: 1.3 GB to 38 MB. Four hour refresh to four minutes. The Snowflake warehouse went quietly back to Small and no budgetary explanation was required. And the part I should've expected — usage went up. More viewers in the first month than the monster had drawn in a quarter. Reports that load get opened. Who knew?

With that said, none of the optimization work was wasted! Query folding, incremental refresh, cardinality hygiene — these are now fully implemented in this dataset and allow that previously undiagnosed monster to instead serve as a tightly organized building block for future reports.

Keep the chart notes. But notice what I got wrong: the sequence. Those were all treatments. The patient consult was the diagnosis. I ran every test in the book before I talked to the patient, and the patient had been trying to tell me for three weeks that she just wanted nine columns and a quiet life.

The question that fixed the report — what decision does this need to support? — cost nothing, required no premium capacity, and folded perfectly. I've written before that it's the question that turns you from a report vending machine into a thinking partner. It turns out it's also the best performance tuning tool in the entire stack, because the most expensive performance problem in BI isn't a broken fold or an oversized warehouse. It's a report faithfully answering a question nobody asked — at enterprise scale, on schedule, seven nights a week.

The message will come again. It will be a Monday morning at 7:42 AM, and something enormous will refuse to load. But the first thing I reach for now isn't DAX Studio.

It's a calendar invite.

The report was never slow, it was misdiagnosed. The right question becomes the cheapest medicine in the stack, with no side effects, taken before every build rather than after the patient collapses. Because there is no optimization on earth that can cure a report with no purpose...

...but fifteen minutes with the patient just might.