Forum Discussion

anmattos's avatar
anmattos
Advocate I
1 year ago
Solved

Tool to evaluate Refresh operation bottlenecks

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 star...
  • anmattos's avatar
    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.