Slow refresh time in Power BI service

[VL22]

Hi,

I have a 46MB pbix and the data source is from SSAS, import mode. The problem is when I using Desktop to refresh, it only took about 15mins to be refreshed, but it will take about 1 hour and 25 mins to be refreshed in Power BI Service. The pbix doesn't contain any report, any measure any calculated column. it's pure import data as it's made to troubleshoot the refresh issue. (our original file is about 137MB and will timeout when refreshes, so I tried to narrow down the scale and find the possible issue or bottleneck.)

I have another pbix which is 876MB, it's imported from SQL server. both the SQL server and SSAS are the same server. It took only 28 mins to be refreshed in Power BI Service, which is much faster than the SSAS one. our Gateway is using the latest versio, and 8 vCores, 8 GB of memory, the CPU is alwasy under 15% and memory is about 50% when refreshing. 

Does anyone have any ideas what the difference is when refresh from Desktop and refresh from Power BI Service? 

Based on my tests, it might not be the gateway's issue as the SQL pbix (876MB) is much bigger than the SSAS pbix (46MB). and the situation didn't get any better even if I added more resource to gateway.  

I used SQL Profiler to catch the query, and found out that there were many crossjoin because the data source is a cube and there are many dimentions. However, if the data source is the bottleneck, I guess it will also be very slow in Desktop? 

Any idea is appreciated. Thank you so much!

[VL22 · Accepted solution]

Some update for this issue.

Our main data source is from SSAS with import mode, and we will refresh the data via Enterprise Gateway. We made the following changes and the refresh time was from over 2 hours, which would time out and never got success, to 20 mins by scheduled refresh or 8 mins by manully refresh.

1. upgraded the SSAS server to 4 vcore and 16GM of RAM and use D-series in Azure.

2. moved gateway from prviate cloud to Azure. 

Based on the official document, import mode will require more RAM than CPU. However, in our case, the gateway memory always uses around  2~2.5 GB and the CPU is around 80%(4 vcores). 

[v-xiaosun-msft]

Hi @VL22 ,

According to your description, here is my suggestion.

Your data source is from SSAS and use import mode, which may cause the slow connection. If the source data changes frequently or is large, and the increased load in the source and the impact on query performance are acceptable, consider using DirectQuery/LiveConnect mode instead of import mode. Please change the connection mode, and try again to see if it is still to slow.

Please refer to the following document.

Data refresh in Power BI - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VL22]

Hi @v-xiaosun-msft ,

Thank you for your reply. Regarding the frequency of data changes in SSAS, this SSAS only process one time per day and I always refreshed the dataset after the process.

Based on the SQL Profiler's data, for both of the refreshes, I can see the same DAX query was sent to SSAS. If all the query is handling in SSAS, do you have any idea why refreshing in Desktop was much faster than refreshing in Power BI Service? Thank you!

Best regards,

VL