Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 56 ยท Getting Data

Import, DirectQuery and Live Connection

Understand Power BI storage modes: Import, DirectQuery, Dual and Live Connection, composite models, refresh versus live queries, and the trade-offs.

Connecting to a source answers where the data comes from. A second decision answers when Power BI reads it: once, ahead of time, and kept in memory, or again every time someone clicks a visual. This choice is called the storage mode, and it shapes speed, freshness, size limits and which features you can use.

This lesson explains the modes (Import, DirectQuery, Dual, Live Connection), what a composite model is, how refresh differs from live queries, and how to choose. The modes are features of the real tool and are described read-only. The stand-ins are small simulations in Python that count the work each approach does, so you can see the trade-off in numbers. They are teaching models, not benchmarks of Power BI.

The modes at a glance

Import

Copy the data Stored compressed in the model.
Fast visuals Answered from memory.
Needs refresh Data is as old as the last refresh.
1 / 3

Import mode

In Import mode Power BI reads the data from the source during a refresh and stores a copy inside the semantic model. The engine behind it keeps data in a compressed, column-oriented in-memory format, which is why Import is usually the fastest option. Visuals are answered from that copy, without touching the source. You also get the full DAX language, the full set of Power Query transformations and the most flexibility.

The costs are size and freshness. The model has to fit in memory and under the size limit of your licence (shared Pro workspaces have a limit around 1 GB per model, and larger capacities allow much more; check current numbers). And the numbers are only as recent as the last refresh. You can refresh manually, or schedule refreshes in the service (see Scheduled Refresh and Gateways).

Import is the default and the right choice for most reports.

DirectQuery mode

In DirectQuery mode the model stores only the structure (table and column names, relationships, measures), not the rows. When a user opens a page or clicks a slicer, Power BI translates each visual into a query, usually SQL, sends it to the source and shows the result. The data is always current and the model stays small, so it suits very large or constantly changing sources.

The costs are speed and limits. Every interaction waits for the source database to answer, so the report is only as fast as that database and the network. Many concurrent users multiply the queries the source has to handle. Some DAX functions (notably certain time intelligence functions) and some Power Query steps are restricted or unavailable. Results from a single query are capped, and you should design the model so each visual asks for aggregates, not millions of rows. Relational databases such as SQL Server, Azure SQL, Oracle, PostgreSQL and Snowflake are the usual candidates.

DirectQuery is not a way to avoid refresh problems. It moves the load to your database. If that database is slow or shared with critical applications, reports can become slow and may slow other systems down.

Live Connection

A Live Connection does not hold a model at all. Your report connects to a model that already exists elsewhere: a semantic model published in the Power BI service, or a SQL Server Analysis Services or Azure Analysis Services model. In Desktop you see the model's fields, but you cannot edit the tables, relationships or Power Query. You can add visuals, and report-level measures where the source allows it.

This is how organisations separate roles. A central team builds and certifies one model, and many analysts build reports on it. Everybody uses the same definitions, and the model is refreshed once. A related feature, DirectQuery for Power BI semantic models, lets you extend a published model with your own tables, which turns it into a composite model.

Dual mode and composite models

A composite model mixes storage modes in one model. For example, a big sales fact table stays in DirectQuery while small dimension tables such as Product and Region are Imported. This works well when most slicers and chart axes use the small tables.

A table in Dual mode can behave either way, depending on the query. If a visual combines it with Import tables, it is read from memory. If the visual combines it with a DirectQuery table from the same source, it can be queried in the source, so Power BI can generate one efficient query instead of moving data around. Dimension tables used by DirectQuery fact tables are the typical Dual candidates.

Relationships between tables that live in different storage groups or sources are called limited relationships. They work, but behave differently: some DAX functions such as RELATED do not work across them, and the engine cannot always guarantee uniqueness, so results can differ from what you expect. Keep related tables in the same source and mode when you can.

Newer Microsoft Fabric options, such as Direct Lake, aim to combine Import-like speed with fresher data by reading directly from files in OneLake. They are outside the scope of this lesson but follow the same question: when and where is the data read?

flowchart TB
    A["User clicks a slicer"] --> B{"Storage mode"}
    B -->|Import| C["Query the in-memory model"]
    B -->|DirectQuery| D["Send a query to the source"]
    B -->|Dual| E["Either, whichever is cheaper"]
    C --> F["Visual updates"]
    D --> F
    E --> F

Each click is answered from memory or by the source, depending on the mode of the tables involved.

Refresh versus live queries

These two words describe different things.

  • A refresh re-reads data from the source into an Import table. It is a batch operation that happens at a schedule or on demand, and takes minutes. Users never wait for it.
  • A live query happens each time a user interacts with a DirectQuery visual. It is small and frequent, and users wait for it.

So Import has one slow, predictable cost (refresh) and cheap interactions, while DirectQuery has no refresh but a cost on every interaction. There is also a middle road: for Import tables you can configure incremental refresh, which re-reads only recent partitions, and a later lesson on Query Folding, Incremental Refresh and Query Performance shows how.

Counting the work, in Python

The simulation below uses a class that counts how many queries reach the source and how many rows the source scans. The Import model reads the table once; the DirectQuery model sends a query for each interaction.

Python Stand-in: Import versus DirectQuery work

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Import hits the source exactly once; DirectQuery hits it once per click, and a busy report with many users and visuals multiplies that. Real engines are far smarter than a full table scan, so the absolute numbers mean little. The pattern (one big read versus many small ones) is the point.

Freshness: the other side of the trade

The second simulation shows why DirectQuery's costs can be worth paying. A row is added at the source after the Import model was refreshed.

Python Stand-in: freshness and refresh

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

How to choose

Start with Import and move away only for a reason.

Situation Usual choice
Data fits in the model and hourly or daily freshness is fine Import
Source is huge or must show second-by-second data and the database can cope DirectQuery
Large fact table, small dimensions Composite: fact in DirectQuery, dimensions in Dual or Import
A company model already exists Live Connection (or DirectQuery for semantic models to extend it)
Row-level security must be enforced by the source database DirectQuery can be considered, with its own care

Also consider the people using it. A report with fifty simultaneous users in DirectQuery sends many more queries to the source than the same report in Import.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In Import mode, when does Power BI read from the data source?

    Import copies the data into the model at refresh time. Visuals are then answered from memory, so the data is as old as the last refresh.
  2. 2. What is the main downside of DirectQuery?

    Every visual sends queries to the source, so speed depends on that system. DirectQuery keeps the model small and the data current, but it limits some DAX and Power Query features.
  3. 3. What is a Live Connection?

    A Live Connection attaches a report to an existing model. You can build visuals but you do not edit the model's tables or Power Query.
  4. 4. In a composite model, which tables are good candidates for Dual mode?

    Dual lets a small dimension act as Import or as a query against the source, depending on what it is combined with, which keeps queries efficient.

Summary

  • Import copies data into a fast in-memory model; it is the default and needs refresh to stay current.
  • DirectQuery stores no rows and sends queries to the source on every interaction; it is fresh and small but depends on the source and restricts some features.
  • Live Connection attaches a report to an existing model; you cannot edit tables or Power Query.
  • Dual tables behave as Import or DirectQuery as needed; composite models mix modes and sources, with limited relationships across groups.
  • Refresh is a scheduled batch; a live query happens on each interaction.
  • Start with Import and choose another mode only for a clear reason such as size or freshness.

Next, see how credentials, privacy levels and parameters keep connections secure and portable in Data Source Settings, Privacy Levels and Parameters.