Import and Direct Query Load question

[elcapitano]

Hi all, data modelling question here.

I have a client with a large dataset where the main fact table is quite large (~1gb). We want to try doing a reload where we Import load the fact table up to a certain date (e.g. up to the end of last month) and then we would import a version of that fact table as a Direct Query for the newest data. The idea would be to keep increasing the Import load infrequently (once a month) but the Direct Query occurs daily. We would then append the Direct Query fact table to the Import load fact table during this process.

If we did this on the same table but append the Direct Query to it daily, does that mean reduced refresh times? Or does the fact that the Import Load version is changed mean that we will not improve refresh times?

Wondering if anyone has had experience with that before we go down the rabbit hole.

[v-stephen-msft · Accepted solution]

Hi @elcapitano ,

With import connection mode 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.

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.

Import Data

Advantages

• Fastest Possible Connection
• Power BI Fully Functional
• Combining Data from different sources
• Full DAX expressions
• Full Power Query transformations

Disadvantages

• Power BI file size limitation (It is different for Premium)

DirectQuery

Advantages

• Large Scale data sources supported. No size limitation.
• Pre-Built models in some data sources can be used instantly

Disadvantages

• Very Limited Power Query functionality
• DAX very limited
• Cannot combine data from multiple sources
• Slower Connection type: Performance Tuning in the data source is MUST DO

If you want more details about import and DirectQuery, please kindely refer to

DirectQuery, Live Connection or Import Data? Tough Decision! - RADACAD.

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.

[amitchandak]

@elcapitano , 1 GB is not large. You should schedule incremental ETL and load it.

https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only
https://thinkaboutit.be/2020/02/how-do-i-implement-an-incremental-refresh-in-power-bi-free-or-pro/

The option you are taking is Hybrid Table

Refer video from Guyinacube -https://www.youtube.com/watch?v=HckuKYlx8kk