How The “Maximum Connections Per Data Source” Property On Power BI DirectQuery Datasets Can Affect Report Performance

If you’re working with DirectQuery in Power BI then one of the most important properties you can set on your dataset is the “Maximum connections per data source” property. You can find it on the Published Dataset Settings tab in the Options dialog in Power BI Desktop:

The description of what it does in the guidance documentation is pretty comprehensive:

You can set the maximum number of connections DirectQuery opens for each underlying data source. It controls the number of queries concurrently sent to the data source.

The setting is only enabled when there’s at least one DirectQuery source in the model. The value applies to all DirectQuery sources, and to any new DirectQuery sources added to the model.

Increasing the Maximum Connections per Data Source value ensures more queries (up to the maximum number specified) can be sent to the underlying data source, which is useful when numerous visuals are on a single page, or many users access a report at the same time. Once the maximum number of connections is reached, further queries are queued until a connection becomes available. Increasing this limit does result in more load on the underlying data source, so the setting isn’t guaranteed to improve overall performance.

When the model is published to Power BI, the maximum number of concurrent queries sent to the underlying data source also depends on the environment. Different environments (such as Power BI, Power BI Premium, or Power BI Report Server) each can impose different throughput constraints.

I thought it would be interesting to do some experiments to see how this property behaves, what you see in Profiler (or Log Analytics) when connections are queued up, and how you can find an optimal value for your dataset.

The first thing to mention – and this is something I only realised relatively recently – is that this property applies to DirectQuery on Power BI datasets and Analysis Services as well as traditional DirectQuery to external databases. I’m a lot more comfortable with Power BI than any relational database so I decided to do my testing with a DirectQuery dataset connected back to another Power BI dataset; the behaviour of the feature is the same as with DirectQuery to a relational database.

For my tests I created a simple dataset – let’s call it Dataset A – with not much data but a really inefficient DAX measure on it. I then created a composite model dataset – let’s call this Dataset B – with a DirectQuery connection to Dataset A. Finally I created a report with a Live connection to Dataset B with 25 card visuals on, each of which used the inefficient measure with a different filter. The DAX query for each of these cards, when run on its own through DAX Studio, took around 28 seconds, with almost all that time spent in the Formula Engine. The datasets and reports were published to a PPU workspace and all tests were run in the Power BI Service and not in Power BI Desktop (at the time of writing, things work differently in Desktop – which means you should always test performance of DirectQuery reports in the Service and not in Desktop). I ran Profiler traces using the Query Begin and Query End events on both Dataset A and Dataset B during my tests.

First of all, let’s see what happened when the Maximum Number of Connections property on Dataset B was set to 1. This means that Dataset B is only allowed to have one connection open to Dataset A to run its DirectQuery queries. When the report was run, right at the start the Profiler trace on Dataset B showed 25 Query Begin events indicating all 25 queries for the 25 card visuals were being run in parallel; the Profiler trace on Dataset A showed just 1 Query Begin event:

This is what you would expect: since Dataset B can only use one connection to Dataset A it can only run one query at a time and the other 24 queries have to queue up to wait for the connection. When the first query against Dataset A completed, another one started and so on. Since the maximum length of time that a DAX query can run in the Power BI Service is 225 seconds, after 225 seconds any remaining queries timed out. At that point 8 queries had completed, so 8 cards were rendered, and all the remaining cards showed the timeout error I blogged about here:

At the end, the Profiler trace against Dataset A showed 8 completed Query Begin/End pairs:

While the Profiler Trace against Dataset B showed, after the 25 Query Begin events, 8 Query End events for the successful queries and then 17 Query End events with timeout errors for the unsuccessful queries.

One interesting thing to notice is the durations of the queries. As I said, when run on their own each of these queries took around 28 seconds, and the Profiler trace on Dataset A shows each query taking around 28 seconds. If you look at the successful queries on Dataset B you’ll see that their duration goes up in increments of around 28 seconds: the first takes 29984ms, the second takes 58191ms, the third takes 87236ms and so on until you hit the 225 second timeout limit. This shows that the duration of the queries against Dataset B, the composite model, includes the time waiting to acquire a connection. Notice also that the CPU Time of the queries against Dataset B is minimal because it only includes the CPU used by the query for Dataset B; you have to add the CPU Time to the related queries on Dataset A to get the total CPU Time used by these queries.

The important question is, though, what is the effect of increasing the Maximum Connections Per Data Source property? Increasing it will increase the number of queries run in parallel, but is more paralellism always better? I reran my tests with the property set to 5, 10 (which is the default value and the maximum that can be used for datasets not in Premium capacity) and 30 (which is the maximum value that can be used for datasets in any form of Premium capacity). Here are the results:

Maximum Connections Per Data Source	Number of Visuals That Render Successfully In 225 Seconds
1	8
5	16
10	10
30	8

As you can see increasing the parallelism a little bit helps more than increasing the parallelism a lot, and in this case reducing the value from the default was better than increasing it: overloading your source with a lot of expensive parallel queries is often a bad thing. This test isn’t representative of most real-world reports – you shouldn’t have one visual, let alone 30, with queries that run for as long as 30 seconds and the best way to optimise a report like this would be to display the same data in a smaller number of visuals – but I think it’s a useful illustration of how this property works and how it can affect report performance.