SQL Server Direct Query

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.

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.

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 ..