Forum Discussion
Dataset refresh too long (Sometimes)
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.
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)
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.
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.