One of the most important properties you can set in a Power BI DirectQuery semantic model is the “Maximum connections per data source” property, which controls the number of connections that can be used to run queries against a data source. The good news is that the maximum value that you can set this property to has just been increased in Premium.
This property is important because the number of open connections to a data source acts as a limit on the number of concurrent queries that can be run by the semantic model against the source: each connection can only have one query running on it at any one time. If you have Power BI reports that have a large number of visuals on a page and/or a large number of users running reports at the same time then it is very easy for a DirectQuery semantic model to need to send lots of queries back to your data source at the same time. If some of the queries that your semantic model runs against your data source are slow – more than one or two seconds even – then the number of queries that need to be run at a given time will increase. The same is true if you have increased the Max Parallelism Per Query property to increase the number of parallel queries that can be generated by a single DAX query.
This property is documented in a number of places, including the DirectQuery guidance documentation and in data source-specific best practice documents such as this one for Snowflake. You can set the property in Power BI Desktop in the Current File/DirectQuery section of the Options dialog:
If you are not using Power BI Premium (ie you are using Power BI Shared capacity, also known as Power BI Pro) then the maximum value that you can set this property to is 10. If you are using Power BI Premium then the maximum value up to today was 30 but now that limit has been increased. The table on this page shows what the new limits per SKU are:
As you can see, for a P1/F64 the maximum limit is now 50 rather than 30 and this limit goes all the way up to 200 for a P4/F512 and higher.
I’ve seen plenty of cases where increasing the value of this property makes Power BI reports run a lot faster. However, this will only be true if your data source is able to handle the number of queries that Power BI is trying to run against it. As I showed in this post, if your data source can’t handle the number of queries you’re trying to run then performance will get worse and not better, so you should try different values to see which one works best.
That’s awesome. Were there any baselines provided as best practices for certain sources like databricks?
No, it depends too much on your reports, semantic model and usage patterns.
Thank you Chris !
Is this also true for composite modele ? I mean, when you create a semantic model B based on another semantic model A, semantic model B is in direct query to semantic model A. In this case, do I need to change this setting on semantic model B ?
Ben
Correct – this also applies to composite models and you should try changing this on semantic model B. See https://blog.crossjoin.co.uk/2022/06/12/how-the-maximum-connections-per-data-source-property-on-power-bi-directquery-datasets-can-affect-report-performance/
Thanks Chris for sharing such valuable insights. Just to confirm my undestanding, it is more about the number of concurrent DirectQuery connection per semantic model not “Maximum Connections Per Data Source”. This means that regardless of the number of data sources a semantic model connects to (in DirectQuery), these limits still apply.
For example, I use a Premium P1 that connects to two Azure SQL DBs in DirectQuery mode, the max concurrent connection is still 50, right?
Cheers
Sorry for the late reply – I had to ask some people to get the answer. In your case, if you have two Azure SQL DB connections in the same model, you can set the Maximum Connections Per Data Source property to 50 for both of them. If only one connection ever gets used then it can use up to 50 connections. However if both are used, the total number of connections across both can never exceed 50 either.