Valid case for not using DirectQuery?

[Anonymous]

Hi experts,

I see some people created massive datasets like:

After checking the content, I can see the dataset is just a    select * from a View in SQL SERVER.

Why don't they  just use DirectQuery??, so all the data doesnt have to be stored in a power bi dataset?

Is there a valid reason/case where you can't use DirectQuery? and are forced to store all of it in PowerBI?

[ibarrau]

Hi. The thing here is that import data is so much popular because you have your data "in memory" on a high speed columnar engine that runs fast. It's the most used connection. This connection is asyncronous. So the 1gb select * from view, would be run only X times a day depending on the configuration.

When you use direct query, each interaction of the user will run a query to the engine except the ones in cache. It's way more complex to make it work properly. There are docs about this: https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance

Unless you can do all the things on that doc, you might have a lousy performance or overload the source.

If you are concerned about the size of the models, there are practices for downsizing and improving imported data models that could help a lot reduce the model even though it's a select * from view. 

I hope that helps