“Reading Data” and “Waiting for other Dara Model queries...” question

[anmattos]

Hello,

I have a question that seems simple, but I couldn’t find a satisfactory answer on the web.

What does Power Query and Power Pivot in Excel are doing during a “Refresh” operation and the status in the status bar is “Reading Data... (Press ESC to cancel)” and the connections read “Waiting for other Dara Model queries...”, just like in the image attached.

I’m asking this because I have a data model for Excel 365. It 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 questions are:

1) What does Power Query and Power Pivot in Excel are doing during a “Refresh” operation and the status in the status bar is “Reading Data... (Press ESC to cancel)” and the connections read “Waiting for other Dara Model queries...”? Is it waiting for something, or is it just processing normally?

2) Is there any setting in the data model that can be used to avoid this hang up?

3) Is there any tool that can be used to identify in what part of the Refresh process exactly this hang up happens?

Information:

- I’m using Excel 365.

- My source data are Excel files in Sharepoint and lookup tables inside the Excel file itself.

- My main data set is not big, around 5k lines, but somewhat wide with around 32 columns, being 10 calculated columns in Power Pivot. (Not counting columns removed and created in Power Query)

- I have around 5 small lookup tables and a Calendar Table.

- The calendar table has multiple connections with the main data table due to many date columns in the data, and some of those date columns are calculated columns.

- I have around 70 Measures and 22 pivot tables.

- I have many slicers, but for most of them I disabled the option to hide entries not available, to save processing.

- I disabled the option to refresh some small lookup tables upon “Refresh All” command.

Best regards,

[BeaBF · Accepted solution]

@anmattos Hi! 

During a refresh operation in Power Query and Power Pivot, Excel goes through multiple stages of data processing. The messages "Reading Data... (Press ESC to cancel)" and "Waiting for other Data Model queries..." indicate different parts of this process.

1) What Happens During These Stages?

• "Reading Data..."
  This phase involves retrieving data from the source, whether it's an external file, database, or online storage like SharePoint. The time spent here depends on the size of the dataset, the complexity of transformations applied in Power Query, and network latency. If queries reference each other, they must be processed in sequence, which can add to the delay.

• "Waiting for other Data Model queries..."
  This message means that some queries are dependent on others and must wait for them to complete before proceeding. If multiple queries are refreshing simultaneously, Excel may be holding back some processes until prior ones finish. Dependencies between queries or tables in the data model can contribute to these waiting periods.

2) How to Reduce These Delays?

• Optimizing Power Query

  • Turn off background refresh for large queries to ensure they execute in a controlled order.
  • Review query dependencies to eliminate unnecessary links between tables that might be causing delays.
  • Minimize redundant transformations and load only the necessary columns instead of full datasets.

• Improving Power Pivot Performance

  • Instead of using calculated columns, try shifting calculations to Power Query or using DAX measures, which are typically more efficient.
  • If multiple relationships exist between your fact table and the calendar table, consider simplifying these connections to improve processing speed.
  • Review DAX formulas to ensure they are optimized and avoid performance-heavy functions like FILTER inside calculated columns.

• Excel and Source Data Considerations

  • If your data source is in SharePoint, delays might be caused by slow response times or network latency. Testing download speeds can help determine if this is a factor.
  • Refreshing queries individually instead of using Refresh All can help identify which part of the process is slowing down.

3) Tools to Identify Bottlenecks

• Power Query Diagnostics (found in the Power Query Editor) logs execution times for each step, helping pinpoint slow operations.
• Query Dependencies View provides a visual representation of relationships between queries, making it easier to identify unnecessary dependencies.
• DAX Studio and Power BI Performance Analyzer can help analyze the efficiency of your Power Pivot model and pinpoint slow measures or queries.

By investigating these areas, it is possible to reduce refresh time and improve the overall performance of the data model.

BBF

[anmattos]

Hello,

Sorry for reopening the thread. I downloaded DAX Studio but could not find a way to measure timings during a refresh operation. I can only perform time measurements for specific queries.

Is there a way to measure timings during a refresh operation using DAX Studio in order to evaluate bottlenecks during the process? If no, there is another tool for that that works with Excel?

Best regards,

[anmattos]

Hello,

Thank you very much for your comprehensive response.

It would be nice to learn how to measure performance in Power Pivot/Query.nUnfortunately I'm using Excel and Power Query does not offer Diagnostics in there.

I'll try DAX Studio, but I'll need to learn how it operates from scratch. I think it is time to expand my knowledge.

Thank you,