cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trungnguyen0000
Helper IV
Helper IV

Can not refresh the report because of memory of the virtual machine

Hi guys,

 

I have the version power bi report server and my report is connected to a SQL SERVER database which is heberged in a virtual machine. The table in the sql server includes 2 millions lines with fiftieth columns and the report is about 250 Mb.

 

Recently, the report struggles to refresh the data : 

1) the planning of the refresh in the server failed because of memory not enough

2) I tried to refresh the report in Power BI Desktop, once it reached about 1 600 000 lines , it stagnates and resumes a little bit before stagnating again

3) I copied the PIBX in the virtual machine and launch the refresh and check the memory in task manager in the same time. I noticed that when the memory reachs over 90%, the refresh stagnates

 

So I concludes that the problem is du to the lack of memory for the virtual machine . What do you think ? Did you have this one already and do you have a solution ?

 

Thank you very much 🙂 

1 ACCEPTED SOLUTION

Hi, @trungnguyen0000 

It’s hard to find out why there is the error.

Here are some suggestions:

1.Try to use directquery mode instead of import mode if the dataset is too large.

2.Optimize the model to avoid complex queries.

3.Optimize those DAX formulas that have poor performance and may cause very high memory consumption

Related links:

Optimization guide for Power BI

Understanding memory used by Power BI

 

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
trungnguyen0000
Helper IV
Helper IV

Hi guys,

 

Thank you for your answers

I found the error. It's due to my code M which sort a column for 2 million lines . I removed it and it worked !

collinq
Super User
Super User

Hi @trungnguyen0000 ,

 

To answer the initial question - yes, having more RAM and more CPU are good things to allow things to process.  But, when you say it "stagnates" do you just mean that it takes a long time to refresh?  Or, are you saying that it stops?  if it stops, are you getting any error messages?  It also depends if you are "import" or are "Direct Query".  It might also depend on the other things that the SQL server is servicing simultaneously.  It might also be your network speed.  It could be your connection type (VPN, RDP, etc).  it could even be that you have inefficient queries that are causing things to slow down.  Does it eventually refresh?




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




Hi

Thank you for answering

by stagnate, I wanted to mean  that it takes a long time to refresh and I used Import mode.

I copied the the pibx into the virtual machine so  could not be my internet speed or VPN and and I had the same problem

 

What kind of queries do you talk about ?

It didn't refresh because it took so much time so I stop the refresh

 

Thank you

Hi, @trungnguyen0000 

It’s hard to find out why there is the error.

Here are some suggestions:

1.Try to use directquery mode instead of import mode if the dataset is too large.

2.Optimize the model to avoid complex queries.

3.Optimize those DAX formulas that have poor performance and may cause very high memory consumption

Related links:

Optimization guide for Power BI

Understanding memory used by Power BI

 

Best Regards,
Community Support Team _ Eason

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!