Dataset refresh too long (Sometimes)

[MikolajSikorsk]

Hi,

In the company we use Golden Dataset for reports (~20 reports connected).

Usually refresh takes around 1-1:20 hrs.

But sometimes (usually on Mondays) refresh progresses 4+ hrs.

I dont know why this happens, any idea ?

We using PPU license. 

[v-pnaroju-msft]

Hi MikolajSikorsk,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi MikolajSikorsk,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi MikolajSikorsk,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[sscott0203]

Have been seeing the same issue for the last 2 weeks or so and ussually around the same time everyday (10am).  Thin reports, the visuals just sit and spin.  Dataset is import and all ETL is up in dataflows.  We're also using a PPU license.  However, dataflows refresh in 1-2 minutes, datamodel in 2-4 minutes and model size is 28k in size.  

[sscott0203]

Normal refresh

[v-pnaroju-msft]

Hi MikolajSikorsk,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thank you, @Akash_Varuna, for your response.

Hi @MikolajSikorsk,

We appreciate your inquiry through the Microsoft Fabric Community Forum.Thank you for providing detailed information regarding the dataset refresh issue.

Since the issue occurs occasionally, particularly on Mondays, while all other factors remain consistent, it is likely that the problem is related to global shared resource contention. Although Power BI Premium Per User (PPU) licenses offer enhanced performance, they still depend on shared resources within the Power BI service, which may experience higher demand during peak times.

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

1. Navigate to the dataset settings in the Power BI Service, select Scheduled Refresh, and examine the refresh history to identify any patterns or errors during extended refresh periods.
2. Since the queries are consistent, consider using Query Diagnostics to ensure that no unexpected changes or inefficiencies have been introduced during the refresh process.
3. If consistent performance is critical for your organisation, upgrading to Power BI Premium Capacity may be beneficial, as it provides dedicated resources and eliminates the impact of shared resource contention.

Additionally, you may refer to the following link for further insights:
Query diagnostics - Power Query | Microsoft Learn

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[MikolajSikorsk]

High Load on Data Sources: We are using one data source (Snowflake), and the load is the same as on other days.

Gateway Bottlenecks: No Gateway (OAuth2 authentication).

Complex Queries: The queries are exactly the same every day.

Network Latency: Both Power BI and Snowflake are cloud services.

Overlapping Schedules: Again, on other days, it works correctly with the same amount of refreshing datasets.

As I said, the problem occurs from time to time in the same environment.

In my mind, it should work the same way every time.

Is it possible that the problem is on Microsoft's side, since we have a PPU license? (Global Shared Resources)

[andrewsommer · Accepted solution]

I am less concerned with the jump in refresh time from an hour + to 4 hours + than I am with the refresh even taking an hour +.  Even the largest and most complex semantic models I have come across rarely take longer than 20 minutes.  I had a client a few years ago in the Health Care sector that had a bloated 6GB model that was highly inefficient, and it never even took 40 minutes.

My first suggestion would be to look at your query design.  Avoid a lot of heavy transformations.  If you can set up your queries as dataflows and then ingest the data flows into your model so you are segregating the ETL from the model refresh and you can isolate inefficient queries. 

Check your report settings to make sure that parallel loading of tables is enabled. 

[MikolajSikorsk]

Sooo, im avioding Power Query transformations, because they are very slow in use. Everything is done on database level ( Snowflake ) 
But i will check this parallel loading. 

[Akash_Varuna]

Hi @MikolajSikorsk It may be because of many reasons such as 

• High Load on Data Sources: Databases might have more queries on Mondays.
• Gateway Bottlenecks: Resource contention on the on-premises gateway.
• Complex Queries: Heavy transformations or non-folding queries.
• Network Latency: Increased congestion affecting data transfer.
• Overlapping Schedules: Multiple refreshes running at the same time.
  For detailed performance check you could check for Gateway Performance and Query Performace follow below for these 

• Monitor Gateway Performance

  • Go to Manage Gateways in Power BI Service and check for performance issues or resource constraints.
  • Look for logs to identify bottlenecks or unusual delays.

• Check Query Performance

  • Use Query Diagnostics in Power Query to analyze how long individual steps take.
  • Look for long-running steps or queries, especially on Mondays.
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

[MikolajSikorsk]

Sorry, I have to say it - if I wanted to ask Chat GPT, I would ask Chat GPT...