Direct Query or Import

[ahmedshalabyy12]

Hello,

i have large model that every fact table has more than 100m of rows 

is it better to be direct query or import  ? 

i have prem workspace , and my question is for the service when i publish the reports 

Thank you 

[Visharavana · Accepted solution]

Hi @ahmedshalabyy12 
Import Mode is better

• Since the VertiPaq engine is well optimized for speed and compression, import mode is faster because data is stored in memory.
• Enables more complex DAX calculations, relationships, and transformations.

Challenge:

• Memory usage: Because 100M+ rows can be substantial, your Power BI capacity (Premium or Pro) and RAM need to be sufficient to support it.
• Refreshes may need to be properly managed (e.g., incremental refresh).

[v-echaithra · Accepted solution]

Hii @ahmedshalabyy12 ,

Thank you for reaching out to Microsoft Community.

I understand that your query is about choosing between DirectQuery and Import mode when publishing reports to the Power BI Service. 

Since you mentioned that you’re using Premium capacity and working with very large fact tables, Import mode is strongly recommended unless you have a specific requirement for real-time data or compliance constraints, as your Power BI capacity is Premium and RAM need to be sufficient to support it, you get large model support up to 400 GB in memory with PPU and even more in F64/F128 capacities, so memory constraints are not usually a blocker.

If the data size is so huge that you can’t fit it into a maximum .pbix file size, then you should use use DirectQuery. Data stays in the source with DirectQuery, and your aggregations/calculations are done well before the modified results are returned to your report.

Import mode is faster because all data is loaded into memory (VertiPaq engine). Queries run in milliseconds even on large datasets.Import Data gives you access to all of Power BI’s features. Full Power Query transformations, DAX measurements, and visualizations are all available. DirectQuery will present you with a number of Powers Query alternatives.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

[Akash_Varuna · Accepted solution]

Hi @ahmedshalabyy12 Use Import mode for faster performance and interactive reporting if your Premium capacity has sufficient memory and your data can be refreshed periodically. Opt for DirectQuery if you need real-time or near-real-time data and your data source can handle high query loads. For very large datasets, consider aggregating data to reduce size in Import mode or using a hybrid model combining Import and DirectQuery. Choose based on your need for data freshness and report performance.

[RossEdwards · Accepted solution]

I want to add to this conversation. @Akash_Varuna's  respoonse was spot on, so i would advise following his advice.

I want to also point out that "Rows" are not the only thing to consider about your table sizes.  The number of columns you bring in and the datatypes you store will dramatically change how well your model holds up in the service.  The easiest thing to do is firstly only grab the exact columns your model/report needs. 

Next avoid keeping DateTime fields.  If possible, split those fields in to seperated Date and Time fields.  Like a calendar table, you can have a Time table that specifies the relevant time intervals in a 24 hour period.  If you use the right method of storing Date and Times, you will find they compress much better and this will help avoid your concern about what is held in the service.

[Akash_Varuna · Accepted solution]

Hi @ahmedshalabyy12 Use Import mode for faster performance and interactive reporting if your Premium capacity has sufficient memory and your data can be refreshed periodically. Opt for DirectQuery if you need real-time or near-real-time data and your data source can handle high query loads. For very large datasets, consider aggregating data to reduce size in Import mode or using a hybrid model combining Import and DirectQuery. Choose based on your need for data freshness and report performance.

[ahmedshalabyy12]

May you please explain what happen in the background 

[v-echaithra · Accepted solution]

Hii @ahmedshalabyy12 ,

Thank you for reaching out to Microsoft Community.

I understand that your query is about choosing between DirectQuery and Import mode when publishing reports to the Power BI Service. 

Since you mentioned that you’re using Premium capacity and working with very large fact tables, Import mode is strongly recommended unless you have a specific requirement for real-time data or compliance constraints, as your Power BI capacity is Premium and RAM need to be sufficient to support it, you get large model support up to 400 GB in memory with PPU and even more in F64/F128 capacities, so memory constraints are not usually a blocker.

If the data size is so huge that you can’t fit it into a maximum .pbix file size, then you should use use DirectQuery. Data stays in the source with DirectQuery, and your aggregations/calculations are done well before the modified results are returned to your report.

Import mode is faster because all data is loaded into memory (VertiPaq engine). Queries run in milliseconds even on large datasets.Import Data gives you access to all of Power BI’s features. Full Power Query transformations, DAX measurements, and visualizations are all available. DirectQuery will present you with a number of Powers Query alternatives.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

[Visharavana · Accepted solution]

Hi @ahmedshalabyy12 
Import Mode is better

• Since the VertiPaq engine is well optimized for speed and compression, import mode is faster because data is stored in memory.
• Enables more complex DAX calculations, relationships, and transformations.

Challenge:

• Memory usage: Because 100M+ rows can be substantial, your Power BI capacity (Premium or Pro) and RAM need to be sufficient to support it.
• Refreshes may need to be properly managed (e.g., incremental refresh).

[ahmedshalabyy12]

how i can check the memory of my capacity while working or while refreshing the data 

[ahmedshalabyy12]

Thank you for the reply visharvana 
i have prem workspace , my question is for the service when i publish the reports