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]
Seems like this information is not exposed to Log Analytics connected to the PBI workspace.
Is this only available via Profiler?
if yes, are there any plans to also add it to Log Analytics?
-gerhard
Hmm, it should be in Log Analytics although I admit I didn’t test it. Can you confirm that when you do a new refresh (ie you aren’t looking at older data) you see this information in Profiler but not in Log Analytics?
well, I only tested it on Log Analytics
I have some issues with Profiler on that environment so I cannot really test it there :/
Maybe wait a bit – hopefully it will turn up in your tenant very soon. I was assuming it had already rolled out across the world but maybe not.
I just checked again and the only tables where I can find this new information are those which had been created using “Enter Data” in Power Query ?!?
I simply ran a query where the TextData contains “Mashup” so it either still has not yet been rolled out to all regions (I am on West Europe) or it is just not present in the Log Analytics logs
Next idea: are you using a gateway?
yes, we use a gateway – maybe we need to update the gateway version?
No, I don’t think you will ever see these messages when you use a gateway
that also does not sound like this will be added in the future 🙁
No, but if you’re interested to know how much CPU your queries use on your gateway machine you can get that from the gateway logs
Do you know how we can increase data fetch from default 10000 rows (Integer Data column in SQL profiler trace) to fetch more rows in Power BI service.
There are multiple Progress Report End in the screenshot, huh?
Even with one table there are a lot of operations. For example, there are Progress Report End events for the partition, the table that partition is in, and the whole dataset. It’s the Progress Report End for the partition that contains the new information.