Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.