Memory allocation failure on dataset refresh

[Testeri]

Hi,

my dataset refresh at Power Bi Service takes from 45 minutes to 2 hours and sometimes, not always, I get error "Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries." On Power Bi Desktop refresh takes about 45 minutes. I have new powerful laptop so I think there are massive bottlenecks somewhere.

How can I find exactly what causes this way too long refresh? I know which table(s) refresh takes long time and I think some calculated columns might cause this but I can't be sure. I have used Dax Studio Vertipaq Analyzer and managed to reduce Total size in memory from 1,5GB to 800MB by removing columns and adjusting relationships. Still getting that error occasionally.

As an example I have two datetime columns and according to Vertipaq each takes 55 MB (1,7 million rows), is that normal?

Service refresh use Personal gateway on our on-prem server.

Thanks

[ggpbidev]

I have exactly the same problem. I have read all the suggestions on forum posts I have found but the suggested solutions were general everywhere and did not help finding the column or query step that I needed to delete.

For a beginning, it would be also useful to see whether it is in power query or in power bi that the memory consumption goes high. I have steps in power query that could potentially be memory-intensive (such as merges) and also calculated columns and tables in power bi.

If I do a refresh on power bi desktop and monitor memory usage in the task manager, I can see that memory usage is the highest right before the end of the refresh. Memory usage usually goes above 15 GB, which means that it could not fit the 10 GB that is allocated for shared capacity subscribtions in Power BI Service. It is weird because the size of the dataset that I am trying to refresh is 39 MB. 

I have tried to set up incremental refresh as well, but the first refresh that was supposed to create the partitions failed as well.

[TheCreepster1]

What's your data source? Have you tried opening in the .pbix and refreshing each individual query one at a time to identify where the issue is, or stepping through the Power Query steps? That's where I'd start.

[Testeri]

Thanks for your reply. I'm using SQL Server as datasource. Like I said I know what tables takes long time but I want to know what is it exactly in those tables. Can't remove columns for testing because they might affect other calculated columns in the table. Is it possible to track what happens when you refresh Individual table? After rows are loaded (image below) nothing happens for a long time (8 minutes). That gif just keeps rolling. Would be nice to know what happens in the bacground during that.