Forum Discussion

gehe_muc's avatar
gehe_muc
Frequent Visitor
4 years ago

RAM Problems -Excel Crashes even with a very small DataModel

Hi to all! I'm working with Win 64bit, Office/Excel 365 Desktop with 32bit. Using PQ, PPivot, DaxMeasures an Excel-Tables. I developed a small Data Model with 6-8 tables, some relationsships and th...
Need Help
gehe_muc's avatar
gehe_muc
Frequent Visitor
4 years ago

Hi Pete,

I have the FactTable and all the Dimension Tables with relationships in my model, I know this concept. 

Indeed it is the second example - I did 4 joins/merge with other information and expand and did filtering with that... 

Just now I'm trying to get rid of the concept and I lost already the merges with this concept:

 

I did some "lookups" with this Add.Column  ... each Filialen[VertriebsID]{List.PositionOf(Filialen[FilialID], [Filiale])} and some Filtering with list concept like ... each List.Contains(FEDAS[FEDAS],[FEDAS])

After loosing all my nestedJoin/merge I will test again what happend with Ram 🙂

Thanks!

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    gehe_muc ,

     

    Yep, this was exactly the kind of thing I was talking about.

    I assume that Power Query in Excel also supports query folding so, as long as your fact and reference tables both fully fold back to the server, you should see significant performance gains using these List methods.

    If you want to speed it up even further, you can use List.Buffer to buffer your reference lists which will, in the sort term, increase RAM usage, but will release it far quicker, therefore reducing RAM requirement overall.

     

    Pete

  • gehe_muc's avatar
    gehe_muc
    Frequent Visitor
    4 years ago

    Hi,

    I rebuild the query without any merge/join, only with Filtering with lists and "lookup"...  as mentionend before, unfortunately I was not able to refresh the table... this way needs even more ressources as before... 

    Any other ideas beside of avoiding merge/joins to find a solution?

     

    I understand that this refreshing & merging process needs RAM, but when the Data is loaded the Model is very small (1,5 MB), the fact tables has about 8500 Rows... Why do I have RAM problems AFTER, I mean beside of refreshing this....

    I have to look for other solutions... 😞

    • BA_Pete's avatar
      BA_Pete
      Super User
      4 years ago

      Hi gehe_muc ,

       

      My recommendation would be to work in Excel 64bit and increase your RAM to 16GB plus.

       

      Regarding the RAM vs data size issue, this is almost completely irrelevant. It's not just the data size that potentially goes into RAM, it's running Excel, Power Query, Mashup Evaluation Containers, CefSharp processes, SAS processes etc. etc.

       

      As I said before, I think you're really going to struggle to use Power Query in any anger in a 32bit Excel file.

       

      Pete