Power BI has 3 storage modes for tables: Import, DirectQuery, and Dual . These can be combined in a Composite Model where different tables use different storage modes.
Whether you're dealing with a small departmental report or a massive enterprise data warehouse, understanding the nuances between Import, DirectQuery, and Dual modes—and how to combine them in Composite Models—is essential. Let's dive into the details that will transform how you architect your Power BI solutions.
Quick Comparison
| Feature | Import | DirectQuery | Dual |
|---|---|---|---|
| Data Location | In-memory (cached) | Source database | Import + DirectQuery |
| Performance | Excellent | Variable | Good |
| Data Freshness | Scheduled | Real-time | Mixed |
| Dataset Size | Limited by memory | Unlimited | Limited by memory |
| DAX Support | Full | Limited | Full |
Storage Modes Explained
| Category |
Import
DEFAULT
|
DirectQuery
REAL-TIME
|
Dual
HYBRID
|
|---|---|---|---|
| Description | Data loaded and compressed into Power BI's in-memory engine (VertiPaq). Most common and fastest mode. | Queries sent directly to source database in real-time. No data stored in Power BI. | Tables act as either Import or DirectQuery depending on context. Power BI auto-selects best option. |
| Benefits | |||
| Performance | ⚡ Blazing fast - all queries from memory | Variable - depends on database | ⚡ Fast when cached |
| Data Freshness | Scheduled refreshes only | ✓ (Near) Real-time, always current | ✓ Mixed (fresh + cached) |
| Dataset Size | Limited by memory (1-10 GB typical) | ✓ Unlimited - stays at source | Limited for cached tables |
| DAX Support | ✓ Full functionality | ⚠ Limited functions | ✓ Full functionality |
| Compression | ✓ Excellent (10:1 typical) | N/A - no storage | ✓ For cached tables |
| Offline Access | ✓ Works without connection | ✗ Requires connectivity | Partial |
| Data Source Support | ✓ All PBI sources (databases, files, feeds, web, dataflows, etc.) | ⚠ Only major relational databases, PBI datasets, Analysis Services | ✓ Same as Import |
| Power Query (M) Support | ✓ All transformations | ⚠ Limited - must translate to native SQL | ✓ All transformations |
| Bottlenecks & Limitations | |||
| Data Staleness | ⚠ Requires scheduled refreshes | ✓ Always fresh | Mixed |
| Memory Limits | ⚠ Large datasets need Premium | ✓ No limits | ⚠ For cached data |
| Query Speed | ✓ Sub-second responses | ⚠ Each query hits database | Variable |
| Database Load | ✓ No impact after refresh | ⚠ Can overwhelm source | ⚠ For DirectQuery tables |
| Complexity | ✓ Simple to understand | Moderate | ⚠ Harder to predict |
| Refresh Time | ⚠ Can take hours for large data | ✓ No refresh needed | For Import tables |
| Source System Impact | ✓ Impact only during refresh | ⚠ Every user query hits source (OLTP impact risk) | ⚠ For DirectQuery queries |
| Row-Level Security | Defined in Power BI model | ✓ Can leverage source database RLS (some sources) | Defined in Power BI model |
| When to Use | |||
| Ideal For |
• Dataset under 1-10 GB • Need fastest performance • Real-time not critical • Complex DAX needed • Standard reporting |
• Near real-time data critical • Extremely large datasets (TB) • Optimized database • Compliance requirements • Can't fit in memory |
• Using Composite models • Small dimension tables • Optimize DirectQuery • Span Import/DirectQuery • Mixed relationships |
| Example Scenario | |||
| Real-World Use | Sales Dashboard: 500 MB monthly sales data, nightly refresh. Fast interactive reports with complex year-over-year calculations. 24-hour-old data acceptable. | Live Inventory: 50 TB warehouse data in Azure SQL. Real-time stock levels. Database has optimized indexes and aggregation tables. | Composite Sales Model: Product/Customer dimensions in Dual (small, stable). Sales fact table in DirectQuery (large, real-time). Power BI uses cached dims when possible. |
🎯 Final Thoughts
The best storage mode isn't the one with the most features or the newest technology—it's the one that meets your specific business requirements while delivering the performance your users expect. Start simple with Import, optimize ruthlessly, and only add complexity when it solves a real problem.