Power BI Service refresh taking 4–5 hours, while Desktop takes 5–10 mins

VIKASRP You asked what you would check first, so I will answer that literally rather than add a fifth theory to the pile, because you have already spent real effort on two that did not pay.

First check, one minute, and Olufemi7 has you starting in the right place. What is worth adding is what to read once you are in Refresh history, because the total is not the useful number. Look at how a single attempt is broken down. Every refresh is split into two operations that run in sequence: Data, which loads data into the model, and Query Cache, which is the Premium query cache and dashboard tile refresh. They are reported separately. If your 4h19m is nearly all Data, the time is at the source or in transit and tayloramy has you pointed the right way. If a real slice of it is Query Cache, you are looking at something else entirely, and something that cannot happen in Desktop at all.

That second case deserves a serious look given your own history. The documentation is blunt about it: "Significant use of dashboard tiles or premium caching can increase refresh duration, since either can queue many queries after each refresh." Dashboards accumulate quietly. "It used to run in under 30 minutes a few months ago" and "a lot more tiles have been pinned since then" fit together without anything having gone wrong on the ClickHouse side, and it would explain why swapping between a cloud connection and the on-premises gateway changed nothing.

Second check, and this is the one that actually answers where the hours went. On the refresh detail page there is an Execution details column with a Show link, which gives you execution metrics for each refresh attempt. That data used to need Log Analytics or workspace monitoring to get at and it is now in the product. You can reach it from the semantic model refresh settings, from the semantic model details page, or from the Monitoring hub. That turns "four hours" into a shape you can read instead of a number you have to guess about.

Third, and only after those two. You are on PPU, so you have the XMLA endpoint, and it gives you two useful things. You can refresh one table at a time from SSMS or Tabular Editor and time each of them, which converts "16 tables take four hours" into a sorted list. And on the timeout specifically, the five hour cap is a service limit rather than a limit on your model: "On Premium, the maximum refresh duration is five hours, but using XMLA endpoint to refresh data can bypass the five-hour limit." That is not a fix, but it stops the run dying at hour five while you are still trying to measure it.

One thing I would rule out early, because your table count makes it likely. You have around 100 tables and only 14 to 16 that load. If a lot of those lookup and parameter queries reference a shared query rather than going to the source themselves, that shared query is not evaluated once. The docs are unusually direct that this is a common wrong assumption: "it's often assumed that Power Query retrieves the Query1 result, and that it's reused by referenced queries. This thinking is incorrect." Each referencing query re-runs the whole chain beneath it. The same page notes Power Query can cache but may still fetch from the source more than once, which is why an interactive Desktop session that has already pulled everything is not a fair comparison for a cold service refresh. Query Dependencies view will tell you in thirty seconds whether you have that shape, and if you do, the documented fix on PPU is to move the shared query into a dataflow.

Which of the two, Data or Query Cache, is eating the four hours?

Hi VIKASRP 

I have seen this happen in the past and what it could potentially be is purely a bandwidth issue from your gateway server into the Power BI service and that is why it is taking a significantly longer time to process. Another option to check is to use the link below from Phil Seamark to understand when the refresh is happening, which component is taking the longest amount of time to help you troubleshoot where to go and look to resolve the long issue. I have customers who have been importing millions of rows that can take a few minutes to load, so it is definitely possible to have a fast refresh. https://dax.tips/2023/12/05/visualize-power-bi-refresh-using-sempy/ https://dax.tips/2021/05/11/visualise-your-power-bi-refresh-in-real-time/