Tool to evaluate Refresh operation bottlenecks

[anmattos]

Hello,

I have a data model in Excel 365 that used to update quite quickly (around 30 seconds) when I “Refreshed All”. Then I added some more tables and measures, and then the Refresh process started to present the following behavior:

A) It now takes around 2 minutes (expected it to increase, since I added more calculations);

B) Most of this time Excel spends at this status: “Reading Data... (Press ESC to cancel)” and “Waiting for other Dara Model queries...” (in the Queries tab), during which it seems to be doing nothing, to have hang up, waiting for something, and then it times out and continue as normal. This happens about 2 or 3 times each refresh.

My question is:

Is there any tool that I can use to diagnose what this bottleneck is during the refresh operation?

I tried DAX Studio, but could not find a way to measure timings during a refresh operation. I can only perform time measurements for specific queries. I don't know if I'm missing something as I just began to use this tool.

Also, I can't use Power Query Diagnostics since I'm in Excel and not PowerBI.

Any suggestion about this?

Best regards,

[anmattos · Accepted solution]

I relatively solved my problem using the following method, which I'll describe below for future reference:

In Excel's Power Query, go to FILE ==> OPTIONS AND SETTINGS ==> QUERY OPTIONS.

Go to the DIAGNOSTICS tab and tick the ENABLE TRACING option. Also, click in OPEN CRASH DUMP/TRACES FOLDER, to open the folder where the tracing files will be recorded.

The ENABLE TRACING options is turn on for the remaining of the session, but is automatically turned off after closing Excel. It is now recording everything Excel is doing.

Immediately after that start a Refresh operation. Everything will be recorded in files in the aforementioned folder. After the Refresh operation has finished, close Excel so that the tracing files does not record extra information.

The files with the traces are in the dump folder. Unfortunately, they are quite unfriendly to read. Fortunately, I found a PIBX file set up to read those files. Link below:

https://github.com/DevScope/powerbi-trace-analyser

In my specific case, I didn't find anything really useful. I wish the trace files would be more specific in relation to the operations in the Data Model related to each measure and pivot table being updated, but it shows information in a more high level pattern. Anyway, its results are interesting to check.

[v-prasare]

@anmattos,
Thanks for reaching MS Fabric community support.

since you have mentioned this issue is related excel sheet. i request you to reachout their tech community, where you might find answers for your question

Home | Microsoft Community Hub

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[bhanu_gautam]

@anmattos 

Although you mentioned that you are using Excel and not Power BI, you can still use Power Query Diagnostics within Excel. Here’s how:

Go to the "Data" tab in Excel.
Click on "Get Data" and then "Launch Power Query Editor".
In the Power Query Editor, go to the "Tools" tab and select "Start Diagnostics".
Refresh your queries.
After the refresh is complete, go back to the "Tools" tab and select "Stop Diagnostics".
Review the diagnostics results to identify any bottlenecks.

[anmattos]

Hello,

Thank you for your response. Unfortunately my Power Query doesn't have a Tools tab. Please see below:

I´m using Microsoft® Excel® for Microsoft 365 MSO (Version 2502 Build 16.0.18526.20168) 64-bit

Do I need to activate it somewhere?

Regards,