Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jeffmasse
Frequent Visitor

Refresh on Service takes longer than Desktop

I've recently made changes to a Power BI model that has significantly slowed down the refresh time. Before the changes discussed below, a typical refresh on both Desktop/Online would take approximately 20-30 minutes.

 

I introduced the following change:

- Sales table has approximately 3.5 million rows

- Cost lookup table has approximately 20,000 rows

- I performed a MERGE (left-outer) inside the Sales table to the Cost table. I merged on a field that is unique in the Cost table.

 

When I first implemted this change, the refresh time for both Desktop and Online was approximately the same (2+ hours or timing out).

 

I did some research to determine how to get the refresh time down. I was pointed to a blog by Chris Webb (link) which discussed adding a Primary Key to the lookup table (i.e., Cost in my scenario). This SIGNIFICANTLY improved the refresh time on Desktop - from 2 hours+ down to approximately 10 minutes. However, this performance improvement didn't seem to translate to Power BI Online. Refreshes are still timing out after 2+ hours.

 

A bit of information on my setup

- I'm connecting from my PC through VPN to the host server

- My desktop - I'm running i7-7700, 24GB RAM, SSD, 100MB fibre connection from my PC to the host DB. Running Power BI 64bit September update.

- Server - I'm not 100% what the specs are on that - could find out. Running July gateway (had updated to August, but it was causing some failures so reverted back to July).

 

Is there a difference in how Power BI Online processes a query refresh compared to Desktop?

7 REPLIES 7
v-anabat
Employee
Employee

even I have same issue with my report, it takes around only 1min when refresh the desktop file, but in workpsace it's taking around 20mins. Also, all the connections I'm using in the report are Azure. 

Please let me know if you have found any answer for this.

Hi,

 

I have a Power BI report which takes around 1min to complete the overall refresh when I refresh the desktop file.

But after publishing this file to workspace(premium), it is taking around 20mins to complete each refresh.

All the connections are connected to Azure sql datases/ Azure Cosmos database and I'm using import query to load the data.

why is it taking such longer time? Is there any way to find which connection is blocking the refresh?

 

Regards,

Ananth

Hi there

When you say your connections are in Azure, which connections are you talking about?

And did you have to setup the Gateway in order to refresh the data?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi Gibert,

Thanks for the reply.
I mean that all my data is already present in Azure Sql db/ Azure cosmos database.
So, we don't need to setup gateway to connect to these connections.

Regards,
Ananth

I would imagine it should be quick, unless the data is located in different regions?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User
Super User

Quick question, did you update the PBIX online?

And also the updated versions of the On-Premise Data Gateway includes the Mashup Engine which does all the Power Query work, so if your gateway is using the July Version but you are using Power BI Desktop Sept there is a mismatch which could be causing the slowness when going via the Gateway.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Sorry for the late reply - I needed to work with my IT Admin to get the latest Gateway installed. I've now got the September Gateway installed and still having the same refresh timing issues. 

 

Not sure what you mean by updating the PBIX online? I uploaded my PBIX file and immediately refreshed it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors