Data Refresh takes a very long time

[GuySev]

Hello all,

My apologies if this subject has been already on this board, I searched for a solution for a while and couldn't find the right answer.

I've built a rather complex model that combines several data sources: SQL server, MySQL, Google big-query and one excel file. In order to create relationship between the tables, I've created several merges and appends. unfortunately these couldn't be avoided.

In general, my model contains a reasonable amount of rows, approx. 15 million. I've gone through this article and applied every step that I could within the limits of my model to increase performance.

During refresh, the row count on the refresh window reaches the maximum rows on each table pretty fast, but then gets stuck on that display for a very long time, about one-two hours.

I also went through all the steps applied on the query view to try and figure out what causes this, with no luck.

Does any one have a clue what can cause this and how I can increase performance?

Any help would be much appreciated,

Guy.

[Rajeshkumaryarl]

Hi. I have a similar issue. We consumed data from SAP HANA database where in we created dataflows in Power BI Service. The dataflows we created are refreshing in very lesser time. We consumed the same dataflows in to power BI Desktop and when we move this dataset to service, it is consuming almost 3.5 hours in service. We also enabled incremental refresh in Service on dataflow. So not sure why our dataset is consuming a lot fo time for refresh. Any help could be greatly appeciated. 

[cpofri]

Old thread but hope this helps someone else.

When importing data from SQL, in Data Source settings, SQL Server Database connection info, you need to specify the port and not just the SQL server name.  Not doing this will slow down the data import by quite a bit

ex: SQLSERVER1, 12345

[v-lili6-msft]

hi, @GuySev 

For your dataset is so big, the performance will involve many aspects, you could refer to below documents and blogs that may help you:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

https://www.sqlbi.com/topics/optimization/

Best Regards,

Lin

[GuySev]

Hi @v-lili6-msft ,

Thank you for your reply!

As I stated, I already went through these performance articles and implemented quit a few techniques.

Quit frankly, this model is not that big at all. I have sum models that are twice and even three times bigger, but preform much better.

I'm trying to understand if a step I made on query view is causing this, or rather a calculated measure maybe.

Is there any way to measure what causes this slow down?

[Anonymous · Accepted solution]

When you do joins/appends in Power Query it has to load the entire table into virtual memory. This causes a lot of paging and is very slow.

Does your dataset have to be refreshed on an approximatly hourly basis or is a daily cadence ok?

As this will greatly affect what options are open to you to increase performance.

[GuySev]

Hi @Anonymous ,

Thank you for this clarification, eventually I kind of figured out this is the case as well.

I changed my data model in order to avoid some of the appends and indeed performance increased substantially.

Thanks again!

[WynandPblm]

@GuySevHi

I ran in a similar problem, my power queries running up to 4 hours. I'm using Access to do my joins, the same joins take me 3 seconds now. I found Access to be an excellent tool for extracting from multiple sources, doing your data modeling and specific views, then pushing it out to power Bi. Access also enforces accuracy in your data and it is not difficult to learn. It follows the same data modeling rules as Data modeling in Power BI. Just more strict as it demands parents to child ect aka data referral integrity.

It also acts as a centralization point for your data to maintain and build dimensions etc.

Regards

Wynand