cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
filip1150
Frequent Visitor

Memory error when saving/importing data from SQL Server

Hi All
Running Power BI Desktop 2.64.5285.741 32 bit, November 2018
I have a fact table stored in SQL Server, about 40 M rows and about 50 integer columns.
Compressed in SQL Server (using the columnstore clusterd index) the table takes about 2GB.
I created a new powerBI file and tried to import this table (using get data/import).
Computer has 16GB of RAM and only 6 GB are in use at the time of starting the import.
The import will succeed at which point the memory consumption on the system is about 8.5 GB (so another 7GB free)

However, trying to save the model fails with a memory error:
Memory error: Memory allocation failure: Not enough storage is available to process this command. Try simplifying or reducing....
If I change my query to only fetch 25M rows, the import will succeed and will create a 500MB pbix file.
Is there any limitation in terms of how big the data set can be in import mode, other than the physical memory on the box?

Thanks

9 REPLIES 9
vanessafvg
Super User
Super User

@filip1150  as far as i know no, when you running it when it reaches that 25 million mark, what does the memory say then?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!





@vanessafvg 

In both scenarios - importing 25M or importing 40M, at the end of the import process there is plenty of memory left available. The difference is when trying to save the file - it succeeds for 25M but for 40M it fails right away with the Memory allocation failure message

is this a once off?  @filip1150  to be honest i have no idea what the issue is, it obviously is having some sort of memory issues, i guess what you could try and do is split the load into 2 and then merge it?

 

also you say you fields are integer, do they come through as integer in the power bi?  is it only integers, power query is case sensitive and if you have strings that are coming through with different cases that can also take up more space, so you might want to standardize that.

 

what about direct query is that not an option for you?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@filip1150  also under your file / options and settings / options / data load

 

check your cache you might want to clear it

and go through these settings

https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781788290142/1/ch01lvl1se...

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

https://www.powerpivotblog.nl/what-is-eating-up-my-memory-powerpivot-excel-edition/





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




 

Thanks.

It is not a one-off, I tried it many times and it always happens. First time I try saving I get the memory error, if I dismiss the dialog and try saving again I get a "something went wrong".

All columns are integers and come as integers in powerbi.
I tried clearing the load cache and it did not help.

Direct query is not an option as I plan to work disconnected.

I will loading twice and merging

@filip1150  you might also want to enable the diagnostics which is also under options and settings, maybe that can help





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




 

From the trace file:

 

DataMashup.Trace Error: 24579 : {"Start":"2019-04-29T14:20:11.6292915Z","Action":"PBI.FileManager/TrySaveFile/TrySaveBIPackage/Retryable save
error Exception:System.AggregateException: One or more errors occurred. --->
System.ApplicationException: We weren't able to save database. ---> Microsoft.PowerBI.Client.Windows.AnalysisServices.XmlaExecutionException:
ExecuteXmla failed with result ---> Microsoft.AnalysisServices.OperationException:
Memory error: Memory Allocation failure : Not enough storage is available to process this command. .
Try simplifying or reducing the number of queries.\r\nThe current operation was cancelled because another operation in the transaction failed.\r\n\r\n ---
End of inner exception stack trace ---\r\n at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.ExecuteXmla(String xmla)\r\n at
Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.<>c__DisplayClass28.<ImageSaveDatabaseToPbix>b__27()\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.OnErrorClarify(Action action, String clarification)\r\n ---
End of inner exception stack trace ---\r\n at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.OnErrorClarify(Action action,
String clarification)\r\n at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.ImageSaveDatabaseToPbix(String databaseName, String filePath, Uri dataModelPath)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.<>c__DisplayClassd.<SaveDatabaseToPbix>b__b()\r\n
at Microsoft.PowerBI.Modeler.ServerUtilities.EnsureConnectionAndExecuteAction(Server server, Action action)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.SaveDatabaseToPbix(IAnalysisServicesDatabase database, String filePath, Uri dataModelPath)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesDatabaseLocal.<>c__DisplayClassb.<SaveToPbix>b__9()\r\n
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.<>c__DisplayClass15.<ExecuteSynchronizedWithModelChanges>b__14()\r\n
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.<>c__DisplayClass1d.<ExecuteSynchronizedWithModelChanges>b__1b(Task`1 t)\r\n
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()\r\n at System.Threading.Tasks.Task.Execute()\r\n --- End of inner exception stack trace ---\r\n
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)\r\n
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesDatabaseLocal.SanitizeDataModel(Action saveAction)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesDatabaseLocal.SaveToPbix(String filePath, Uri dataModelPath)\r\n
at Microsoft.PowerBI.Client.Windows.Services.PowerBIPackagingService.Save(Report report, FileStream fileStream, DataModelSaveMode dataModelSaveMode, Queries queries)\r\n
at Microsoft.PowerBI.Client.Windows.Services.FileManager.SaveToTempFile(Boolean forceModelDirty, Boolean forceStreamSave)\r\n
at Microsoft.PowerBI.Client.Windows.Services.FileManager.<TrySaveBIPackage>d__15.MoveNext()\r\n---> (Inner Exception #0) System.ApplicationException:
We weren't able to save database. ---> Microsoft.PowerBI.Client.Windows.AnalysisServices.XmlaExecutionException: ExecuteXmla failed with result --->
Microsoft.AnalysisServices.OperationException: Memory error: Memory Allocation failure : Not enough storage is available to process this command. .
Try simplifying or reducing the number of queries.\r\nThe current operation was cancelled because another operation in the transaction failed.\r\n\r\n ---
End of inner exception stack trace ---\r\n at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.ExecuteXmla(String xmla)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.<>c__DisplayClass28.<ImageSaveDatabaseToPbix>b__27()\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.OnErrorClarify(Action action, String clarification)\r\n --- End of inner exception stack trace ---\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.OnErrorClarify(Action action, String clarification)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.ImageSaveDatabaseToPbix(String databaseName, String filePath, Uri dataModelPath)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.<>c__DisplayClassd.<SaveDatabaseToPbix>b__b()\r\n
at Microsoft.PowerBI.Modeler.ServerUtilities.EnsureConnectionAndExecuteAction(Server server, Action action)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesService.SaveDatabaseToPbix(IAnalysisServicesDatabase database, String filePath, Uri dataModelPath)\r\n
at Microsoft.PowerBI.Client.Windows.AnalysisServices.AnalysisServicesDatabaseLocal.<>c__DisplayClassb.<SaveToPbix>b__9()\r\n
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.<>c__DisplayClass15.<ExecuteSynchronizedWithModelChanges>b__14()\r\n
at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.<>c__DisplayClass1d.<ExecuteSynchronizedWithModelChanges>b__1b(Task`1 t)\r\n
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()\r\n
at System.Threading.Tasks.Task.Execute()<---\r\n","ParentPath":"PBI.Program/Main","ProductVersion":"2.64.5285.741 (18.11)","ActivityId":"00000000-0000-0000-0000-000000000000",
"Process":"PBIDesktop","Pid":13364,"Tid":54,"Duration":"00:00:00.0000473"}

@filip1150  try increasing your cache?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors