Power BI DirectQuery best practices

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, DAX, M, MDX, Power Query, Power Pivot and Excel

Here’s a recording of a session I did for the Manchester (UK) Power BI user group recently on best practices for DirectQuery mode in Power BI:

I’ve done it for a few other groups over the last six months but this is the latest and best version, I think.

I’ve worked with several customers using DirectQuery mode since I joined the CAT team and learned a lot along the way. Some of this knowledge has been written up by me (eg this post on Snowflake DirectQuery mode best practices), some of it by the people who work with the data sources Power BI runs on top of (see posts by Dany Hoter on the Azure Data Explorer blog, for example; there’s also going to be a lot of new material on DirectQuery for Databricks coming soon, with this post as a start). There’s a lot of detailed information in the docs too, for example here, here and here.

But remember folks: the most important piece of advice around DirectQuery is to not use it unless you’re really, really, really sure you have no other option. It’s possible to make it work well but it takes a lot more tuning and specialist skill than Import mode!

My name is Chris Webb, and I work on the Power BI CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel. View all posts by Chris Webb

4 thoughts on “Power BI DirectQuery Best Practices Video”

1. lbendlin
   May 7, 2023 at 6:27 pm

   I was always wondering – isn’t Import Mode Direct Query by another name? The difference seemingly is the data source location:

   in “Direct Query” mode it is the (assumedly on-prem) data source that (hopefully) supports the amount of custom queries and all the network traffic.

   “Import Mode” eventually results in a Power BI dataset in the Azure cloud that behaves the same as a Direct query source, but with (allegedly) guaranteed/stable performance and network throughput. The drawback seems to be the loss of immediacy.

   Reply
   1. Chris Webb
      May 8, 2023 at 10:32 am

      No, I would say Import mode and DQ are very different. A Power BI report on an Import mode dataset is like you going home and talking to your mother in your native language. Power BI on a DQ dataset is like you having a Teams call with someone in another country using machine translation: you can understand each other, and the machine translation gets better every day, but communication is still not as efficient as it could be.

      Loading...
      Reply
2. David Beavon
   May 7, 2023 at 10:06 pm

   Thank you very much for sharing this.

   >> But remember folks: the most important piece of advice around DirectQuery is to not use it unless you’re really, really, really sure you have no other option

   I did not realize this was your stance. I’ve had minimal use for DirectQuery thus far (outside of composite modeling aka “DirectQuery for PBI Data”). But had always heard bloggers say that DirectQuery was “essentially ROLAP done right”. I think there is a crossjoin blog which stated this back in November 2010…

   The primary reason I hoped to use DirectQuery is to compensate for the expensive cost of memory in PBI Premium P1. So far we have stayed under our 25 GB limit, but as more datasets are hosted on premium, I was expecting that we would need to start using some DirectQuery models for overflow purposes.

   Loading...
   Reply
   1. Chris Webb
      May 8, 2023 at 10:28 am

      “ROLAP done right” does sound like something I would say, but with experience I’m not more wary of DQ. I’m not as anti-DQ as Marco is for example and I *have* seen successful implementations on very large data volumes (especially with Azure Data Explorer which, BTW, is amazing technology) but I have also seen plenty of failures caused by people ignoring the best practices and by other limitations. We’re doing a lot of work to improve DQ and there are still more changes to come so hopefully in a year or so the situation will be a lot better.

      >>The primary reason I hoped to use DirectQuery is to compensate for the expensive cost of memory in PBI Premium P1.
      The interesting thing is that we have found that Import mode usually works out cheaper than DirectQuery mode for customers: yes, you need to pay for Premium but the overall cost works out to be less than paying for the equivalent resources on almost all cloud data warehouse platforms.

      Loading...
      Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.