Forum Discussion
Direct Query exceeds available resources
So if you were importing the data it would be fine. However in Direct Query everything is still being sent back to SQL server to process.
Have a look at the query plan that SQL Server uses when executing the join across the two sub queries (Display an Actual Execution Plan - SQL Server | Microsoft Docs). It might give you a clue.
There is some good advice in here: sql server - SQL Query performance that use subquery and joins - Stack Overflow
It's possible that adding some indexes to the base tables might help to assist the joins. Eg the individual queries are fast but in order to join them it's having to do full scans of the right hand table for every row of the left(certainly sounds like it).
An indexed view lets you take each query and save it as a view but also add indexes to assist in joining those views which will make it more explicit as to what you want SQL Server to do.Sorry if that's vague. Without seeing your setup it's a hard one to diagnose.
Hello bcdobbs ,
Thanks so much for your reply! You are spot on that the tables that make up my report are generated by SQL queries, consisting of several joins of tables in the native database.
I've read that particular article before I posted this here, but it looks like I didn't fully understand what they mean by "materializing" data transformations. In my view I was doing that by transforming the data in my native SQL queries, ensuring that the data was all ready when loaded to Power BI. Is that the way to go or is that not what they mean by that statement?
So if you were importing the data it would be fine. However in Direct Query everything is still being sent back to SQL server to process.
Have a look at the query plan that SQL Server uses when executing the join across the two sub queries (Display an Actual Execution Plan - SQL Server | Microsoft Docs). It might give you a clue.
There is some good advice in here: sql server - SQL Query performance that use subquery and joins - Stack Overflow
It's possible that adding some indexes to the base tables might help to assist the joins. Eg the individual queries are fast but in order to join them it's having to do full scans of the right hand table for every row of the left(certainly sounds like it).
An indexed view lets you take each query and save it as a view but also add indexes to assist in joining those views which will make it more explicit as to what you want SQL Server to do.
Sorry if that's vague. Without seeing your setup it's a hard one to diagnose.
Awesome advice! I really wasn't sure where to look (our Service capacity, DAX query or something else), but this has given me some great pointers to investigate.
Your reply is not in the least bit vague! I'm actually impressed, because I think that you understood my problem so well, even without too many details 🙂
I will look into your links tomorrow and let you know if I can make it work. Thanks a lot for now and I'll get back to you.
Great!
A few other "non SQL" things to consider:
- Do you really need Direct Query? What will the business do differently with live data compared to data that is n hours old? I know when I first started with Power BI direct query seemed like the obvious go to but now I'd avoid it as much as possible and go import every time (Vertipak is a thing of beauty)
- How static are your dimension tables? Could they be imported and have a mixed mode model?
- Could you make everything imported but make your fact table hybrid so it has data up to yesterday imported over night and only hits direct query for todays data?