Refresh Error - Queries are blocked because of the following error

[KrisKolady]

I'm running into an error when I try to refresh the data in Power BI desktop. The backend connection is to data in Snowflake. When I try to refresh, it gives me the error of:

Queries are blocked because of the following error ... and gives me a dimension table with some more info.

Interestingly if I cancel out, and try the refresh again it works on the third try. Any ideas on why this might be happening?

Thanks,

Kris K

[Ritaf1983 · Accepted solution]

Hi @KrisKolady ,

This issue might be caused by a few factors. Here are some potential reasons and steps to resolve the problem:

1. Query Dependencies in Power Query:
   If you're performing merge operations or combining multiple queries in Power Query, the order or complexity of these transformations can sometimes cause temporary blocks during refresh. Consider reviewing your Power Query steps to ensure the order of operations is optimized and efficient.
2. Connection Overlap or Contention:
   If you’re refreshing multiple tables from Snowflake simultaneously, there could be a communication bottleneck or contention for resources. Try staggering the refresh of these tables or verifying if there’s a specific table causing the issue.
3. Network or Authentication Issues:
   The error might indicate temporary network communication issues with Snowflake or a timeout during authentication. Ensure that your connection credentials and network configuration are stable.

Test in Power BI Service:
Publish the report to the Power BI Service and try refreshing it there. This helps determine if the issue is local to Power BI Desktop or something server-related. If the issue persists in the Service, you may want to raise a ticket with Snowflake

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[KrisKolady]

Thanks @ritaf1983  - Any tutorial on #2? I typically do a "Refresh All" in PowerBI desktop. You are correct that I'm merging between tables and there is dependency on how the tables are loaded. Or is that not best practise.

What I'm doing is filtering out records in the dimension table that are not used in the fact table by merging. This seems to reduce the model size considerably and improves performance. Is there a best practise around this.

Thanks,

KrisK

[Ritaf1983 · Accepted solution]

Hi @KrisKolady 

Disclaimer: I haven’t worked directly with Snowflake, but I assume it functions similarly to SQL and other databases. Based on my experience with similar scenarios, here’s what I would recommend:

1. Use Views in Snowflake:
   Instead of performing the filtering and merging in Power BI, I’d suggest creating Views in Snowflake. This ensures the data is pre-processed (e.g., filtered and joined) before it reaches Power BI, reducing the data load and improving performance.

2. Minimize Data Size Before Importing:

   • Apply filters in Snowflake to exclude unnecessary records early on. For example, only include records in the dimension table that are actually used in the fact table.
   • Avoid loading entire tables unless absolutely necessary. Focus on the relevant columns and rows.

3. Simplify Power Query Steps:

   • Remove unnecessary steps in Power Query. Each step in the transformation pipeline can slow down refreshes, especially if there are merges or calculated columns.
   • Disable the "Enable Load" option for intermediate steps or staging tables that are used only for transformations.

4. Optimize Merging Logic:

   • If you’re merging the dimension table with the fact table to filter out unused records, I’d recommend moving this logic to Snowflake. Merging large datasets in Power Query can cause significant performance issues during refreshes.
   • Alternatively, you could preprocess the data in Snowflake by creating a pre-filtered table or view specifically designed for this purpose.

5. Test with Smaller Subsets of Data:

   • If the refreshes still fail, try testing your model with smaller subsets of data to isolate the issue and ensure that the problem isn’t related to hardware or Power BI memory limits.

6. Evaluate Power BI Settings:

   • Ensure you’re not loading unnecessary columns. Reducing the width of your tables can have a big impact on performance.
   • Check the Power BI Desktop’s memory and performance settings to ensure your machine isn’t running out of resources during refreshes.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[KrisKolady]

Thanks Ritaf1983 for the details. Appreciate the time taken to provide the response in detail.

KrisK

[Ritaf1983 · Accepted solution]

Hi @KrisKolady ,

This issue might be caused by a few factors. Here are some potential reasons and steps to resolve the problem:

1. Query Dependencies in Power Query:
   If you're performing merge operations or combining multiple queries in Power Query, the order or complexity of these transformations can sometimes cause temporary blocks during refresh. Consider reviewing your Power Query steps to ensure the order of operations is optimized and efficient.
2. Connection Overlap or Contention:
   If you’re refreshing multiple tables from Snowflake simultaneously, there could be a communication bottleneck or contention for resources. Try staggering the refresh of these tables or verifying if there’s a specific table causing the issue.
3. Network or Authentication Issues:
   The error might indicate temporary network communication issues with Snowflake or a timeout during authentication. Ensure that your connection credentials and network configuration are stable.

Test in Power BI Service:
Publish the report to the Power BI Service and try refreshing it there. This helps determine if the issue is local to Power BI Desktop or something server-related. If the issue persists in the Service, you may want to raise a ticket with Snowflake

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.