If you’re performance tuning a DirectQuery mode semantic model in Power BI, one of the first things you’ll want to do is look at the SQL that Power BI is generating. That’s easy if you have permissions to monitor your source database but if you don’t, it can be quite difficult to do so from Power BI. I explained the options for getting the SQL generated in DirectQuery mode and why it’s so complicated in a presentation here, but I’ve recently found a new way of doing this in Power BI Desktop (but not the Service) that works for some M-based connectors, for example Snowflake.
The trick is to use Profiler and the M Data Provider Events/Execute Source Query event. To demonstrate this I created a DirectQuery semantic model in Power BI Desktop using Snowflake as a source. I then connected SQL Server Profiler to Power BI Desktop (the easiest way to do this is to install it as an External Tool as described here, although you can connect by finding the diagnostics port as described here) and selected the following events:
I then added a card visual to my report that contained a simple SUM measure.
Here are the events generated in Profiler:
As you would expect there’s a single DirectQuery Begin/End event pair generated because a single SQL query is sent to Snowflake by the card visual. However, the TextData column shows the following SQL – which is definitely not Snowflake SQL:
SELECT SUM([t0].[fare_amount])
AS [a0]
FROM [NYCTAXIDATA] AS [t0]
This is because for some data sources, Power BI emits TSQL and the connector used then converts this to the actual dialect of SQL used by the source database.
This is where the Execute Source Query event comes in. The TextData column for this event shows the actual SQL sent to Snowflake for this query, which is:
select sum("fare_amount") as "C1"
from "NYCTAXIDB"."PUBLIC"."NYCTAXIDATA"
Interestingly this event is generated after the Query End event for the whole query, but you can associate this event with its parent DAX query by matching the value in the RequestId column with the RequestId for the other events generated by the query.
In the past I have blogged about how important the number of connections from a Power BI DirectQuery semantic model to a data source is for performance. It’s also true that in some cases opening a connection, or some of the operations associated with opening a connection, can be very slow. As a result it can be useful to see when your semantic model opens connections to a data source, and you can do this with Log Analytics.
To show this I published a DirectQuery semantic model to a workspace with Log Analytics enabled and opened a report with one table visual on it. Because the model and report had only just been published I could be sure there were no existing connections back to the data source used by the DirectQuery model. I then ran the following KQL query:
PowerBIDatasetsWorkspace
| where TimeGenerated>ago(1hr)
| where OperationName in ('Notification', 'QueryBegin', 'QueryEnd', 'DirectQueryBegin', 'DirectQueryEnd')
| project TimeGenerated, OperationName, OperationDetailName, EventText, DurationMs
| order by TimeGenerated asc
Here’s the output:
Some things to note:
I have highlighted in red the events of type Notification and subtype OpenedConnection. These events are fired when the semantic model opens connections back to the data source. There are three of these events so three connections are being opened.
There is one DAX query generated for the single visual on the report page. As you would expect there is a single pair of QueryBegin/QueryEnd events.
After the QueryBegin event there are two DirectQueryBegin/DirectQueryEnd events, which mean that for this DAX query Power BI ran two SQL queries against the source.
Immediately before these two DirectQueryBegin/DirectQueryEnd pairs, and after the QueryBegin event, there are two Notification events which indicate that two connections are being opened to run these queries.
Before the DAX query runs, another connection is opened and there is a DirectQueryBegin/DirectQueryEnd pair that is not associated with a DAX query that I have highlighted in blue. The EventText column contains M code rather than SQL code, and a look at this M code shows that it uses the DirectQueryCapabilities.From function which is marked for “internal use only” in the docs. The DirectQueryEnd event from this pair has a duration of 3 seconds which shows that in this case it’s quite expensive. This query is the Power Query engine understanding the DirectQuery capabilities of the source and one of the side effects of this is that it needs to read metadata from the source, which can be quite slow sometimes. Doing things like disabling relationship columns in the connector and, for some sources like Snowflake, connecting with an identity or with a security role that can only see the tables or views used by the model (as mentioned here), can make this a lot faster.
For some reason the Notification events associated with opening connections do not appear in Profiler traces run on models in the Service, although they do appear if you run a trace on a model in Power BI Desktop. This is why I’ve used Log Analytics here.
Power BI pools connections once they are opened and keeps them alive for a certain amount of time and it also caches data source metadata after it has retrieved it, so as a result you may not see the events I have highlighted here when you run a report.
[Thanks to Curt Hagenlocher and Akshai Mirchandani for some of the information in this post]
Recently I was working with a customer using DirectQuery mode and where all traffic to the data source had to go through an on-premises data gateway for security reasons. They noticed that report performance got worse when traffic went through the gateway and this was particularly true when Power BI generated SQL queries that returned hundreds of thousands of rows. Looking in the gateway logs we found that spooling (see here and here for more details on spooling, and here for information on monitoring spooling in the logs) was taking place, so I advised the customer to set the StreamBeforeRequestCompletes property on the gateway to true. This eliminated the delays from spooling on the gateway (I’m told it helps performance inside the model too) and resulted in some substantial improvements in report performance. As a result, if you’re using DirectQuery and a gateway, you should experiment with the StreamBeforeRequestCompletes property to see if it can help you too.
That said, if you’re using DirectQuery mode you should try to avoid situations where Power BI generates SQL queries that return a large number of rows such as table visuals with scrollbars. Regardless of whether you’re using a gateway or not, moving large amounts of data from your data source to your semantic model can be slow – even if your data source tells you the SQL query itself is fast. You can use Performance Analyzer in Power BI Desktop (see here) to find out how many rows the SQL queries Power BI generates return and how long it takes to read that data.
A few months ago I blogged about the new limits available for the Maximum Connections Per Data Source property in Premium and why the number of connections that a DirectQuery semantic model can open to a data source is so important for report performance. At that time, however, there was no way for you to know whether the performance of your reports was being affected by a lack of available connections. The good news is that, with the announcement this week of the new Execution Metrics event in Log Analytics and Profiler, you can now see when this is happening.
To illustrate how to use Execution Metrics to see whether availability of connections is hurting performance, I created a Power BI semantic model with three tables in DirectQuery mode connected to SQL Server. Each table consisted of a single row and column and was bound to a SQL query that took 10 seconds to run (using the TSQL custom function I blogged about here).
I then built a report connected to this model containing three cards, each of which displayed the single value returned by each of these three tables. As you would expect, the DAX queries associated with each of these card visuals took 10 seconds to run when run in isolation.
I left the Max Connections Per Data Source property set to the default value of 10 and published the model and the report to the Power BI Service:
I then opened the report and let it render to make sure there were some connections open in Power BI’s connection pool, then re-ran the report while running a Profiler trace that recorded the Query Begin/End, DirectQuery Begin/End and the new Execution Metrics event.
As you might expect, the three DAX queries for the three cards were run in parallel and each query took around 10 seconds to run. Each DAX query generated a single SQL query and each SQL query also took around 10 seconds to run. Here’s what the Execution Metrics event returned for the first DAX query (the Execution Metrics for the other two DAX queries were more or less identical):
The datasourceConnectionThrottleTimeMs metric is 0, which indicates that no time was spent waiting for a connection before the model could run the sole SQL query linked to this DAX query.
I then created a copy of the semantic model, changed the Maximum Connections Per Data Source property to 1, and published this new model and an identical report and ran a Profiler trace while running the report. Changing the Maximum Connections Per Data Source property to 1 meant that this new model could only have one connection open to SQL Server at any one time and therefore could only run one SQL query at a time.
Here’s what the Profiler trace looked like:
The first thing to note is that even though the three DAX queries were still run in parallel they now took 10, 21 and 33 seconds to run, as seen from the Duration column for each of the three Query End events. The SQL queries generated by each DAX query, however, still only took 10 seconds each, as shown from the Duration column for the three DirectQuery End events.
Here’s what the Execution Metrics event for the last DAX query to finish (the one that took 33 seconds) returned:
This clearly shows that out of the total query duration of 33 seconds, 22 seconds of that was spent waiting for a connection to become available, as indicated by the datasourceConnectionThrottleTimeMs value. This was because this DAX query had to wait for the SQL queries generated by the two other DAX queries to complete before it could get a connection back to the source. As for the other two DAX queries generated for this report, the query that took 10 seconds still had a datasourceConnectionThrottleTimeMs of 0 because it was the first query to run and there was a connection available for it to use, while the second query to run that took 22 seconds had a datasourceConnectionThrottleTimeMs of 11 seconds because it only had to wait for the first DAX query’s SQL query to run before it could use the connection.
What should you do if you have performance problems and can see that datasourceConnectionThrottleTimeMs is consistently high? Before anything else you need to tune your model and your data source (see here and here for some tips); the more SQL queries your model generates when a report is run, and the slower those queries are, the more likely you are to run out of connections. If that doesn’t work and you are using Pro/Shared or a capacity that is lower than an F64, then you will need buy an F64 capacity or greater (see the table here for the maximum number of DirectQuery connections supported by each SKU) all to allow for more than 10 connections back to your source. Once you have done that you will also need to set the Max Connections Per Data Source property in your model to something larger than 10. However, you also need to be aware that increasing the maximum number of connections can make performance worse if your data source cannot handle the number of SQL queries that Power BI is trying to run on it.
I recently took part in a webinar with Denny Lee, Liping Huang and Marius Panga from Databricks on the subject of best practices for using Power BI on Databricks. You can view the recording on LinkedIn here:
My section at the beginning covering Power BI best practices for Import and DirectQuery doesn’t contain any new information – if you’ve been following the DirectQuery posts on this blog or read the DirectQuery guidance docs here and here then there won’t be any surprises. What I thought was really useful, though, was hearing the folks from Databricks talk about best practices on the Databricks side and this took up the majority of the webinar. Definitely worth checking out.
Many Power BI connectors for relational databases, such as the SQL Server connector, have an advanced option to control whether relationship columns are returned or not. By default this option is on. Returning these relationship columns adds a small overhead to the time taken to open a connection to a data source and so, for Power BI DirectQuery semantic models, turning this option off can improve report performance slightly.
What are relationship columns? If you connect to the DimDate table in the Adventure Works DW 2017 sample database using Power Query, you’ll see then on the right-hand side of the table. The following M code:
let Source = Sql.Database("localhost", "AdventureWorksDW2017"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate
…shows the relationship columns:
Whereas if you explicitly turn off the relationships by deselecting the “Including relationship columns” checkbox:
…you get the following M code with the CreateNavigationProperties property set to false:
let Source = Sql.Database("localhost", "AdventureWorksDW2017", [CreateNavigationProperties=false]), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate
…and you don’t see those extra columns.
How much overhead does fetching relationship columns add? It depends on the type of source you’re using, how many relationships are defined and how many tables there are in your model (because the calls to get this information are not made in parallel). It’s also, as far as I know, impossible to measure the overhead from any public telemetry such as a Profiler trace or to deduce it by looking at the calls made on the database side. The overhead only happens when Power BI opens a connection to a data source and the result is cached afterwards, so it will only be encountered occasionally and not for every query that is run against your data source. I can say that the overhead can be quite significant in some cases though and can be made worse by other factors such as a lack of available connections or network/gateway issues. Since I have never seen anyone actually use these relationship columns in a DirectQuery model – they are quite handy in Power Query in general though – you should always turn them off when using DirectQuery mode.
[Thanks to Curt Hagenlocher for the information in this post]
If you’re tuning a DirectQuery semantic model in Power BI one of the most important things you need to measure is the total amount of time spent querying your data source(s). Now that the queries Power BI generates to get data from your source can be run in parallel it means you can’t just sum up the durations of the individual queries sent to get the end-to-end duration. The good news is that there are new traces event available in Log Analytics (though not in Profiler at the time of writing) which solves this problem.
The events have the OperationName ProgressReportBegin/ProgressReportEnd and the OperationDetailName ParallelSession. Here’s a simple Log Analytics query that you can use to see how this event works:
PowerBIDatasetsWorkspace | where TimeGenerated > ago(10min) | where OperationName in ("QueryBegin", "QueryEnd", "DirectQueryBegin", "DirectQueryEnd", "ProgressReportBegin", "ProgressReportEnd") | project OperationName, OperationDetailName, EventText, TimeGenerated, DurationMs,CpuTimeMs, DatasetMode, XmlaRequestId | order by TimeGenerated asc
Here’s what this query returned for a single DAX query that generated multiple SQL queries (represented by DirectQueryBegin/End event pairs) against a relational data source:
Notice that there is just one ProgressReportBegin/End event pair per query; this is always the case, no matter how many fact tables or data sources are used, at least as far as I know. The ProgressReportBegin event for ParallelSession comes before the DirectQueryBegin/End event pairs and the associated End event comes after the final DirectQueryEnd event. The DurationMs column for the ProgressReportEnd event gives you the total duration in milliseconds of all the DirectQuery events. In this case there were six SQL queries sent to the source (and so six DirectQueryBegin/End event pairs) each of which took between 1-2 seconds. However, since all of these queries ran in parallel, the overall duration was still just over 2 seconds.
Hybrid tables – tables which contain both Import mode and DirectQuery mode partitions to hold data from different time periods – have been around for a while. They are useful in cases where your historic data doesn’t change but your most recent data changes very frequently and you need to reflect those changes in your reports; you can also have “reverse hybrid tables” where the latest data is in Import mode but your historic data (which may not be queried often but still needs to be available) is in DirectQuery mode. Up to now they had a problem though: even when you were querying data that was in the Import mode partition, Power BI still sent a SQL query to the DirectQuery partition and that could hurt performance. That problem is now solved with the new dataCoverageDefinition property on the DirectQuery partition.
What dataCoverageDefinition does is tell Power BI what data is present in the DirectQuery partition so it knows whether to generate a SQL query to get data from your DirectQuery source or not. It takes the form of a simple DAX expression that returns True for rows that are stored in the DirectQuery partition, for example:
RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}
If you’re working with large fact tables in DirectQuery mode and don’t have a religious objection to some of that data being stored in Import mode, you should check out hybrid tables because they can really improve report performance!
I don’t have anything against the use of calculated columns in Power BI semantic models in general but you do need to be careful using them with DirectQuery mode. In particular when you have a DirectQuery connection to another Power BI semantic model – also known as a composite model on a Power BI semantic model – it’s very easy to cause serious performance problems with calculated columns. Let’s see a simple example of why this is.
Let’s say you have an Import mode semantic model called Source Model containing sales data:
Here’s the contents of the Sales table:
And here’s the definition of the Sales Amount measure:
Sales Amount = SUM(Sales[Sales])
Now, let’s say this semantic model gets published to the Service and you open Power BI Desktop, create a Live connection to this model and then click the “Make changes to this model” button to create a composite model. You now have a local composite model in Power BI Desktop that is connected to the Source Model semantic model.
Next, you decide to create a calculated column on the Sales table and after reading Marco and Alberto’s article on circular dependencies (because you’ll definitely run into circular dependency errors) come up with the following code:
Great! You can now build a report that looks like this:
This is where the first warning comes though. In Import mode semantic models calculated columns are evaluated when the model is refreshed, but in DirectQuery mode they are evaluated at query time if the query needs to use them. Performance Analyzer shows two DAX queries generated for the visual above. The first is the query that the visual runs against the local semantic model in Power BI Desktop, which looks like this:
EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, 'Country'[Country], 'Product'[Product]
There’s nothing much interesting here. However, there’s a second DAX query generated: the one generated by the local model to get the data it needs from the Source Model semantic model in the Service. This query looks like this:
EVALUATE ASDQ___DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, 'Country'[Country], 'Product'[Product]
Notice, right at the top of the query, a DEFINE statement that defines the Tax calculated column. Every time this visual is rendered in your Power BI report the calculated column will be re-evaluated and that will have a performance overhead. If the calculated column had been created in Source Model (which is Import mode remember) it would have been evaluated when the model was refreshed and any queries that used it would probably be faster as a result.
There’s something else to watch out for though. Let’s say you define a second calculated column in the local model like so:
You can now create a table that looks like this, which does include the Apple Sales column but does not include the Tax column:
The DAX query sent by the visual to the local model is not worth looking at. However, the DAX query sent by the local model to Source Model does have something surprising in it:
EVALUATE ASDQ___DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, 'Country'[Country], 'Product'[Product]
There’s a DEFINE statement for the Apple Sales column as you would expect. However there is also a DEFINE statement for the Tax column, which means it is evaluated too even though it’s not shown in the visual and it seems as though the Apple Sales column doesn’t reference it – or does it? I’m not going to try to explain what’s going on here (again, this is best left to Marco and Alberto) but the important point is that both calculated columns are now being evaluated at query time which means the query will be even slower. If you have large data volumes and/or your calculated columns contain some complex DAX you could end up with some very slow reports. If you’re using Premium then you could also be putting a lot of extra load on your capacity.
Composite models are an important part of Power BI’s self-service story and it’s inevitable that at some point your developers will want to use build calculated columns in them. As always you need to monitor the performance of your reports carefully, both in terms of duration and CPU usage, while you are building them to make sure they are well optimised and keep the use of calculated columns in composite models to a minimum.
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.
