Power BI Import vs DirectQuery: Which to Choose (2026)
Import and DirectQuery are the two primary storage modes in Power BI—they determine where your data lives and how queries execute. Import loads data into Power BI's in-memory engine (fast queries, scheduled refresh). DirectQuery queries the source database live (always fresh, no data copy, slower performance). Choosing the wrong mode costs you: Import datasets hit refresh limits (10GB cap on Pro); DirectQuery reports load slowly (5–30 seconds per visual).
This guide covers Power BI Import vs DirectQuery are, how they differ, when to use each, performance trade-offs, Composite models (mixing both), aggregations, and migration strategies.
What Are Power BI Storage Modes?
Power BI supports three storage modes per table in a dataset:
Import: Data is copied into Power BI's in-memory columnar engine (Analysis Services). Queries run against the cached copy (sub-second response times). Requires scheduled refresh to stay current.
DirectQuery: No data is cached. Every visual query generates a live SQL query to the source database (Azure SQL, Synapse, Databricks, etc.). Data is always fresh, but query speed depends on source performance.
Dual: (Available in Composite models only.) Table behaves as Import when aggregated data exists, DirectQuery otherwise. Used for performance optimization with aggregations.
Key difference:
Import = snapshot (data copied at refresh time, fast queries, stale between refreshes)
DirectQuery = live wire (real-time data, slow queries, no refresh needed)
Example: Sales Dashboard (1M Rows)
Import mode:
Refresh schedule: Nightly at 2 AM
Query time: 200ms per visual (data in memory)
Freshness: Data is 0–24 hours old (depending on when user opens report)
Data size limit: 10GB (Pro), 100GB+ (Premium)
DirectQuery mode:
Refresh schedule: None (data always current)
Query time: 3–10 seconds per visual (live SQL query)
Freshness: Real-time (reflects source database state at query time)
Data size limit: None (Power BI doesn't store the data)
When to use each: Import for fast dashboards with acceptable staleness (daily refresh); DirectQuery for real-time operational reports querying massive datasets (billions of rows).
Import Mode (Scheduled Refresh)
How Import Mode Works
Data load: Power BI Desktop connects to source (SQL Server, Azure SQL, Excel, API), runs queries, and copies all rows into the .pbix file.
Compression: Data is stored in columnar format (VertiPaq engine)—10GB source typically compresses to 1–2GB in .pbix.
Publish: Upload .pbix to Power BI Service; dataset is now hosted in Power BI's cloud.
Scheduled refresh: Dataset refreshes on a schedule (hourly, daily)—Power BI re-runs source queries, replaces cached data.
Query execution: Visuals query the in-memory cache (no source database hit).
Import Mode Advantages
✅ Fast queries: Sub-second response times (data in RAM)
✅ Rich DAX support: Full DAX function library (Import supports features DirectQuery doesn't)
✅ Offline access: Once data is imported, source database downtime doesn't break the report
✅ Data transformations: Power Query M transformations (merge, pivot, custom columns) run once at refresh time
✅ Cross-source joins: Combine SQL Server + Excel + SharePoint in one model (DirectQuery can't join tables from multiple sources)
Import Mode Limitations
❌ Data freshness: Data is stale between refreshes (24-hour lag for daily refresh)
❌ Size limits: 10GB per dataset (Power BI Pro), 100GB+ (Premium)—large datasets require Premium or incremental refresh
❌ Refresh duration: 2-hour max refresh window (Pro), 5-hour (Premium)—datasets with 50M+ rows may time out
❌ Memory footprint: Power BI Service caches the dataset in memory (evicted after inactivity, reloads on next query)
For data warehouse consulting projects, Aptocoiner's certified engineers design Import mode architectures for 95% of dashboards (sub-second query performance, acceptable staleness for business reporting). We implement incremental refresh for datasets exceeding 10GB (e.g., 5-year transaction history)—load only last 90 days in full, archive older partitions.
DirectQuery Mode (Live Connection)
How DirectQuery Mode Works
No data load: Power BI Desktop connects to source (Azure SQL, Synapse, Databricks), but does not copy rows—only schema metadata is stored.
Publish: Upload .pbix to Power BI Service; dataset contains connection string and field list (no data).
Query execution: When a user opens the report, Power BI generates live SQL queries for each visual (e.g., SELECT SUM(Amount) FROM Sales WHERE Region = 'North') and sends them to the source database.
Result returned: Source executes the query, returns aggregated result, Power BI renders the visual.
Result: Every visual refresh = a new live query (data is always current, but query speed depends on source database performance).
DirectQuery Advantages
✅ Real-time data: No refresh lag (data reflects source state at query time)
✅ No size limit: Power BI doesn't store data—query 10 billion rows (if source handles it)
✅ No refresh schedule: Source is always fresh (no manual refresh trigger)
✅ Security enforced at source: Database-level RLS/permissions are honored (Power BI sends user identity to source via Single Sign-On)
✅ Lower Power BI capacity usage: No dataset storage cost (dataset is just a connection definition)
DirectQuery Limitations
❌ Slow queries: 3–30 seconds per visual (depends on source database performance, indexes, network latency)
❌ Limited DAX support: Features like calculated tables, many time-intelligence functions, and complex M transformations don't work
❌ Query folding required: Power Query transformations must translate to SQL (e.g., Table.AddColumn with native SQL function)—unsupported transforms cause errors
❌ Source dependency: If the source database is slow or down, reports break
❌ Single-source limitation: DirectQuery can only query one source per model (can't join SQL Server + Snowflake tables)—unless using Composite models
DirectQuery Performance Bottlenecks
Symptom: Report loads in 30+ seconds; visuals render one-by-one slowly.
Causes:
Missing indexes: Source queries scan 10M-row fact table (no index on OrderDate)
Complex DAX measures: Power BI generates a 500-line SQL query with 10 nested subqueries
Network latency: Source database is on-prem; gateway adds 200ms round-trip per query
Cross-visual dependencies: Slicers trigger cascading queries (selecting Region re-queries 10 other visuals)
Fixes:
✅ Index source tables: Add indexes on filter/join columns (OrderDate, CustomerID, Region)
✅ Simplify DAX: Avoid iterators (SUMX, FILTER) that generate row-by-row SQL
✅ Use aggregated views: Create SQL views with pre-aggregated data (SalesByRegionDaily)—query the view, not the 10M-row fact table
✅ Enable query reduction: Power BI Desktop → Options → Query reduction (reduces cascading queries)
For Power BI performance optimization projects, Aptocoiner typically reduces DirectQuery query times by 60–80% through source-side indexing, aggregated views, and query reduction—transforming 20-second reports into 3-second interactive dashboards.
Power BI Import vs DirectQuery: When to Use Each
Scenario | Import Mode | DirectQuery Mode |
Dashboard with daily refresh | ✅ Fast queries, acceptable staleness | ❌ Unnecessary (no real-time need) |
Real-time operational dashboard | ❌ Data lags behind (stale) | ✅ Always current |
Small dataset (< 1GB) | ✅ Fits easily in Power BI Pro | ❌ Slower than Import (no benefit) |
Large dataset (100GB+) | ❌ Exceeds Pro limit | ✅ No size limit (query in place) |
Complex DAX time intelligence | ✅ Full DAX support | ❌ Limited time-intelligence functions |
Source database has strict RLS | ❌ Power BI RLS separate from source | ✅ Honors source-level RLS via SSO |
Offline access required | ✅ Data cached (source downtime OK) | ❌ Reports break if source is down |
Multi-source model (SQL + Excel) | ✅ Import supports cross-source joins | ❌ DirectQuery = single source only |
General rule:
Default to Import (95% of use cases)—fast queries, full DAX support, acceptable staleness for most business reporting
Use DirectQuery when real-time data is critical and source performance is excellent (indexed, materialized views, fast network)
Composite Models (Mixing Import + DirectQuery)
Composite models let you mix storage modes within the same dataset—some tables Import, others DirectQuery. This unlocks two key patterns:
Pattern 1: Large Fact Table (DirectQuery) + Small Dimension Tables (Import)
Scenario: 10-billion-row Sales fact table (100GB), queried via DirectQuery. Customers dimension (50k rows, 5MB) imported.
Why:
Fact table too large for Import (exceeds 10GB Pro limit)
Dimension table is small, rarely changes (importing it speeds up queries for slicers/filters)
Setup:
Add Sales table → Set storage mode: DirectQuery
Add Customers table → Set storage mode: Import
Create relationship: Sales[CustomerID] → Customers[CustomerID]
Power BI queries Customers in-memory (fast slicer), sends CustomerID filter to DirectQuery Sales table
Result: Slicers load instantly (Import dimension), fact queries still live (DirectQuery).
Pattern 2: Aggregations (Dual Mode)
Scenario: 10-billion-row Sales table (DirectQuery). Pre-build an aggregated Import table with daily totals (365 rows/year).
Why: Aggregated queries (e.g., "total sales by month") hit the Import aggregation table (sub-second), detailed queries fall back to DirectQuery.
Setup:
Create Aggregation table in SQL:CREATE VIEW SalesDaily AS SELECT OrderDate, Region, SUM(Amount) AS TotalSales FROM Sales GROUP BY OrderDate, Region
Import the aggregation table into Power BI (Import mode)
Set Sales table to DirectQuery
Define aggregation mapping: SalesDaily aggregates Sales on OrderDate, Region
Set SalesDaily storage mode to Dual
How it works:
Query: "Total sales by month" → Power BI uses SalesDaily (Import, fast)
Query: "Show individual orders for customer X" → Power BI uses Sales (DirectQuery, detailed)
Aptocoiner's Power BI Composite model implementations deliver 80–90% query performance improvement for hybrid scenarios—fast dashboards querying 10+ billion rows with sub-second summary visuals, falling back to DirectQuery only for drill-through detail.
Storage Mode Comparison Table
Feature | Import | DirectQuery | Composite (Dual) |
Data location | Power BI cache (in-memory) | Source database | Mixed (Import + DirectQuery) |
Query speed | Sub-second (RAM) | 3–30 seconds (live SQL) | Sub-second (aggregations), slower (detail) |
Data freshness | Stale (refresh schedule) | Real-time | Mixed (aggregations stale, detail real-time) |
Size limit | 10GB (Pro), 100GB+ (Premium) | None (data not stored) | Import portion: 10GB (Pro) |
DAX support | Full | Limited (no calc tables, limited time-intel) | Full (Import portions) |
Cross-source joins | ✅ Yes | ❌ No (single source) | ✅ Yes (via Import tables) |
Refresh required | ✅ Yes (scheduled) | ❌ No | Import tables: Yes; DirectQuery: No |
Source dependency | ❌ No (cached) | ✅ Yes (breaks if source down) | Partial (DirectQuery portions dependent) |
Use case | Standard dashboards (fast, acceptable staleness) | Real-time dashboards (always current) | Large datasets with aggregations (fast summaries + detailed drill) |
How to Switch Storage Modes
From Import to DirectQuery
Warning: Switching Import → DirectQuery removes all data from the dataset. Test in a copy first.
Power BI Desktop → Model view
Select table (e.g., Sales)
Properties pane → Advanced → Storage mode: Change from Import to DirectQuery
Power BI warns: "This will remove data and may break DAX measures" → OK
Test report—some DAX measures may fail (e.g., calculated tables aren't supported in DirectQuery)
Publish
Common issues after switching:
Calculated tables error out (not supported in DirectQuery)—convert to SQL views
CALCULATE with complex filters generates slow SQL—simplify or pre-aggregate at source
From DirectQuery to Import
Model view → Table → Storage mode: Change to Import
Power BI loads data into memory (may take 5–30 minutes for large tables)
Set up scheduled refresh (DirectQuery doesn't need refresh; Import does)
Publish
Benefit: Instant query performance (3-second DirectQuery reports become 200ms Import reports).
Power BI Gateway and DirectQuery
On-prem DirectQuery sources (SQL Server, Oracle on your corporate network) require the Power BI Gateway:
Install gateway on a Windows Server in your network (same network as the database)
Add data source in gateway config (connection string, credentials)
Publish report to Power BI Service
Dataset settings → Gateway connection: Map dataset to the gateway data source
How it works:
User opens report in Power BI Service
Power BI sends query to gateway (cloud → on-prem tunnel)
Gateway executes SQL query against on-prem database
Results sent back to Power BI Service via gateway
Performance: Gateway adds 100–500ms latency per query (network round-trip)—for DirectQuery reports with 10 visuals, that's 1–5 seconds overhead.
For Power BI Gateway installations, Aptocoiner configures clustered gateways (2–4 nodes) for high availability and load balancing—reducing DirectQuery latency by 40–60% vs single-gateway deployments.
Frequently Asked Questions
Q: Can I use Import and DirectQuery tables together in the same report?
A: Yes, with Composite models. In Power BI Desktop, you can set different storage modes per table—e.g., Customers table in Import mode, Sales in DirectQuery. When you create a relationship between them (Customers → Sales), Power BI allows the mixed model. Queries that touch only Import tables run fast (in-memory); queries needing DirectQuery tables send live SQL to the source. This pattern is ideal for large fact tables (DirectQuery) + small dimension tables (Import). Without Composite models (e.g., older .pbix files), mixing modes isn't possible—each report must be fully Import or fully DirectQuery.
Q: What happens to scheduled refresh if I switch from Import to DirectQuery?
A: Scheduled refresh stops automatically. DirectQuery datasets don't store data, so there's nothing to refresh—Power BI disables the refresh schedule in dataset settings. If you later switch back to Import, you must manually re-enable the refresh schedule (Power BI Service → Dataset → Settings → Scheduled refresh → Turn on). Switching to DirectQuery also deletes all cached data from the dataset (irreversible without re-importing).
Q: Can DirectQuery query multiple databases (e.g., SQL Server + Snowflake) in one report?
A: Not natively with pure DirectQuery. A DirectQuery dataset can only connect to one source database—it's a single live connection. However, Composite models offer a workaround: set SQL Server tables to DirectQuery, import Snowflake data via Import mode, and join them in Power BI (the Snowflake portion is cached, SQL Server queries remain live). Alternatively, create a linked view (e.g., SQL Server Linked Server querying Snowflake), then DirectQuery the view—but performance depends on the linked-server network latency. For multi-source real-time reporting, Aptocoiner typically recommends a data warehouse integration layer (Azure Synapse or Fabric Lakehouse) where all sources land first—DirectQuery then queries the unified warehouse.
Choose the Right Power BI Storage Mode for Your Data
Whether you're designing a high-performance Import model with incremental refresh, implementing DirectQuery for real-time operational reporting, or architecting a Composite model with aggregations for billion-row datasets, Aptocoiner Analytics brings certified Power BI storage optimization expertise.
Our Microsoft-certified engineers deliver proven Power BI storage mode implementations for US and UK enterprises, with measurable outcomes: 60–80% query performance improvement through storage mode tuning, 10+ billion rows queried at sub-second response times via aggregations, and hybrid architectures balancing data freshness with interactive performance.
From Import vs DirectQuery architecture decisions to Composite model design to DirectQuery performance optimization, we architect data platforms that scale reliably and respond instantly.
Schedule a free discovery call to discuss your Power BI storage mode strategy.
Comments