Forum Discussion
RAM Problems -Excel Crashes even with a very small DataModel
Hi Pete,
thanks for your response!
I'm using 8 GB RAM on a Virtual Windows machine (I Know I can use only 2GB for Excel 32Bit). I'm loading about 150-200K rows from SQL, after that I transfer and merge 3-4 times to some Dimension Tables, after I aggregate everything and there are remaining less then 10K rows and about 15 columns. The DataModel Table shows 8.500 rows loaded. All the other dimension tables are very small and have only a few columns.
I noticed the following behavior: Even when I refresh only one small table in the DataModel (refreshing from an Excel table inside a file, 1 column, 1 row - single value, takes 1-2 sec) the RAM goes up from 143 MB to nearly 1 GB?? Even though I don't refresh the data from the SQL-Server....BeforeRefreshAfterRefresh
What happens at this time? There ist no PQ action, no transformation at this time....
Any further ideas?
Could it be a solution to load the Data to Excel in the workbook, not storage the Data in the PowerPivot Kompressed Ram? If the file is going up to some MB doesn't mind if the solution is stable after...
Thanks!
After some minutes doing nothing at the DataModel or PQ Excel stops working:
Hi gehe_muc ,
You're getting into the programming architecture of how Power Query technically works, which is way beyond my skillset I'm afraid.
However, I notice that you mentioned that you "transfer and merge 3-4 times". As I said before, merges are very expensive and should be avoided, especially if you're limited to 32bit RAM limitations. There are a number of alternatives/mitigations to merge costs which I'm happy to explore with you if required.
Pete
Hi Pete,
thanks for trying to solve my issue! I'm german so I'm not sure what you mean bei "merge". In the Main-Query I do 4 "Joins" and adding columns and filtering after. Maybe I have to try another kind of transformation...
I'm curious about alternatives to solve my problems, thank you for sharing!
Steve
Hi gehe_muc ,
'Merges' ar what 'joins' are called in Power Query, and they're very intensive on system resources. Four merges/joins on one table could quite easily cause you major issues.
The primary way to avoid merges is to recreate them in the data model using relationships instead. Where you might do a left outer merge on your fact table to bring in item description from a dimension table, you would recreate this using a relationship like this:
factTable[itemID] MANY : ONE dimensionTable[itemID]. You would then use the dimensionTable[itemDescription] in any visuals where you want to display it.
If you're using merges to perform table filtering operations, there are other ways around this using list references and buffering, but I suspect it's my first example that you're actually using the merges for.
Pete
