Advanced Power BI Incremental Refresh - Taking Control With the XMLA Endpoint

There's a point in the life of a Power BI dataset where the friendly, click-through incremental refresh setup runs out of road. It usually arrives quietly. The model has grown to a few hundred million rows, the historical load has to reach back seven years, and the first full refresh after publishing either times out or takes so long that it eats the entire refresh window and then some. The standard configuration, the one you set up with a couple of parameters and a slider in Power BI Desktop, is fine for a lot of workloads. But for the big ones, the ones where the data actually justifies the effort, you eventually need to get under the bonnet.

That's what the XMLA endpoint gives you. It lets you treat a published Power BI dataset the way a database professional would treat a tabular model, connecting with tools like SQL Server Management Studio or Tabular Editor and managing the partitions directly, one at a time, on your terms rather than the service's. For any Australian business running Power BI on a Premium or Fabric capacity with serious data volume, this is the difference between a model that's a constant source of anxiety and one that just works. Microsoft's reference on advanced incremental refresh with the XMLA endpoint covers the API. This post is about when you actually need it and what tends to bite.

Why the standard setup hits a wall

The normal incremental refresh flow is deliberately hands-off. You define your RangeStart and RangeEnd parameters, set your archive and refresh windows, publish, and the service builds all the partitions for you on the first refresh. For a model covering two or three years of data, that first refresh is a bit slow but survivable, and after that you never think about partitions again.

The problem is that first refresh. When you publish a model that needs to load seven years of history in one go, the service tries to build and populate every partition in a single operation. On a large enough source, that operation runs past the refresh timeout and fails. And because it's all or nothing, a failure at the six-year mark means you've got nothing to show for it, and you go again. I've watched teams burn a week on this loop, kicking off a full refresh at close of business, coming in the next morning to a timeout, tweaking something, and repeating. It's demoralising and it's avoidable.

The other wall is control. The standard setup gives you partitions defined entirely by your window settings. If you want to refresh just one specific month because you know that's the only period that changed, or you want to reprocess a single bad partition without touching the rest, the service doesn't give you a handle on that. You're stuck refreshing whole windows. For a big model, that's a lot of unnecessary compute.

What the XMLA endpoint changes

Connecting through XMLA flips the relationship. Instead of the service managing partitions for you, you manage them. You can see every partition, when each last refreshed, how many rows it holds, and you can refresh, add, merge, or clear any of them individually.

The scenario where this earns its keep immediately is the initial historical load. Rather than asking the service to swallow seven years at once, you connect via XMLA and refresh the partitions in batches. Load 2019 and 2020, let it finish, then 2021 and 2022, and so on. Each batch is a self-contained operation that fits comfortably inside the timeout, and if one fails you've still banked everything before it. What was a week of failed overnight refreshes becomes a controlled afternoon of loading history in chunks. Tabular Editor scripts this nicely, and once you've done it a couple of times it's routine. This is bread-and-butter work for our Power BI consultants, and it's usually the first thing we reach for when a client's big model won't do its initial load.

Beyond the initial load, XMLA gives you surgical refresh. Know that only last month's numbers were restated? Refresh that one partition. Suspect a single partition loaded badly? Clear and reprocess just that one and compare. You stop paying the compute cost of refreshing data that didn't change, which on a large capacity is real money and real time.

The things that catch people out

This power comes with sharp edges, and I'd be doing you a disservice to pretend otherwise.

The big one: once you modify a dataset's partitions through the XMLA endpoint, you generally can't republish over it from Power BI Desktop without wiping out what you did. Deploying a new version of the model from Desktop can reset the partition structure the service built, and any manual partition work goes with it. This changes how you have to think about your deployment process. For a model you're managing at this level, Desktop stops being the source of truth for the whole thing, and you need a more deliberate release process, often through Tabular Editor or a proper deployment pipeline, so a routine report change doesn't blow away your carefully loaded history. Teams that don't understand this find out the hard way when someone pushes a small visual tweak from Desktop and triggers a full seven-year reload. Not a good afternoon.

Second, XMLA read-write has to actually be turned on for the capacity. It's a tenant and capacity setting, and it's not always enabled by default. Before you plan any of this, confirm the capacity admin has switched on XMLA read-write, or you'll be connecting in read-only mode and wondering why you can't change anything.

Third, this is genuinely a database administrator's mode of working, and the safety rails are off. The service's normal guardrails assume it's managing partitions. When you take over, you can create partition definitions that don't match your model's expectations, leave gaps, or overlap ranges, and nothing stops you. The tooling assumes you know what you're doing. That's fine if you do, and a liability if a Power BI report author who's never managed a tabular model is suddenly poking at partitions in production because a blog post made it sound easy. Match the person to the tool.

Where this fits in a broader data platform

I want to be clear that XMLA-level partition management is not where most Power BI work should live. The overwhelming majority of datasets are perfectly well served by the standard incremental refresh setup, and reaching for the XMLA endpoint on a small model is over-engineering. If your model is a few million rows and refreshes in a couple of minutes, leave it alone.

But at the top end, on the models that are genuinely large and genuinely important, this is a normal and necessary part of running a healthy Power BI estate. It also sits naturally alongside the wider Fabric and data platform tooling. The same XMLA endpoint, the same Tabular Editor workflows, and the same partition thinking carry across the broader Microsoft Fabric world, and once a team is comfortable operating at this level, a lot of previously mysterious dataset behaviour becomes visible and fixable. You go from hoping the refresh works to knowing exactly what it did.

There's also a maturity signal here worth naming. When an organisation's Power BI usage reaches the point where XMLA partition management is on the table, it usually means the analytics function has grown past the "someone in finance builds reports" stage into something that needs actual data engineering discipline: version control, deployment pipelines, monitoring, the lot. That transition is one of the more valuable things we help clients through, because doing it well is what lets analytics scale without becoming a fragile mess of undocumented models nobody dares touch. It's the foundation any serious business intelligence work has to sit on, and it matters even more once AI enters the picture, because AI over untrustworthy data just produces confident nonsense faster.

A sensible way in

If your big model won't complete its initial load, that's the natural first use of the XMLA endpoint, and it's low risk because you're loading data, not restructuring anything live. Confirm XMLA read-write is enabled, connect with Tabular Editor, and refresh your historical partitions in batches small enough to beat the timeout. You'll have your model loaded the same day.

From there, adopt the discipline before you adopt the power. Sort out your deployment process so Desktop republishing can't wipe your partition work, decide who's allowed to operate at this level, and treat the XMLA endpoint as a controlled tool rather than something anyone with dataset access can start experimenting with in production.

Get those foundations right and advanced incremental refresh stops being scary and starts being one of the more satisfying parts of running Power BI at scale. If you've got a large model that's fighting you on refresh, or you're building towards data volumes where you know the standard setup won't hold, get in touch and we'll help you get it under control properly.