Connecting Power BI to Azure Synapse Analytics with DirectQuery - When It Works and When It Bites

There is a specific moment in a lot of Australian data projects where import mode stops being an option. The dataset is too big to pull into Power BI, or it changes too often to refresh on a schedule, or governance says the numbers must come live from the warehouse and nowhere else. That is when people reach for DirectQuery against Azure Synapse Analytics, and it is also when a lot of dashboards quietly turn slow and cranky.

DirectQuery is a genuinely good tool. It also has sharp edges that catch teams who treat it like import mode with a different checkbox. I have been called into more than one project where the Power BI report "worked" but every slicer click took eight seconds and the whole thing felt broken, and the fix was never one setting. It was understanding what DirectQuery actually does and designing for it. So this post is about that: when Synapse plus DirectQuery is the right call, when it isn't, and how to keep it fast.

Microsoft's documentation on connecting Power BI to Azure Synapse with DirectQuery covers the connection steps. This is the part about making it actually perform in production.

What DirectQuery really does, and why it matters

The mental model is the whole battle here, so let me be blunt about it.

In import mode, Power BI copies the data into its own in-memory engine. Every interaction after that hits local memory, which is very fast, and the data is as fresh as your last refresh. In DirectQuery mode, Power BI stores no data. It stores the model and the relationships, and every single visual, slicer, filter and drill generates a live SQL query that runs against Synapse in real time.

That difference changes everything. Your report's performance is now your warehouse's performance. A slow query in Synapse is a slow visual in Power BI, full stop. A dashboard with fifteen visuals on one page can fire off fifteen or more concurrent queries the moment it loads, and again every time someone touches a slicer. If Synapse is tuned and the queries are lean, this is fine. If it is not, the user experience falls apart, and no amount of Power BI-side tweaking rescues it.

So the first thing I tell any team considering this: you are not tuning a Power BI report, you are tuning a query workload against a data warehouse. If you do not have someone who understands Synapse performance, get one, because that is where the wins and the losses both come from. This is exactly the kind of thing our Power BI consultants get pulled into, usually after the dashboard has already gone slow.

When DirectQuery against Synapse is the right choice

DirectQuery earns its place in a few clear situations, and it is worth being honest about which they are.

The strongest case is data volume. Synapse is built for large-scale analytics, and if your fact tables are hundreds of millions or billions of rows, importing them into Power BI is impractical or impossible. DirectQuery lets you report on data at a scale import mode simply cannot hold. This is the classic reason and it is a good one.

The second case is data freshness. If the business genuinely needs to see what is true right now, not what was true at the last scheduled refresh, DirectQuery gives you that. Operational reporting, near-real-time monitoring, anything where a few hours of staleness actually matters. Import mode caps out at how often you can refresh. DirectQuery is always current by definition.

The third case is governance and single source of truth. Some organisations, especially in regulated industries, have a hard rule that reporting numbers must come live from the governed warehouse and never sit in a copied cache. DirectQuery satisfies that. The data lives in Synapse, Power BI just visualises it, and there is no second copy to reconcile or secure.

If none of those three apply to you, and honestly for a lot of reports none of them do, import mode is faster and simpler and you should probably use it. DirectQuery is not a badge of sophistication. It is a trade-off you take on when you have a real reason.

Where teams get burned

Now the failure modes, because these are predictable and avoidable.

The most common one is throwing too many visuals on a page. Every visual is a query. A busy executive dashboard with a dozen cards, three charts and five slicers is a query storm every time it loads. In import mode you would never notice. In DirectQuery it is the difference between a snappy report and one that spins. Fewer, more deliberate visuals per page is not just cleaner design here, it is a performance requirement.

The second is expensive DAX. Some DAX patterns translate into ugly SQL when Synapse has to run them live, and calculations that were instant in import mode can become the slow query that holds up the whole page. You have to think about how your measures actually get translated and executed against the warehouse, which is a skill import-mode developers often have not needed.

The third is a warehouse that is not modelled for this. If your Synapse tables are not properly distributed, if statistics are stale, if there are no useful indexes or materialised views for the query patterns your report generates, DirectQuery will expose every one of those weaknesses under load. The report becomes the stress test your warehouse never had. Often the real fix for a slow DirectQuery report is not in Power BI at all, it is tuning Synapse: better distribution keys, result-set caching, materialised views for the common aggregations. Our work on the Microsoft Fabric and data platform side is frequently where these DirectQuery performance problems actually get solved.

Composite models are the pragmatic middle ground

Here is the thing a lot of teams miss, and it is genuinely useful: you do not have to choose import or DirectQuery for the whole model. Composite models let you mix them.

The pattern that works beautifully is DirectQuery for the enormous, always-changing fact tables, and import mode for the small, stable dimension tables like dates, products, regions and stores. Your big transactional data stays live in Synapse, but the dimensions people slice and filter by are cached locally in Power BI, so those slicer interactions are instant. You get the freshness and scale of DirectQuery where you need it and the speed of import where you do not.

Aggregations take this further. You can pre-build summarised aggregate tables, import those, and let Power BI answer high-level questions from the fast in-memory aggregates while only dropping to a live Synapse query when a user drills into the detail. Done well, most interactions never touch the warehouse, and the ones that do are the ones that genuinely need the raw grain. This is one of the better performance patterns available and it is underused, mostly because it takes a bit more design thought upfront.

My honest view is that pure DirectQuery is rarely the best answer for a real reporting solution. A well-designed composite model usually is. It takes more skill to build, but it gives you the best of both modes, and it is where the genuinely fast large-scale Power BI solutions end up.

Practical advice before you commit

A few things worth doing early.

Design the report for DirectQuery from the start. Fewer visuals per page, deliberate use of slicers, and a hard eye on which interactions are actually needed. Retrofitting performance onto a busy report built as if it were import mode is painful. Building lean from the beginning is easy.

Push work down into Synapse. Views, materialised views and pre-aggregations that shape the data the way the report needs it will nearly always beat trying to do that shaping in DAX at query time. The warehouse is better at heavy lifting than Power BI's live query layer. Let it do the work.

Test with real data volumes and real concurrency, not a tiny sample. A DirectQuery report that flies with a thousand test rows and two users can crawl with a hundred million rows and fifty people hitting it at nine on a Monday. The problems only show up at scale, so test at scale before you promise anyone a launch date.

The bottom line

DirectQuery against Azure Synapse is the right tool when you have genuine scale, genuine freshness needs, or a genuine governance rule that rules out caching. It is the wrong tool when you have reached for it out of habit or a vague sense that it is more serious. When you do use it, remember you are tuning a warehouse workload, not a Power BI report, and lean hard on composite models and pre-aggregation to keep interactions fast. Get that right and you can put billion-row data in front of business users and have it feel quick. Get it wrong and you have a beautiful dashboard nobody wants to wait for.

If you are building large-scale Power BI on top of Synapse or Fabric and want it to be fast as well as correct, that is squarely our work. Have a look at what our Power BI consultants do, and if you have a report that is either slow or about to be, get in touch.