Forum Discussion
There's Not Enough Memory To Complete This Operation
Hi All,
I am loading the data from SQL server. I am trying to refresh the data for my tables. But the data is not able to refresh and the below is message is reported:
The operation could not be completed as you do not enough memory. Please try again after you have enough memory. Please some body can help. FYI I have 8 gb of Ram.
32 Replies
I ran into the same problem, and I have 8gb of RAM as well.
Once I figured out which of the tables is causing the problem, I uploaded the model without that specific table. Then, I opened the query editor and uploaded just that table there.
Once you upload a table in the query editor, it allows you to edit the data before uploading it to the data model. I filtered only the data I needed (in my case it was a log table, so I filtered only the past three years) and only then clicked "apply".
In my case it worked great.
Here are some of my memory tips for powerbi.
Power BI is definately a memory hog. I use it with a 16Gb machine. 8gb works for most of our users. Restart if you get problems and make sure your memory has been release which can take a while. Close other memory hogs like browsers.
Obviously removed unneeded columns.
Aggregate in SQL or M to a lower grain before you load if you don't need all the detail.
Filter out rows you don't need again in SQL or M. Maybe only bring the last x days/months data.
Aim for a star schema if possible with id keys joins. Try adding a date dimension with your reporting month and join to your dates.
Turn off the Time Intelligence Auto Date/Time option if you don't need it as create date dimension in the background for every date field from the smallest to largest dates.
Also reduce the precision of times if you dont need them to be highly accurate.
Ideally load date and time seperatly both as integers e.g. mins after midnight.
I read here that ordering the data can help the compression if the data is very large. > 1million rows.
https://powerpivotpro.com/2016/02/sort-data-load-improved-compression/
here is some details of looking at what is using storage in your model.
https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/
If you still too big then you may need to use either SSAS tabular on a server or try Azure Analysis Services (which can import your powerbi model)
https://azure.microsoft.com/en-gb/services/analysis-services/
How big is the final powerbi doc? I have a couple of docs that are 0.5Gb.
Phil
Hi, I ran into the same problem when only refreshing 67MB data. Do you have any idea why it failed?
Thanks to this tip, I could solve the problem I have been dealing with since the last weekend. Many thanks for taking the time to leave it here almost 5 years ago !
We are facing the same issue and restarting the gateway machine will resolve the issue. I think there is some memory leakage issue at power bi side (gateway)
Thanks
Naimish
Do you have 64 bit Power BI? If not, you should get it.
How many columns in your tables you are loading? If you have more than 15 in a large table, then try removing columns you don’t need
I have already installed 64 bit Power BI Desktop and the number of rows are obviously more than 15 as there too many tables.
Kumar11109 wrote:the number of rows are obviously more than 15 as there too many tables.
It is not the rows that matter, it is the columns. My question was how many columns do you have. I don't know what "too many tables" means
Kumar11109, try to do step by step. one refresh per click. i know that's not the best practices but if you continuing with same problem it can help you provisory.
individual refreshing works fine, but who will do that every month with 50 data sources = 50 clicks :-) ?
Maybe your better to consoldate the data into a single data source first using SSIS.
or if that doesn't work try using SSAS tabular, this should then allow using SSIS to build each data set. You can then point your powerbi at this.
If you've not got any servers that you can use to run ssis/ssas on then another option would be to extract the data to files. SSIS or R could do this for you running from a pc. Then mash the files together in powerbi.
Kumar11109, try to do step by step. one refresh per click. i know that's not the best practices but if you continuing with same problem it can help you provisory.
