One of the factors that affects dataset refresh performance in Power BI is the number of objects that are refreshed in parallel. At the time of writing there is a default maximum of six objects that can be refreshed in parallel in Power BI Premium but this can be increased by using custom TMSL scripts to run your refresh.
A few months ago I blogged about how partitioning a table in Power BI Premium can speed up refresh performance. The dataset I created for that post contains a single table with nine partitions, each of which is connected to a CSV file stored in ADLSgen2 storage. Using the technique described by Phil Seamark here I was able to visualise the amount of parallelism when the dataset is refreshed in a Premium Per User workspace:
In this case I started the refresh from the Power BI portal so the default parallelism settings were used. The y axis on this graph shows there were six processing slots available, which means that six objects could be refreshed in parallel – and because there are nine partitions in the only table in the dataset, this in turn meant that some slots had to refresh two partitions. Overall the dataset took 33 seconds to refresh.
However, if you connect from SQL Server Management Studio to the dataset via the workspace’s XMLA Endpoint (it’s very similar to how you connect Profiler, something I blogged about here) you can construct a TMSL script to refresh these partitions with more parallelism. You can generate a TMSL script by right-clicking on your table in the Object Explorer pane and selecting Partitions:
…then, in the Partitions dialog, selecting all the partitions and clicking the Process button (in this case ‘process’ means the same thing as ‘refresh’):
…then, on the Process Partition(s) dialog, making sure all the partitions are selected, selecting Process Full from the Mode dropdown:
…and then clicking the Script button and selecting Script Action to New Query Window:
This generates a new TMSL script with a Refresh command that refreshes all the partitions:
This needs one more change to enable more parallelism though: it needs to be wrapped in a TMSL Sequence command that contains the maxParallelism property. Here’s the snippet that goes before the refresh (you also need to close the braces after the Refresh command too):
{
"sequence":
{
"maxParallelism": 9,
Executing this command refreshed all nine partitions in parallel in nine slots:
As you can see, increasing the number of refresh slots in this way can have a big impact on refresh performance – although, of course, you need to have enough tables or partitions to take advantage of any parallelism and you also need to be sure that your data source can handle increased parallelism. You can try setting MaxParallelism to any value up to 30 although no guarantees can be made about how many slots are available at any given time. It’s also worth pointing out that there are scenarios where you may want to set maxParallelism to a value that is lower than the default of six, for example to reduce to load on data sources that can’t handle many parallel queries.
[Thanks to Akshai Mirchandani for the information in this post]
Hi Chris, thanks for sharing! How about refreshes that use an incremental refresh policy, is that also using the default value for MaxParallelism if not stated otherwise? Meaning, if a table has a policy to refresh 10 days, it will not do more than 6 of those days in parallel, right?
If that is indeed the case, will that process use the MaxParallelism value that I provide manually in the TMSL?
PS. In your example TMSL JSON, the ‘operations’ part is also something you manually added, but you missed that in the code example.
No, the operations part was generated automatically in SQL Management Studio – I definitely did not add it.
Re incremental refresh, I’m not sure. If you test it let me know what you find. I think it should be possible to use MaxParallelism with incremental refresh somehow.
Regarding the operations part, I meant the 4 lines of code you have as a snippet, the operations part is missing in that snippet, that’s all.
I did some tests using PPU and a partitioned table, using the following m code for each partition:
#table(type table [PartitionNum = number, Start = datetime, End = datetime], {{1,DateTime.LocalNow(),Function.InvokeAfter(()=> DateTime.LocalNow(), #duration(0,0,0,10))}})
It seems that once you use the TMSL sequence command a single time (regardless of the inclusion of the maxParallelism property), the default value of maxParallelism is no longer set to 6 using the TMSL refresh command without the sequence command.
So doing a couple of refreshes in the following order yields:
Refresh from the Power BI Service -> maxParallelism bounded
TMSL refresh command -> maxParallelism bounded
TMSL sequence command without maxParallelism -> maxParallelism unbounded
TMSL refresh command -> maxParallelism unbounded
Refresh from the Power BI Service -> maxParallelism bounded
TMSL refresh command -> maxParallelism unbounded
I’ve also tried AMO-TOM and got the same behavior where maxParallelism is always unbounded by default (the same as in AAS).
Also, doing a full process for a whole partitioned table seems to process the data of a single partition before starting to process the data of any other partition.
This doesn’t happen if one partition which includes data is excluded from the processing.
I’ve tried adding a processed partition with 0 rows and then process all the other partitions, but it doesn’t help. I guess Vertipaq needs to decide on hashing algorithms for the columns or something of the sort.
Thanks Chris, another great tip from you that has gone straight to prod 🙂 I didn’t know about that sequence wrapper – we have plenty of tables and partitions to throw at this, processing times have been impacted very positively 🙂
Thanks again!
This is behind-the-scenes of incremental refresh in power bi, but problem here is when you create partition for dataset now the pbix file won’t let you download. How to handle this?
Documentation here:
https://learn.microsoft.com/en-us/analysis-services/tmsl/refresh-command-tmsl?view=asallproducts-allversions
Says that refresh always runs in parallel unless you throttle it with Sequence command (TMSL).
Why then use sequence at all with Refresh?
Thanks Chris, as the different Premium SKU has limitation on Model refresh parallelism, does the partition contributes to the refresh slots? or even if a dataset is partitioned, refreshing all partitions still counts as one slot towards the SKU slot limit.
The published limits for Premium are about the number of models that can refresh in parallel, so nothing to do with parallelism within an individual refresh