Here’s a recording of a session I did for the Manchester (UK) Power BI user group recently on best practices for DirectQuery mode in Power BI:
I’ve done it for a few other groups over the last six months but this is the latest and best version, I think.
I’ve worked with several customers using DirectQuery mode since I joined the CAT team and learned a lot along the way. Some of this knowledge has been written up by me (eg this post on Snowflake DirectQuery mode best practices), some of it by the people who work with the data sources Power BI runs on top of (see posts by Dany Hoter on the Azure Data Explorer blog, for example; there’s also going to be a lot of new material on DirectQuery for Databricks coming soon, with this post as a start). There’s a lot of detailed information in the docs too, for example here, here and here.
But remember folks: the most important piece of advice around DirectQuery is to not use it unless you’re really, really, really sure you have no other option. It’s possible to make it work well but it takes a lot more tuning and specialist skill than Import mode!
Another new metric has appeared in Profiler/Log Analytics recently, added to the end of the query text shown in the TextData column for the Query End event. It’s called WaitTime:
What does WaitTime represent? Here’s the technical explanation: it’s the wait time on the query thread pool in the Analysis Services engine before the query starts to run. But what does this mean for you as someone trying to tune DAX queries in Power BI?
As I explained recently in my post on load testing in Power BI, when you publish your dataset to the Power BI Service it runs on an instance of a version of the Analysis Services engine on a node somewhere inside the infrastructure that we at Microsoft manage for you. You don’t see any of this, but there’s still a machine with a limited amount of CPU and memory available and it can get overloaded if there are too many expensive queries running, or too many datasets being refreshed at the same time, or even one large dataset being refreshed; part of the magic of the Power BI Service is how we move datasets around to ensure this happens as infrequently as possible. If, however, you’re very unlucky you may run a query on a dataset that’s running on an overloaded machine and the performance of that query may be affected because the CPU is too busy. In that case you may see a value for WaitTime that is greater than zero and this means that your query was slower than it might otherwise have been.
This can happen for datasets in both Import mode and DirectQuery mode. It can happen because of something that’s happening on the dataset you’re querying – such as a load test or a full refresh – or it can happen because someone else is doing something similar with a dataset that happens to be on the same node as yours and the Power BI Service hasn’t managed to move your dataset to a quieter node yet. It is not the same thing as throttling in Premium (or “interactive request delays” as it’s officially called) but if you have a capacity that’s being throttled because of something you’re doing with a specific dataset then queries on that dataset may also have a WaitTime greater than zero.
I haven’t seen a non-zero value for WaitTime yet, but I only found out about it yesterday so I’m looking forward to seeing whether I see one next time I’m doing some performance tuning. If you see one let me know in the comments.
[Thanks once again to Akshai Mirchandani for this information]
This post is a follow-up to my recent post on identifying CPU and memory-intensive Power Query queries in Power BI. In that post I pointed out that Profiler and Log Analytics now gives you information on the CPU and memory used by an individual Power Query query when importing data into Power BI. What I didn’t notice when I wrote that post is that there is also now information available in Profiler and Log Analytics that tells you about peak memory and CPU usage across all Power Query queries for a single refresh in the Power BI Service, as well as memory usage for the refresh as a whole.
Using the same dataset from my previous post, I ran a Profiler trace on the workspace and captured the Command Begin and Command End events while I refreshed the dataset. Here’s what Profiler shows for the Command End event:
In the upper pane, the Duration column tells you how long the refresh took in milliseconds and the CPUTime column tells you how much CPU was used by both the Analysis Services engine and the Power Query engine during the refresh. This is not new, and I wrote about the CPUTime column last year here.
In the lower pane where the TMSL for the refresh operation is shown – this is the text from the TextData column – underneath the TMSL there are three new values shown:
PeakMemory shows the maximum amount of memory used during the refresh operation. This includes memory used by the Analysis Services engine and the Power Query engine.
MashupCPUTime shows the total amount of CPU used by the Power Query engine for all Power Query queries used to import data into the dataset. This value will always be less than the value shown in the CPUTime column in the upper pane.
MashupPeakMemory shows the maximum amount of memory used during the refresh operation by just the Power Query engine across all query evaluations. It’s important to note that this value may not be entirely accurate since the memory usage values, and therefore the peaks, are captured asynchronously so there could be higher peaks that are not detected.
This new information should be extremely useful for troubleshooting refresh failures that are caused by excessive memory consumption.
[Thanks to Akshai Mirchandani for this information]
If you’re about to put a big Power BI project into production, have you planned to do any load testing? If not, stop whatever you’re doing and read this!
In my job on the Power BI CAT team at Microsoft it’s common for us to be called in to deal with poorly performing reports. Often, these performance problems only become apparent after the reports have gone live: performance was fine when it was just one developer testing it, but as soon as multiple end-users start running reports they complain about slowness and timeouts. At this point it’s much harder to change or fix anything because time is short, everyone’s panicking and blaming each other, and the users are trying to do their jobs with a solution that isn’t fit for purpose. Of course if the developers had done some load testing then these problems would have been picked up much earlier.
What is the cause of the problem? As the saying goes, the cloud is just somebody else’s computer and when you publish your datasets to the Power BI Service they run (at least at the time of writing) on a single physical node – which of course has a finite amount of memory and CPU. If you have too many users trying to run reports at the same time then that memory and CPU will be exhausted and performance will suffer. This is a problem that the new dataset scale-out feature for Premium, currently in preview, will partially solve by creating multiple replicas of your dataset that run on separate physical nodes in the Power BI Service. I say “partially solve” because there is another constraint: the amount of memory and CPU that is available for use in Shared capacity (commonly called Pro) or with Premium Per User, neither of which are fully documented, or that you have bought with your Premium capacity. If you try to use more memory than you’re allowed you’ll get errors; if you try to use more CPU than is available you’ll be throttled. It’s worth taking some time reading the Premium documentation to understand how limits on CPU usage are calculated and enforced.
As a result of all this it’s important to check that normal production usage of your dataset does not result in any of these limits being hit. These limits are very generous and in the vast majority of cases your reports will perform well without you doing anything, but if you’re working with large data volumes, complex calculations, reports with many visuals and/or thousands of users you can’t assume that everything will be ok. This applies whether you’re building an internal BI solution or using Power BI Embedded to do B2B reporting; similar questions are raised when you’re trying to work out how much Premium capacity you should buy to support a large number of small self-service solutions, or how much Premium capacity is needed when you’re migrating from Azure Analysis Services. If you’re using DirectQuery mode then you might be surprised at how much CPU still gets used on your Premium capacity, and of course you have the additional task of ensuring your data source can handle all the queries Power BI needs to run on it; if you’re connecting to your data source via an on-premises data gateway then you need to ensure the machine your gateway is running on is properly specified too.
How can you do load testing with Power BI? There are a few options for automated load testing, but the most widely used is the Realistic Load Test Tool (see also my colleague Sergei Gundorov’s simplified version here) and Kristyna Hughes has a great post on how to use it here. If automated testing sounds too complex then you can always round up ten or so people and get them to run through a series of interactions on the reports; this won’t give you results you can compare reliably from test to test, but it will tell you whether you have a problem or not. While running a load test you should measure report rendering performance on the front-end – the Realistic Load Testing Tool will capture this information for you – and if you’re using Premium you should also capture information on the back-end either by running a Profiler trace or enabling Log Analytics on your workspace, so you can get accurate DAX query timings. The Premium Capacity Metrics App will tell you how close you’re coming to the limits of your Premium capacity.
One mistake that people often make when load testing Power BI is over-estimating how many concurrent users they’ll have. Even with an enterprise BI solution it’s relatively rare to have more than ten people running queries at exactly the same time. The most concurrent users I’ve ever seen is at a large retail customer where, every morning at 8:30am, staff in every store had a meeting where they all looked at the same Power BI report and even then there were no more than 2-300 concurrent users.
What can you do if your load testing shows you have a problem? The first thing is to do some tuning – and remember, it’s more important to tune your queries to reduce CPU Time than to make them faster. Look at the way your data is modelled, make your DAX calculations more efficient, reduce the number of visuals in your report and so on. Once you’ve done that, if you’re using Shared (Pro) then you should consider buying PPU or a Premium capacity. If you’re already using Premium you should consider testing the scale-out feature mentioned above and either moving up to a larger SKU or enabling autoscale.
When you add a field to the filter pane in a Power BI report and select the “Basic filtering” filter type, in most cases you’ll see some numbers next to the field values:
These numbers are counts of the number of rows for each value in the table that the field is from. The query to get these counts is usually quite fast and inexpensive, but if you’re filtering on a field from a very large table (for example a fact table) and/or using DirectQuery mode that might not be true. For example, the screenshot above is taken from a DirectQuery dataset and here’s the SQL query that generates the counts shown:
Luckily there’s a way to disable these queries and stop the counts being displayed: set the Discourage Implicit Measures property to true on your dataset. The main purpose of this property is to stop the automatic creation of implicit measures when building Power BI reports; this makes sense when you’re using calculation groups, for example, and when you add a calculation group to your dataset then this property is set to true for you. You can also set Discourage Implicit Measures to true manually by connecting to the dataset using Tabular Editor:
Here’s what the filter pane looks like with Discourage Implicit Measures set to true:
Just to be safe, I think it makes sense to set Discourage Implicit Measures to true for all DirectQuery datasets to reduce the overall number of queries run against your data source and reduce the risk of a really expensive query being run. I don’t think seeing the counts adds much value for end users anyway.
[Thanks to Ed Hansberry for pointing out this behaviour and John Vulner for explaining it]
When interacting with a Power BI report you may occasionally get the following error:
Couldn’t load the data for this visual
We can’t display this visual because a measure is used in cross highlighting. Please remove the measure or cross highlight.
What does this mean? The most important thing to explain is that you have not done anything wrong: you have, unfortunately, run into a limitation of Power BI where it can’t generate the query needed for a selection on a visual. The issue occurs in some very rare cases when Power BI needs to filter a measure using multiple fields from different tables. Since only workarounds involve changing your visuals, changing the way you interact with your report or changing the way your report behaves, it is helpful to understand the scenarios where you will encounter it.
To do this, take the following basic Power BI dataset:
There are two products in the Product table – Apples and Oranges – and two countries in the Country table – UK and France, plus some sales values in the fact table. All the data in the dataset (minus the keys) can be shown in a table like so:
Note that fields from all three tables in the dataset (the Country field from the Country table, the Product field from the Product table and the Sales field from the Sales table) are used in this visual and Sales is used as a measure.
One way to trigger this error is to select any one of the rows in the body of this table, right-click and select Exclude:
The presence of a measure plus two fields from two different tables is the key to making the error occur: if you remove either the Country or Product fields then the Exclude will work ok. You could also remove the Sales measure, but it’s likely both of these changes will result in the visual not displaying what you want it to display so you’re better off educating your users to expect to see this error if they use Exclude. Selecting the rows you want to keep and using Include instead also works:
Another way to trigger the error is to cross-highlight this table by selecting two rows from different levels in a separate matrix visual on the same page. So, for example, let’s say the same data was displayed in a matrix visual alongside the table visual on the same page:
If, in the matrix visual, you select the France row and the UK/Apples row at the same time, then the error will occur again:
If you use Edit Interactions to stop the matrix from being able to cross highlight the table then you’ll be able to stop the error:
Another possibility is to select rows that are all at the same level to cross highlight instead of selecting rows from different levels. So, for example, if you change the second matrix to be another table and select the two rows for France and the row for UK/Apples, you won’t get the error:
I’m sure there are other scenarios where this occurs (I see this is called out by OKVIZ here for some of their custom visuals) but hopefully these examples are enough to help you understand what’s going on here.
Last year I wrote a post about a change in the Power BI Service that meant the CPU Time associated with Power Query transformations was added to the total shown for a dataset refresh operation in Profiler traces and Log Analytics:
This was useful, but it didn’t tell you directly how much CPU Time was used by Power Query and it didn’t tell you which tables or partitions in a refresh were using the most CPU. It also didn’t tell you anything about Power Query memory usage. The good news that recently there has been another change that solves these problems.
Let’s say you have a Power BI dataset that consists of a single table whose source is the following Power Query query:
let
Source = #table(type table [MyNumber = number], List.Transform({1 .. 1000000}, each {_})),
#"Added Custom" = Table.AddColumn(
Source,
"ARandomNumber",
each Number.RandomBetween(0, 10000),
type number
),
#"Sorted Rows" = Table.Sort(#"Added Custom", {{"ARandomNumber", Order.Ascending}})
in
#"Sorted Rows"
This query creates a table with a million rows, adds a column with random numbers in and then sorts on that column – which is, as you’d expect, a very CPU and memory-hungry operation.
If you refresh this dataset in the Power BI Service and run a Profiler trace on it, looking at the Command Begin/End and Progress Report Begin/End events, this is what you’ll see:
The final Command End event shows the toal duration of the refresh as well as the amount of CPU used by both the Analysis Services engine and Power Query – in this case 24094ms.
If you look at the Progress Report End event associated with the finish of the refresh for the only partition of the only table in the dataset (highlighted in the screenshot above), there’s some extra information:
It shows the amount of CPU Time and the maximum amount of memory used by the Power Query engine while refreshing this partition. In this case the Power Query engine used 19468ms of CPU and reached a peak of 581848KB of memory. I can tell this is going to be really useful for troubleshooting refresh performance issues and out-of-memory errors.
[Thanks to Akshai Mirchandani, Xiaodong Zhang, Ting-Wei Chang and Jast Lu for this information]
Recently, when preparing my session on query folding in Power Query for SQLBits, I wrote the following query to demonstrate the “Might Fold” indicator in Power Query Online:
let
Source = OData.Feed(
"https://services.odata.org/TripPinRESTierService",
null,
[Implementation = "2.0"]
),
Navigation = Source{[Name = "People", Signature = "table"]}[Data],
#"Filtered rows" = Table.SelectRows(Navigation, each [FirstName] = "Angel")
in
#"Filtered rows"
This query connects to the TripPin OData feed (which is public, so you’ll be able to try this yourself) and filters the People table so it only gets the rows where the FirstName column equals “Angel”. If you paste this query into the Advanced Editor in Power Query Online to create a dataflow, you’ll see that the filter shows the “Might Fold” step indicator:
This tells you that Power Query doesn’t know if the filter on the FirstName column folds or not. The Query Plan view shows two alternate plans for if the query folds and if it doesn’t:
The question is, though, why can’t Power Query tell if the query will fold? I didn’t know so I asked Curt Hagenlocher of the Power Query development team, who very kindly explained.
It turns out that OData sources (and also ODBC sources) sometimes misreport their support for query folding. At the time that the query plan above is generated Power Query has already asked the source for its list of supported OData capabilities, but it won’t be until the query is actually run that it will know for sure if the filter can be folded. Similarly, some OData sources have an undeclared maximum URL length limit which means that if Power Query exceeds that it may get an error or even incorrect data back. As a result of this the runtime behaviour of Power Query has several fallbacks: it will try to fold fully, then fold some transforms, then fold nothing. All of which explains why the “Might Fold” step indicator exists.
If you missed out on the big announcement last week about Tabular Model Definition Language and the future of Power BI version control at SQLBits last week, then the recording of the session has already been published:
If you’re a professional Power BI developer you must watch this one! Mathias Thierbach (of https://pbi.tools/ fame) has also published the slides here.
This is just the beginning of a much better story for pro-developers in Power BI…
If, like me (and Ruth), you spend your life in Power BI but have a lingering feeling that you should get round to learning Python for data analysis sometime then here’s something you should check out: the new Data Wrangler extension for Visual Studio Code. All the details are in the announcement blog post:
…and this video is a great introduction to what it does:
Why is it interesting for someone like me? Because it works in a very, very similar way to Power Query – except that instead of generating M code in the background, it generates Python. It doesn’t have the same amount of functionality that Power Query does and the UI is a bit more basic but anyone with Power Query experience will feel immediately at home. I got it up and running very easily and I can see that it will be great for anyone learning Python or who needs a productivity boost.
