Data Connectivity & Storage Modes: Import vs DirectQuery vs Dual

This lesson on Data Connectivity & Storage Modes: Import vs DirectQuery vs Dual is hands-on and example-driven. You can evaluate, select, and combine Power BI storage modes—Import, DirectQuery, Live Connection, and Composite Models—to optimize report responsiveness, manage dataset size constraints, and connect to enterprise architectures.

What You'll Be Able To Do

• Select the optimal storage mode based on dataset volume, refresh latency, and source capabilities.
• Configure DirectQuery connections to relational databases while mitigating query generation overhead.
• Connect to existing Power BI Datasets using Live Connection and identify interface constraints.
• Convert Live Connections into local DirectQuery models to incorporate additional data sources.
• Construct Composite Models pairing imported aggregation tables with DirectQuery detail tables.

Detailed Concept Walkthrough

1. Import Mode and In-Memory Storage

Import mode loads an exact snapshot of your source data directly into Power BI's high-speed in-memory engine. It provides the fastest analytical performance across all DAX and Power Query operations at the cost of requiring scheduled refreshes.

• Mechanism: Data extracted during scheduled refreshes is ingested, compressed, and stored locally inside the VertiPaq columnar engine. Every visual query evaluates entirely against local RAM rather than hitting the source database.
• Under the Hood: VertiPaq uses dictionary encoding, run-length encoding, and bit-packing to achieve extreme compression ratios. Dataset sizes are limited to 1 GB per dataset on standard shared capacity and 10 GB+ on Power BI Premium.
• Best Practice: Build your data model using a clean star schema with dedicated fact and dimension tables. Removing high-cardinality columns and unnecessary precision significantly reduces memory footprint and refresh duration.

// M Query connecting to SQL Server in Import Mode
let
    Source = Sql.Database("sql-server-prod.database.windows.net", "EnterpriseDW", [
        CreateNavigationProperties = false
    ]),
    dbo_FactSales = Source{[Schema="dbo",Item="FactSales"]}[Data]
in
    dbo_FactSales

Key Takeaway: Import mode provides maximum DAX capability and query performance by caching compressed data in memory.

2. DirectQuery Mode Architecture

DirectQuery stores only table schemas and metadata in Power BI Desktop, leaving all underlying data in the source system. It is designed for massive data volumes that exceed memory limits and scenarios requiring near real-time source querying.

• Mechanism: When a user interacts with a report visual or slicer, Power BI translates the DAX calculation into native source queries (e.g., T-SQL) and executes them on the database. Only the aggregated result set is returned across the network.
• Under the Hood: Every visual on the canvas generates one or more concurrent queries sent straight to the underlying source engine. Source systems without indexed star schemas or sufficient compute suffer high query latency and connection exhaustion.
• Nuance & Constraints: DirectQuery disables several complex Power Query transformations and limits DAX modeling capabilities. Certain time intelligence functions are restricted because their internal mechanics cannot be efficiently translated into native database queries.

// M Query connecting to SQL Server using DirectQuery
let
    Source = Sql.Database("sql-server-prod.database.windows.net", "EnterpriseDW", [
        // Omitting custom SQL enables query folding in DirectQuery
        CreateNavigationProperties = false
    ]),
    dbo_FactWebEvents = Source{[Schema="dbo",Item="FactWebEvents"]}[Data]
in
    dbo_FactWebEvents

Key Takeaway: DirectQuery leaves data in place to support massive scale, but requires highly optimized source engines and restricts complex DAX.

3. Live Connection Mechanics

Live Connection links a Power BI report directly to an externally hosted VertiPaq model, such as SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), or an existing Power BI Dataset. It centralizes semantic modeling while allowing thin visual reporting layers.

• Mechanism: The local report file stores only connection strings and visual definitions without any local tables, calculated columns, or Power Query logic. Queries generated by user interactions pass directly to the external Analysis Services instance.
• Under the Hood: Because data modeling and transformations occur on the upstream server, Power BI Desktop disables both the Data View and the Power Query editor tab. Report authors are restricted to creating report-level measures.
• Best Practice: Use Live Connection to enforce a single source of truth across enterprise teams by separating core dataset governance from front-end report development.

// DAX Report-Level Measure defined over a Live Connection
Internet Sales YoY % :=
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('DimDate'[Date]))
RETURN
    DIVIDE(CurrentSales - PriorYearSales, PriorYearSales, 0)

Key Takeaway: Live Connection delegates data storage and modeling to an external Analysis Services model, locking local schema modification.

4. Composite Models and Aggregations

Composite models allow a single Power BI dataset to blend tables from multiple storage modes or combine disparate DirectQuery sources. This enables combining lightweight imported summaries with live transactional details.

• Execution Flow: Adding a local data source (such as an Excel budget or CSV file) to an active Live Connection triggers a prompt to convert the live link into a local DirectQuery model. This unlocks local schema relationships while maintaining remote connectivity.
• Mechanism: High-level report visuals query pre-calculated aggregate tables loaded in Import mode, ensuring sub-second response times. When a user drills down into granular transaction-level details, Power BI silently routes queries to the underlying DirectQuery fact table.
• Best Practice: Configure Dual storage mode on dimension tables shared across imported aggregate facts and DirectQuery detail facts to avoid cross-source query generation overhead.

// Example: Querying detail facts vs aggregate facts
// Power BI automatically routes high-level visuals to imported summaries
Total Revenue :=
SUMX(
    'FactSalesDetail', // DirectQuery detail table
    'FactSalesDetail'[Quantity] * 'FactSalesDetail'[UnitPrice]
)

Key Takeaway: Composite models combine imported aggregates and DirectQuery tables to deliver fast summary performance with granular drill-down.

Topics Covered in Data Connectivity & Storage Modes: Import vs DirectQuery vs Dual

1. Import Mode & VertiPaq (0:33 - 3:13) — Explains in-memory caching, VertiPaq compression mechanics, capacity file limits, and star schema best practices.
2. DirectQuery Architecture (3:13 - 5:16) — Demonstrates connecting without data caching, visual query generation, and DAX time intelligence limitations.
3. Live Connection Features (5:16 - 6:27) — Covers connecting to external Analysis Services models and datasets while highlighting interface and modeling restrictions.
4. Composite Models & Aggregations (6:27 - 8:05) — Walks through converting live links to DirectQuery models and pairing imported aggregations with detail tables.

Reference Cheat Sheet

• Import Mode — Caches compressed data in VertiPaq memory for fast querying

  Sql.Database("server", "db", [CreateNavigationProperties=false])
  

• DirectQuery Mode — Leaves data in source; passes native queries per visual

  Sql.Database("server", "db", [HierarchicalNavigation=true])
  

• Live Connection — Connects directly to external SSAS, AAS, or Power BI Dataset

  AnalysisServices.Database("asazure://region.asazure.windows.net/server", "Model")
  

• Composite Model Conversion — Converts Live Connection to DirectQuery to allow multiple sources

• Dual Storage Mode — Acts as Import or DirectQuery depending on related table

Comparison Table

Common Pitfalls

• Mistake: Overloading DirectQuery reports with dozens of visuals on one canvas. Avoid: Limit visual count and optimize database indexes to prevent query multiplication.
• Mistake: Attempting unsupported DAX time intelligence functions in DirectQuery. Avoid: Use standard native-foldable DAX calculations or pre-calculate time metrics in the source database.
• Mistake: Accidentally converting an enterprise Live Connection to DirectQuery. Avoid: Maintain separate shared datasets rather than importing ad-hoc flat files into governed models.

FAQs

• Why is the Data tab missing when connecting to an existing Power BI Dataset? Live Connection leaves table schemas and row data on the host instance, so local table editing is disabled.
• What causes DirectQuery reports to render slowly? Each visual generates simultaneous queries to the source, causing latency if the database lacks indexes or adequate compute.
• Can I revert a converted DirectQuery composite model back to a Live Connection? No, once a Live Connection is converted to DirectQuery to add local sources, it cannot be reversed.