When you should use DirectQuery and import for data storage? Best Practice

[Anonymous]

So, i heard that if you are using excel or small dataset then just use Import and if you connecting to company's SQL Server (where you don;'t want the visuals to limit to 1GB of data) then use DIrectQuerty.

Anything else someone can add to enhance my knowledge on this. Thank you

[Anonymous · Accepted solution]

Hi  @Anonymous ,

In the import mode, data is always stored on the disk. When querying or refreshing, the data must be fully loaded into the memory of the Power BI capacity, and the refresh data operation requires loading new data to replace the original data. Therefore, during a certain period of refresh, Power BI stores two fully loaded data sets. In this way, the import mode is not good at handling data sources with large data volume and high data refresh frequency. However, since the data is stored in memory, the import mode can obtain very fast query results and the completeness and editability of the import mode are high. You can use the full DAX formula and Power Query functions.

There are also notes on the import mode:

When refreshed, data is compressed and optimized and then stored to disk by the VertiPaq storage engine. When loaded from disk into memory, it's possible to see 10-times compression. So, it's reasonable to expect that 10 GB of source data can compress to about 1 GB in size. 

However, when the data volume is too large, using data reduction methods is of no avail. At this time, we can use the Direct Query mode, which does not store data in Power BI. When refreshing, it will directly connect to the data source through credentials to obtain data. To a certain extent, the Direct Query mode can query data with a large volume and frequent refreshes, and the data is real-time. However, when the database structure is complex, data refresh will become slower, and some Power BI Desktop functions are not supported in DirectQuery mode.

You can view the differences between import mode and Direct Query mode at the following link:
Semantic model modes in the Power BI service - Power BI | Microsoft Learn
Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn

DirectQuery in Power BI - Power BI | Microsoft Learn

Best Regards,

Liu Yang

[Anonymous]

Thank you for detailed reply.

[danextian]

Hi @Anonymous 

I'd use DQ if the data is too big and cannot be imported without passing the  1 G limit per dataset and the data needs to the be most recent one (query happens  as you interact with the visuals). This is a good read in choosing betweent he two: https://www.phdata.io/blog/import-vs-direct-query-power-bi/ 

[Anonymous]

Thank you for posting link to the article