SQL Server Direct Query

[Sujit_Thakur]

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

[v-yangliu-msft · Accepted solution]

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.

[Sujit_Thakur · Accepted solution]

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 · Accepted solution]

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.

[edhans]

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. 

[vanessafvg]

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