Alternative to Direct Query WRT Resources Exceeded

[vikas2]

I have a Power BI dashboard that often fails to load many visuals with error saying "Resources Exceeded".

My report has many filters and is based on Direct Query with decent amount of data. 

I understand that the load is a bit too much for the service to handle and hence the issues.

I would like to understand what options can I use to render my report faster and without any visual error.

An answer could be use - Import Query. If so, how do I schedule import query on a periodic basis?

Any other option?

Thanks for you help.

[v-juanli-msft]

Hi @vikas2

All DirectQuery requests are sent to the source database, so the time required for a visual is dependent on how long that back-end source takes to respond with the results from the query (or queries).

the issue occurs when a visual has attempted to query too much data for the server to complete the result with the available resources. you may need to try filtering the visual to reduce the amount of data in the result currently.

please read through the article below to learn how to get best performance.

Power BI Performance Best Practices

What do the "chedule import query on a periodic basis" mean?

Would you like to scehdule refresh for the imported dataset?

If so, please read these links to have a test.

https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh

If the link can't help you, could you provide details so i can give more effecient methods.?

( like: which data source you connect to, via Power BI Desktop or connect with Power BI service, the data source is online or on-premises)

Best Regards

maggie

[vikas2]

Hi Maggie,

Thanks. I understand.

For now let me not discuss refresh schedule but focus on direct query only.

Let me put in some more details.

Data is sitting on a Azure SQL DB. I connect via SQL authentication using Direct Query. I am facing delays in both Power BI desktop and Power BI service. More in latter. Goal is to provide interactive report on Power BI service.

More details:

I have 10 direct query tables. Any given table/view does not have more than 100K entries.

On SQL client (SMSS) the queries render pretty fast. I am also not putting any DAX query in Power BI itself except for calculating average in one visual.

My Power BI report has 16 visuals with 4 slicer filters. The 10 tables are interacting with each other via one key. This key in some tables can have Many-Many, Many-1 or 1-1 relationship.

One thing best practice web page mentions is to limit visuals in the report. But I do want the interacting visuals.

I want to make sure that I am not missing some trivial or straightforward points that are causing performance loss. If my report load is indeed heavy then I will re-think my strategy. 

Thanks for your help.