Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sujit_Thakur
Solution Sage
Solution Sage

SQL Server Direct Query

Dear All ,
I am using Power BI direct query with SQL server with RDS instance on AWS .
But my visuals keep on going out of memory for even 3 months data which i approx 360000 rows .
I do accept that there are some measures which i have used . I hope someone can help me

Please help or atleast direct me to a person who can help me getting out of this trouble 
@amitchandak @nickyvv @edhans @GilbertQ 

regards
Thakur Sujit

2 ACCEPTED SOLUTIONS
v-yangliu-msft
Community Support
Community Support

Hi  @Sujit_Thakur  ,

 

For your question, there are the following reference methods:

1. You can refer to the blog.

https://thegeekpage.com/fixed-there-is-not-enough-memory-to-complete-this-operation/

Besides, you need to use the external tool Dax Studio to determine where the problem occurred.

Download and install Dax Studio, and view the content on the "All Queries" page of the query tracking by selecting the ALL Queries button
For the specific use of Dax Studio, you can check the following link:

https://community.powerbi.com/t5/Desktop/Insufficient-memory/m-p/1381881

 

2. Please try to uncheck "Allow data preview to download in nthe backgroup" and "Enable parallel loading of tables" and check if this issue persists or not.

v-yangliu-msft_0-1617092831914.png

3. You can view Power bi Premium Capacity Metrics to view the number of refreshes in a day's time period. If multiple high spikes in query wait times., you can scale-up the capacity. If it is multiple high spikes in refresh wait times, you can scale-up the capacity, or assign the content to a different capacity. It is also possible that the report page contains too many visual effects (interactive filtering may trigger at least one query for each visual effect) or the usage rate of concurrent reports is high. You can try redesign reports with fewer visuals per page.

 

About Power bi Premium Capacity Metrics and How capacities function, you can check the following link:

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-metrics-app

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-premium-monitor-capacity

There are many specific related issues and optimization methods, you can check this link to understand

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-capacity-optimize

 

 

Best Regards,

Liu Yang

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

View solution in original post

Sujit_Thakur
Solution Sage
Solution Sage

Dear all ,

Thank you For your responses .

@edhans  , I am pretty sure that direct query is what is need of the project and also because of the data limit which is growing every second  , as in import mode I'll be restricted to a limit .

 

And I wanted all of you to know that I have got a feasible solution.

The visuals were getting timed out due to delay of time taken by gateway.

 

So what I did is , simply created a AWS EC2  and I have deployed gateway on EC2 with t2 medium size .

Now the speed of data querying is very fast and hence visual don't time out .

 

And the question of visuals getting over load and exceeding the memory , I have changed report pages .

In which it doesn't ask for whole data . First it ask you specifically which data you want to see as per category of vehicles (as mine is automotive industry) .

And then it moves to a page where only filtered data is queried so less amount of rows are queried that too with a date slicer above which gives a free hand to reduced the day wise data quering.

 

 

Thanks for supporting!

 

I hope this helps someone else also ..

 

 

 

View solution in original post

4 REPLIES 4
Sujit_Thakur
Solution Sage
Solution Sage

Dear all ,

Thank you For your responses .

@edhans  , I am pretty sure that direct query is what is need of the project and also because of the data limit which is growing every second  , as in import mode I'll be restricted to a limit .

 

And I wanted all of you to know that I have got a feasible solution.

The visuals were getting timed out due to delay of time taken by gateway.

 

So what I did is , simply created a AWS EC2  and I have deployed gateway on EC2 with t2 medium size .

Now the speed of data querying is very fast and hence visual don't time out .

 

And the question of visuals getting over load and exceeding the memory , I have changed report pages .

In which it doesn't ask for whole data . First it ask you specifically which data you want to see as per category of vehicles (as mine is automotive industry) .

And then it moves to a page where only filtered data is queried so less amount of rows are queried that too with a date slicer above which gives a free hand to reduced the day wise data quering.

 

 

Thanks for supporting!

 

I hope this helps someone else also ..

 

 

 

v-yangliu-msft
Community Support
Community Support

Hi  @Sujit_Thakur  ,

 

For your question, there are the following reference methods:

1. You can refer to the blog.

https://thegeekpage.com/fixed-there-is-not-enough-memory-to-complete-this-operation/

Besides, you need to use the external tool Dax Studio to determine where the problem occurred.

Download and install Dax Studio, and view the content on the "All Queries" page of the query tracking by selecting the ALL Queries button
For the specific use of Dax Studio, you can check the following link:

https://community.powerbi.com/t5/Desktop/Insufficient-memory/m-p/1381881

 

2. Please try to uncheck "Allow data preview to download in nthe backgroup" and "Enable parallel loading of tables" and check if this issue persists or not.

v-yangliu-msft_0-1617092831914.png

3. You can view Power bi Premium Capacity Metrics to view the number of refreshes in a day's time period. If multiple high spikes in query wait times., you can scale-up the capacity. If it is multiple high spikes in refresh wait times, you can scale-up the capacity, or assign the content to a different capacity. It is also possible that the report page contains too many visual effects (interactive filtering may trigger at least one query for each visual effect) or the usage rate of concurrent reports is high. You can try redesign reports with fewer visuals per page.

 

About Power bi Premium Capacity Metrics and How capacities function, you can check the following link:

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-metrics-app

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-premium-monitor-capacity

There are many specific related issues and optimization methods, you can check this link to understand

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-capacity-optimize

 

 

Best Regards,

Liu Yang

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

edhans
Super User
Super User

I would re-examine this project in light of Direct Query. It isn't meant to be used for basic reporting. There are specific use cases for it. You will get better performance using Import with Incremental Refresh as a rule. I talked to someone at MS about this and they said "100% of the time I recommend Direct Query 0% of the time." 😂

I think that is a bit extreme, because it is appropriate maybe .5% of the time. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
vanessafvg
Super User
Super User

firstly you will need to understand where the issue is, have you tried to run the performance analyzer or use dax studio to understand what is happenign with your code?

 

this is a good check list ot start looking at where the issues might be

 

https://selfservicebi.co.uk/what-are-the-options-when-a-power-bi-report-is-slow-power-bi-performance...

 





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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.