Should I use DirectQuery or Import Option?

[Anonymous]

Hello guys, I'm not pretty sure what to use in my Dashboard. I'm publishing it in the cloud and I just need that it can updates the data every day in the morning, I know the difference between DirectQuery and Import Option, but it's not clear for me how does them work in the cloud. I mean, the import option can update the data by itself? or Should change to DirecQuery to get it? I repeat, I just need my dashboard can upload every day in the morning.

[Anonymous · Accepted solution]

Hi @Anonymous ,

First of all, we have to confirm which connection modes your data source supports. There are three connection modes: import, DirectQuery and Live connection.

Import：

With this method data from the source will be loaded into Power BI. Loading in Power BI means consuming memory and disk space. As long as you are developing Power BI on your machine with Power BI Desktop, then it would be memory and disk space of your machine. When you publish the report into the website, then it will be memory and disk space of Power BI cloud machine.

If you have 1 Million rows in a source table, and you load it into Power BI with no filtering, you end up with having the same amount of data rows in Power BI. If you have a database with 1000 tables, however, you only load 10 of those tables in Power BI, then you get memory consumption for only those 10 tables. Bottom line is that you spent memory and disc space as much as you load data into Power BI.

DirectQuery:

DirectQuery is a direct connection to data source. Data will NOT be stored in Power BI model. Power BI will be a visualization layer, then query the data from data source every time. Power BI will only store metadata of tables (table names, column names, relationships…) but not the data. Power BI file size will be much smaller, and most probably you never hit the limitation of the size because there is no data stored in the model.

Live Connection:

Live Connection is very similar to DirectQuery in the way that it works with the data source. It will not store data in Power BI, and it will query data source every time. However, it is different from DirectQuery. Live Connection is only supported for these data sets;

SQL Server Analysis Services (SSAS) Tabular
SQL Server Analysis Services (SSAS) Multi-Dimensional
Power BI Service

If you want to know more about the advantages and disadvantages and differences between the three connection modes, you can refer to this article, which is introduced in detail.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Migasuke]

Hi @Anonymous ,

there are some advantages and dissaddvantages for both options. But in generall I would recommend you Import mode, especially if you dont work with a big data and generally your refresh does not require long time.

So basically you schedule your refresh with import once per day and you should be fine.

More info here:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery