Forum Discussion
Tool to evaluate Refresh operation bottlenecks
- 1 year ago
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.
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.
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,