Refresh Time Out

[wc123]

Hi guys,

I recently created a report and published to the powerbi service. I am connecting to only one SQL database but pulling in 8 seperate tables. There are no additional steps in power query, all the tables in the data warehouse are indexed and perform pretty fast in SQL server Management studio. The problem is that it times out everytime i try to refresh within the service. I would like to keep all these tables in one report but am thinking i may need to split the semntic model into two with 4 tables each. Let me know if you have experinced this issue or have any solutions.

[MasonMA · Accepted solution]

Hello @wc123 

Before you split the semantic model, i would recommend enabling Incremental Refresh to see if this can improve your refresh time. 

You can set up incremental refresh policies for each table and only load new/changed data during refreshes.

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

And although you've mentioned there's no additional steps in power query, I'd suggest double-checking to ensure all transformations can fold back to SQL and avoid steps that break folding (like certain merges or custom columns). You can do this by right-clicking each step in Power Query to check if "View Native Query" is enabled.

Hope this helps:) 

[v-pnaroju-msft]

Hi wc123,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi wc123,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi wc123,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @MasonMA for your response.

Hi wc123,

We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on my understanding, since the queries execute quickly in SSMS but the refresh operation times out in the Power BI Service, the primary cause is likely service side constraints such as dataset size, memory, refresh duration or gateway/network bottlenecks, rather than issues with SQL performance.

Please follow the steps below, which may help to resolve the issue:

1. Check the dataset size and service refresh limits. Power BI Pro refreshes are limited to two hours, while Premium/PPU supports larger datasets and longer refresh durations.
2. Review gateway and network performance. Ensure that the on-premises data gateway is updated to the latest version and that it has sufficient CPU and memory. Also, position the gateway close to the SQL Server to minimise latency.
3. Optimise the model structure by removing unused columns and reducing high-cardinality columns. For very large fact tables, consider using composite models like Import for smaller lookup tables and DirectQuery for large tables to reduce refresh overhead.
4. If you are on Premium, consider using table partitioning for finer control over refresh operations. Splitting the semantic model into two should be considered only as a last resort.

For further reference, please see the following links:
Data refresh in Power BI - Power BI | Microsoft Learn
Monitor and optimize on-premises data gateway performance | Microsoft Learn

We hope this information helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[MasonMA · Accepted solution]

Hello @wc123 

Before you split the semantic model, i would recommend enabling Incremental Refresh to see if this can improve your refresh time. 

You can set up incremental refresh policies for each table and only load new/changed data during refreshes.

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

And although you've mentioned there's no additional steps in power query, I'd suggest double-checking to ensure all transformations can fold back to SQL and avoid steps that break folding (like certain merges or custom columns). You can do this by right-clicking each step in Power Query to check if "View Native Query" is enabled.

Hope this helps:)