cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Query Loading Order

How does Power BI determines the order of queries loaded in the data model? Is it determined by the order of sources you get?

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft
Microsoft

Hi @Anonymous,

 

It looks like nothing special loading queries. The queries can be loaded concurrently.

Query_Loading_Order

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-jiascu-msft
Microsoft
Microsoft

Hi @Anonymous,

 

It looks like nothing special loading queries. The queries can be loaded concurrently.

Query_Loading_Order

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Well that maybe, but that doesn't answer the question. Why this is marked as the solution baffles me. 

Anonymous
Not applicable

Hi Nick,

 

But what if i wanted to set an order? Smiley Surprised

I was thinking the exact same thing.
I have a model that has evolved over time.  It now takes about 10min to load all the data.
One of the tables is a single row, Last Refresh table, that reports on the currency of the data.
Being small, this table is always one of the first to load, and that means the Last Refresh time stamp is always a little bit out.
It's not a big issue, but it would be nice if there was some way to flag that this table should load last.

In the following video there is a possible solution:
https://www.youtube.com/watch?v=YfdypVSYb9c
After disabiling the concurrent refresh ([!] I have not tested the effect upon performance of doing this), next you can add an M Code function function.InvokeAfter in the advanced editor.

I think that you could set it up where the function that is called "daisy chains" through the load order you're interested in.

 

However there's probably a more elegant solution. If I find it, then I'll check back... because I'd like to add a table for load duration, in addition to the load time through

 

datetime.localnow

or, in my case, the pl/SQL:

Select

TO_CHAR(SYSDATE, 'DDMONYYYY HH24:MI:SS') "Retrieved"

From

Dual

Unrelated, kind of (but required sequential loads)... I wanted to calculate data load time durations.

 

Here is a not-so-elegant solution I just used in 3 steps:

 

 

1) Get a start time (from your data or a table with DateTime.LocalNow())

2) invoke a simple function which uses the slowest data set

3) Get an end time - use DateTime.LocalNow() in a table which is dependent upon steps 1 and 2.

 

 

1) Getting a Refresh Start time table loaded:

 

My first step was that I loaded a table which refreshes immediately upon connection (this was done using an SQL statement in the above post to get the time from the queried database. You could use DateTime.LocalNow() instead, though.)

EnglishCJ_1-1631148295234.png

 

2) invoke a function to call a reference to your slowest data set.

EnglishCJ_2-1631153908573.png

(no closed paretheses. Choose a simple aggregate function like count or max)

 

EnglishCJ_3-1631153950915.png

(Open the function)

 

EnglishCJ_4-1631153986993.png

(invoke the data you want to measure load times for)

 

 

3) load the invoked function into a table to calculate the duration of the load:

EnglishCJ_5-1631154470280.png

(convert to table)

 

EnglishCJ_6-1631154534019.png

(Load the start time)

 

EnglishCJ_7-1631154579782.png

(Get the DateTime.LocalNow() time after the invoked function is loaded)

 

 

EnglishCJ_8-1631154655749.png

Calculate the duration

 

 

 

EnglishCJ_0-1631148059787.png

EnglishCJ_0-1631155061116.png

 

 

I timed my query with and without the duration measurement and consistently got a time of 72 or 73 seconds

 

Trial 1 (with): 20s to connect 93s to load (73s)
Trial 2 (with): 14s to connect 86s to load (72s)
Trial 3 (with just evaluating): 7s to evaluate 80s to load (73s)
Trial 4 (without): 13s to connect 86s to load (73s)

 

 

The technique doesn't work if you load the data-for-duration-calculation directly into the table doing the calculation: you need to invoke a function Then load the data into the aggreation. When invoking the function use something simple like Table.RowCount [not Table.RowCount(Your Data)... that loads it directly and allows Power Query to use paralell processing by creating a reference.]

 

It's not an elegant solution.

 

Chris Webb has a series of blog posts regarding the Diagnostic function... which is great if you can pour through your appdata log files... but I have no idea how you calculate load duration in the PowerBI webservice where you don't have the log files.

 

Maybe there is a better solution out there. If anyone finds it, or if the Power BI Team makes a tool in the service for it, where it can populate within a dashboard - please let the community know.

 

My next approach would be to use some Power Automate magic, but I think I'll call it a night.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.