Forum Discussion

jeffmasse's avatar
jeffmasse
Frequent Visitor
7 years ago

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

  • 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.
    • jeffmasse's avatar
      jeffmasse
      Frequent Visitor

      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's avatar
    v-anabat
    Microsoft 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.

    • v-anabat's avatar
      v-anabat
      Microsoft Employee

      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

    • GilbertQ's avatar
      GilbertQ
      Super User
      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?
      • v-anabat's avatar
        v-anabat
        Microsoft Employee
        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