Power BI, Caching, Parallelism And Power Query Refresh Performance

Some time ago a customer of mine (thank you, Robert Lochner) showed me a very interesting scenario where a set of Power Query queries in Power BI Desktop refreshed a lot faster with the “Enable Parallel Loading Of Tables” option turned off. This seemed a bit strange, but I have recently been reading lots of posts by Ehren von Lehe of the Power Query dev team on caching and query evaluation on the Power Query MSDN forum and two posts in particular, here and here, offered an explanation of what was going on. In this post I will walk through a very simple scenario that illustrates the information in that post and sheds some light on the internals of the Power Query engine.

Let’s say you have very simple web service built in Flow that, when it receives a GET request, waits 5 seconds and then returns the text “Hello”:

image

Here’s the M code for a Power Query query called SourceQuery that calls this web service and returns a table with one row and one column containing the text “Hello”:

let
    Source = Web.Contents("https://entermyurlhere.com"),
    ToLines = Lines.FromBinary(Source, null, null, 65001),
    ToTable = Table.FromColumns({ToLines})
in
    ToTable

image

Let’s also say you have four other identical queries called R1, R2, R3 and R4 that reference this query and do nothing but return the same table. Here’s the M code for these queries:

let
    Source = SourceQuery
in
    Source

Now, here’s the big question! If the output of SourceQuery is not loaded into Power BI but the output of R1, R2, R3 and R4 is loaded, as follows:

image

How many times would you expect the web service to be called when you refresh your dataset? One or four?

With the setting of “Enable Parallel Loading Of Tables” turned on, which is the default, the “Enable Data Preview To Download In The Background” setting turned off and Data Privacy checks turned off, Fiddler (see here for how to use it) shows the web service is called four times. SQL Server Profiler (see here for how to use it with Power BI) shows that the four queries are run in parallel and each query takes just over 5 seconds.

This seems wrong, because your natural inclination is to think that the Power Query engine evaluates SourceQuery first, and after that has happened the data passes from there to R1, R2, R3 and R4. In fact what happens is the reverse: R1, R2, R3 and R4 are evaluated in parallel and because each of them reference SourceQuery, then each of them causes SourceQuery to be evaluated independently. Yet another post by Ehren on the forum here explains this in detail.

The Power Query engine does do some caching in a ‘persistent cache’ that can be shared between queries and which stores the data requested from some types of data source on disk – it will store the data returned by Web.Contents for example, but not File.Contents – and only in some situations. The key statements about the persistent cache from this thread relevant to this particular problem are:

Power Query’s persistent cache (which stores data on disk during a particular refresh) is updated via a background thread. And separate evaluations (i.e. separate Microsoft.Mashup.Container.*.exe processes) running at the same time are not coordinated; when evaluation A is accessing the persistent cache (including updating it), this doesn’t block evaluation B from accessing the cache. This means that even when using a shared persistent cache, PQ can potentially end up requesting the same data twice. It depends on the timing of the various requests.

and

…you can reduce the number of evaluations happening at any given time (and thus the likelihood of timing-related cache misses, or additional unwanted requests) by doing the following:

  • Disabling background analysis (which pre-calculates the PQ Editor previews behind the scenes)
  • Disabling the Data Privacy Firewall (which does its own evaluations during refresh that could potentially cause duplicate requests, and also only consume a subset of the data in certain cases)

In PBIDesktop, you can also reduce the likelihood of timing-related cache misses by disabling parallel loading. (In Excel, the loading of multiple queries is always sequential)

As a result, with the “Enable Parallel Loading Of Tables” setting turned off, so the queries above are evaluated in series, Fiddler shows the web service is called only once. What’s more, Profiler shows that only one of the queries out of R1, R2, R3 and R4 takes just over five seconds while the other three, executed after it, are almost instant.

This must be because, when the queries are executed in series, the first query refreshes and data gets loaded into Power Query’s persistent cache. After that, even though the three subsequent queries also cause SourceQuery to be evaluated, each time SourceQuery is evaluated it can reuse the data stored in the persistent cache.

In contrast, with the four queries refreshed in parallel, each evaluation of SourceQuery takes place before the persistent cache has been populated and therefore the web service is called four times. This is slower, but in this case not much slower, than the scenario with the four queries executed in series – each query evaluation takes just over five seconds but remember that they are being executed in parallel so the overall duration is not much more than five seconds. In other cases the difference between parallel and sequential query execution could be a lot larger.

Now for the bad news: the “Enable Parallel Loading Of Tables” setting only works in Power BI Desktop, and there is no equivalent setting in the Power BI service. When a dataset is refreshed in the service, as far as I can tell the queries in the dataset are always evaluated in parallel. In Excel, as Ehren says, all queries are executed sequentially.

Is it possible to make sure the web service is called only once with all the queries executed in parallel? Yes, but not in a completely reliable way. The first thing to say here is that Table.Buffer and related buffering functions are not, as far as I understand it, useful here: they buffer data in memory within a single chain of execution, and in this case we have four separate chains of execution for R1, R2, R3 and R4. Instead what I have found that works is inserting a delay that gives the persistent cache time to be populated. If you keep the SourceQuery and R1 queries the same, and then alter the M code for R2, R3 and R4 so they wait for ten seconds (using Function.InvokeAfter) before returning as follows:

let
    Source = Function.InvokeAfter(
        ()=>SourceQuery, 
        #duration(0,0,0,10)
        )
in
    Source

 

In this particular case this results in the slowest refresh times and it only works because I can be sure that the call to the web service takes five seconds.

There’s one last thing to say here: wouldn’t it be nice if we could ensure that the web service was only called once, and all subsequent queries got their data from the a persisted copy of the data? As Matthew Roche points out here, this is exactly what dataflows allow you to do. Taking SourceQuery and turning it into an entity in a dataflow would result in a single call to the web service when the entity is refreshed, the data from the web service being persisted in the dataflow. You would then just need to have the queries R1, R2, R3 and R4 in your dataset, change them so they get their data from the entity in the dataflow, and as a result they would use the persisted copy of the data in the dataflow and would not call the web service. Another reason to use dataflows!

[I hope all the information in this post is correct  – it is based on Ehren’s forum posts and my own observations alone. If anyone from the Power Query dev team is reading this and spots an error, please let me know]

16 responses

  1. Very helpful post Chris, combined with the technet forum posts this has certainly got me thinking.

    1. Based on this post I assume that there is no just within Power Query (as in not using DW/dataflows) there is no way to reliably force a cache of SourceQuery that would not be reevaluated for each of R1-4.

    2. From reading https://social.technet.microsoft.com/Forums/en-US/34e454b5-3a18-4eef-b920-40703c93f390/tablebuffer-for-cashing-intermediate-query-results-or-how-workaround-unnecessary-queries-issue?forum=powerquery At the end a question is asked about using Table.Buffer to buffer a table that contains nested non scalar values, Ehren replies that these non scalars are not evaluated by Table.Buffer.

    Is there a way to force the evaluation of all values both scalar and non scalar, including both nonscalars within the parent table but non scalars within non scalars?

    I am thinking of what happens when you load in a json file and you get many layers of tables, lists, and records.

    • To answer your questions:
      1) No, I don’t think so – but I may be wrong
      2) That’s something I want to do more research on in future. I think using Binary.Buffer to buffer the actual json file might work here, even if Table.Buffer does not.

      • The output of Json.Document is always buffered in memory (though transformations on top it are not necessarily). This is not likely to change.

  2. Pingback: Power BI, Caching, Parallelism And Power Query Refresh Performance — Chris Webb’s BI Blog | Jim Ehrenberg (SharePoint Pros, Inc.)

  3. Pingback: Power BI XMLA Endpoint, presentation view, Dataflows and more... (April 1, 2019) | Guy in a Cube

  4. Pingback: Comparing The Performance Of Reading Data From Files With File.Contents And Web.Contents In Power Query And Power BI « Chris Webb's BI Blog

  5. Pingback: Power BI, Caching, Parallelism And Power Query Refresh Performance | Pardaan.com

  6. Pingback: Power Query Memory Usage, Dataflow Container Size And Refresh Performance « Chris Webb's BI Blog

  7. Thank you very much Chris for this great overview and the explanations contained in your post! I’m also querying a web service with Excel Power Query. I have cascaded multiple nested queries which reference as their source to one main query which contains filtered data from the web source. Unfortunately when I do a Refresh All every query in the hierarchy below the main query re-downloads it’s data from the web service instead of using the cached data from the main query to which all the nested queries actually refer to as their source. I wish I could apply the dataflow concept which you described but I’m not sure if this is possible in Excel Power Query. Do you have more information on this or do you know of any other possible solution? The only solution which has so far come to my mind is to load the main query which is directly referenced to the web source into a Excel Worksheet and reference all other queries to this Worksheet. It’s an ugly workaround and I would be very happy if I could avoid it. Thank you, Helge

  8. Pingback: Why Does Power BI Query My Data Source More Than Once? « Chris Webb's BI Blog

  9. Great article, I was trying get a definitv answer regarding this for some time now. However, there is one thing which is still unclear to me. How would all this behave with a function referencing a table for lookups; like:

    (param1, param2) => Table.First(Table.SelectRows(ReferenceToLookUpTableWithHeaderP1To3, each _[P1] = param1 and _[P2] = param2))[P3]

    I guess, it may be better to use some kind of JOIN here? But anyway, if a query uses this function (e.g., in a Table.AddColumn operation), would there be a call to the source on every function call or would caching jump in? Would Table.Buffer be helpful here or would it create the same problem as with normal referenced queries?

    Thank you very much for this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: