Dealing with a Data Source Dilemma
When to use Direct Query and when to use Import (and scheduled refresh)
Hey Power people,
We’re gonna start off this newsletter real nice and easy and talk about a basic Data Source dilemma. I recently solved some report loading issues on a large report (which was not originally built by me) with a simple data source change and making the data model more efficient.
In fact, most editions of this newsletter will deal with real world challenges in making Power BI easier for people who use it. I’m not talking about those of us who make Power BI reports… I’m talking about the regular folks who may interact with a Power BI report once a day to help them do their jobs.
People-friendly Power BI is all about putting our users first so they LOVE the reports we make for them.
This week (our first edition!), let’s start where our Power BI reports start – the data source for your report.
Specifically, let’s discuss if you should use Direct Query (where your report connects directly to your data source) or Import (where it imports a copy of your data source).
Using Direct Query has advantages – if your data source is constantly updating, your report will also constantly update. That’s awesome, right? Yes. However, having this awesomeness comes with a tradeoff. Having your Power BI report directly connected with your data means it has to interact and do calculations with it any time your users load your report, filter your report, basically do anything with your report.
This takes tiiiiiiime.
The more data your report is connecting directly with (even if you don’t use all the data in your report – a subject for another issue of this newsletter), the more it is going to slooooow down. It has to possibly connect and filter and calculate with tons of columns, and thousands (or millions) of rows of data.
Guess what? Your dashboard users will hate this. They. Will. Hate. It.
A data dashboard that takes 5, 6, 7 seconds (or longer!) to change and update EVERY time something is clicked? Each slicer? Each table row you want to expand?
They. Will. Hate. It, and YOU will hear about it.
What do you do, hotshot? What do you do?
Well, knowing what your users need is your secret weapon here. If your users don’t need up-to-the-second data in your report, you can use the Import option to connect to your data source, and then set up auto-refreshes throughout the day, where your report will check the data and bring in any updates (you can set these up for every hour between 9am-5pm, for instance).
This way, your users still see updated data, and they’ll know that it’ll update every hour (be sure and tell them!) and most importantly, their report will be FAST.
They will LOVE you.