cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeffmasse Frequent Visitor
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
Super User
Super User

Re: Refresh on Service takes longer than Desktop

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 Datanaut!"
Power BI Blog
jeffmasse Frequent Visitor
Frequent Visitor

Re: Refresh on Service takes longer than Desktop

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.

v-anabat Regular Visitor
Regular Visitor

Refresh on Service takes longer than Desktop

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

v-anabat Regular Visitor
Regular Visitor

Re: Refresh on Service takes longer than Desktop

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.

Super User
Super User

Re: Refresh on Service takes longer than Desktop

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 Datanaut!"
Power BI Blog
v-anabat Regular Visitor
Regular Visitor

Re: Refresh on Service takes longer than Desktop

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
Super User
Super User

Re: Refresh on Service takes longer than Desktop

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 Datanaut!"
Power BI Blog

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 47 members 887 guests
Please welcome our newest community members: