SSAS & Power BI vs memory failure

[Medmbchr]

Hi

I have an issue with a Power BI not refreshing data due to memory problems (I have 12 Gb of RAM). I tried allowing more cache to it, reduce simultaneous queries etc and didn't work.

When I opened Task Manager I noticed that it's not Power BI that consumes memory, but SQL Server Analysis Services (SSAS)? I guess because it's running SQL due to the fact that my Power BI is connected to a SQL Server DB.

My question is: how can I reduce memory allocation of SSAS so that Power BI won't crash during data refresh?

The thing is that, SSAS keeps running even if I close Power BI..

Thanks

[ibarrau]

Hi. Power Bi runs a SSAS instance because it uses the same technology for tabular models. You can't just reduce memory for that. It is inside PowerBi. 

It depends on the size of the data E.g. if it will crash or not. If you are reading 10 gb of data in a 12 gb of ram machine it will probably crash. You should also consider that even if you have 12gb, that doesn't mean you have them all for power bi, there might be other processes and services running on windows and taking more ram.

Do you have an idea of the size of the tables you are getting from Power Bi Desktop?

I hope that helps,

If you want to deep dive and understand how everything works behind PowerBi, you can watch this video: https://www.youtube.com/watch?v=kpAZD7AeRQw&list=PLU6II7MW-aiKE4pDpvMn5KWANKvf0Be9-&index=38

[Medmbchr]

Hi

I don't really have an idea about the size of the tables I have, all I can say is that the largest one contains about 60k of lines and about 30-40 columns, 35 tables in total.

How can I reduce memory allocation for SSAS?

[ibarrau]

Again you cannot do that. It's not a SSAS issue. It's that your ram can't fit all the data in the PowerBi tabular model.

35 tables can be a lot for a single model. Are you sure 65k is the max row count of all of those tables?

You can always work with reduction tips to downsize your data model like: https://blog.ladataweb.com.ar/post/614108758904389632/datamodeling-5-tips-de-reducción-de-tamaño-de

There are many videos and posts about downsizing model with good practices.

Just in case. When the model is too big for your machine, you can develop with a technique of parameters. It's about reducing the data and working with samples of data. Then you can ask for the whole data once published.

I hope that helps,

P/D You can try refreshing the powerbi desktop in a different machine with more ram or less process using your ram and check if that one works.

[Medmbchr]

Hi

Thanks for your reply

I have an idea, if I filter my whole data from Power Query (e.g. I take only 2022 data on all my tables for example), would that optimize my dataset somehow?

[v-mengzhu-msft · Accepted solution]

Hi @Medmbchr ,

Of course you can through this way to optimize your dataset.

Optimization guide for Power BI - Power BI | Microsoft Learn

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly