Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🔌 Module 2 · Connecting to Data Intermediate LESSON 14 / 72

Import vs DirectQuery vs Live Connection

29 min 5 quiz questions 1 practical challenge 4 syllabus topics

This is the most consequential decision in a Power BI project, and it is made in a dropdown most people skim past. Storage mode determines your performance, your data freshness, which DAX you can write, and what your model can do. Changing it later usually means rebuilding.

Lesson 14 of 72 · Connecting to Data 19%
01

Learning Objective

Import is right the great majority of the time. Knowing why, and knowing the specific circumstances that justify DirectQuery, is what stops you making an expensive mistake in the first five minutes of a project.

By the end of this lesson you will be able to
  • Explain what Import, DirectQuery and Live connection each actually do
  • Compare them on performance, freshness, model features and cost
  • Apply a decision framework to choose correctly for a given requirement
  • Explain what a composite model is and when Dual storage mode helps
  • Recognise the DAX and modelling limitations DirectQuery imposes
02

Why This Matters

"The business said they need real-time data, so I used DirectQuery."

This sentence has started more failed Power BI projects than any other. Almost always, "real-time" turns out to mean "not yesterday's figures" — which a scheduled refresh every hour satisfies completely, at a fraction of the complexity.

Meanwhile the DirectQuery model is slow (every visual is a live query), limited (much of DAX is unavailable or degraded), and it puts continuous load on a production database that the DBA did not agree to.

Ask the freshness question precisely
Not "do you need real-time data?" — everyone says yes to that. Ask: "If this number were up to an hour old, what decision would you get wrong?"

In most business reporting the honest answer is "none". Where the answer is genuine — operational dashboards, live monitoring — DirectQuery earns its cost. Elsewhere it is a self-inflicted wound.
03

Concept

The three modes

What each one does
ModeWhere the data livesWhen queries runModel contains
ImportCopied into the model, compressed in memoryAgainst the in-memory model — instantlyThe data itself
DirectQueryStays in the sourceAgainst the source, every time a visual rendersOnly metadata and query definitions
Live connectionIn an existing published modelAgainst that modelNothing — you are connecting to someone else's semantic model

Live connection is different

A live connection points at an already-published semantic model — a Power BI model in the service, or Analysis Services. You are not modelling at all; you are building a report on someone else's model. You cannot add tables or relationships, though recent versions do let you add report-level measures.

This is the right pattern in a mature organisation: one governed model, many reports built on it. It is how you stop fifteen teams each defining "revenue" differently.

The comparison that matters

Import vs DirectQuery
ImportDirectQuery
Visual performanceVery fast — in-memory columnar engineDepends entirely on the source; every visual is a round trip
Data freshnessAs of the last refreshCurrent
Data volumeLimited by model size limits and memoryEffectively unlimited — data stays put
DAX availableAll of itRestricted — many functions unavailable or slow
Power Query transformationsAll of themOnly what folds
Calculated columnsFull supportLimited; can hurt performance badly
Load on the sourceOnce per refreshContinuous, proportional to report usage
Works offline in DesktopYesNo — needs the source reachable
RelationshipsFull supportSome cross-source limitations
File sizeLarge — the data is in itTiny — metadata only
Why Import is so fast
Import uses VertiPaq, an in-memory columnar engine with aggressive compression. It stores each column separately, sorted and dictionary-encoded, which makes aggregating hundreds of millions of values genuinely fast. A well-modelled Import dataset routinely outperforms DirectQuery against a well-tuned database, because it eliminates the network entirely and the storage layout is optimised for exactly this workload.

When DirectQuery is genuinely right

  • The data is too large to import. Genuinely too large — hundreds of millions to billions of rows, beyond what your capacity allows.
  • Sub-minute freshness is a real requirement. Operational monitoring where a five-minute-old number would cause a wrong action.
  • Regulatory constraints prevent copying data. The data may not leave its system of record.
  • The source enforces security you must inherit. Some architectures require the query to run as the end user against the source.

Note what is not on that list: "the data changes during the day" (use scheduled refresh), "the table is big" (define big — VertiPaq compresses well), and "it feels more modern".

Composite models and Dual

A composite model mixes storage modes in one model. The usual pattern:

  • A huge fact table in DirectQuery, because importing it is impractical.
  • Small dimension tables in Dual mode — imported and available for DirectQuery.
  • Aggregation tables in Import, which the engine uses instead of the detail table when a query can be answered from them.
Why Dual exists
A dimension in pure Import mode cannot be joined server-side to a DirectQuery fact, so every slicer selection becomes an expensive operation. Dual lets the engine choose: use the imported copy for a slicer's own list of values, and participate in a server-side join when the query touches the DirectQuery fact.

Setting small dimensions to Dual in a composite model is one of the highest-value optimisations available, and it is a single dropdown per table.
Composite models, aggregations and some DirectQuery features have licensing and capacity implications, and the specifics change. Verify against Microsoft's composite model documentation before designing around them.
04

Visual Explanation

Work through the decision. Each node explains what to ask and what the answer implies.

What DirectQuery costs you in DAX

Commonly affected areas
AreaImportDirectQuery
Time intelligence (TOTALYTD, SAMEPERIODLASTYEAR)FullWorks, but often slow; needs a date table in a compatible mode
Complex iterators (SUMX over a large table)FastCan be very slow — each iteration may become a query
Calculated columnsFullRestricted; some expressions unsupported
Calculated tablesFullLargely unsupported on DirectQuery sources
Certain text and date functionsFullSome have no SQL equivalent and are unavailable
Quick measuresFullSome unavailable
The precise list of restrictions varies by connector and changes between releases. The pattern is stable: if it cannot be translated into the source's query language, it is either unavailable or slow. Check Microsoft's DirectQuery documentation for current specifics.
05

Power BI Demonstration

See where the choice is made
Get data › SQL Server database. The Data Connectivity mode radio buttons are right there in the first dialog. This is the decision.
Note that file sources do not offer it
Connect to a CSV. There is no storage mode choice — file connectors are Import only. DirectQuery requires a source that can answer queries.
Check a table's storage mode
In Model view, select a table and open the Properties panel. Storage mode shows Import, DirectQuery or Dual.
Change a dimension to Dual (composite models only)
In a composite model, select a small dimension table → Properties → Storage mode › Dual. Power BI warns that this cannot be reversed for that table.
Observe the one-way warning
Read the dialog carefully. DirectQuery → Import is a one-way change, and Import → DirectQuery generally is not offered at all. This is why the decision matters early.
Compare responsiveness
If you have both available, build the same visual against an Import table and a DirectQuery table and click a slicer on each. The difference is immediate and obvious.
Use Performance Analyzer on a DirectQuery visual
Optimize › Performance Analyzer › Start recording, then interact. The DAX query line shows the round-trip cost. On DirectQuery you can also copy the generated query.
Check the model size difference
Save an Import model and a DirectQuery model of the same source and compare file sizes. The DirectQuery file will be a tiny fraction — it contains no data.
Model viewselect tablePropertiesStorage mode
Storage mode changes are largely one-way
Power BI will let you convert a DirectQuery table to Import, and warns you that it cannot be undone. Going the other way generally is not offered. Choosing DirectQuery casually and changing your mind later usually means rebuilding the model — which is precisely why the decision framework matters at the start.
06

Example Dataset

This lesson is conceptual and needs no download — it uses the sample data shown inline above. The next lesson that requires a file will link it here.

You can still browse every dataset in the course from the dataset library.

07

Step-by-Step Exercise

A decision drill. For each scenario, choose a mode and justify it in one sentence. Write your answers before checking section 08.

  1. A finance team needs monthly P&L reporting from a 400,000-row ledger table, refreshed each morning.
  2. A logistics operations centre needs a wall display showing vehicles currently delayed, updated continuously.
  3. A retail analyst needs three years of transactions — 90 million rows across 40 columns, of which 12 are needed — for trend analysis.
  4. A hospital needs reporting on patient data that, under its governance policy, may not be copied outside the clinical system.
  5. A marketing team wants a report built on the company's certified Sales semantic model, already published in the service.
  6. A supply chain team needs 2 billion rows of IoT sensor readings at detail level, plus fast monthly summaries.
08

Expected Result

Decision drill answers
ScenarioModeWhy
Monthly P&L, 400K rows, daily refreshImportSmall table, daily freshness is ample. This is the default case and there is no reason to deviate.
Live delayed-vehicle wall displayDirectQueryGenuine sub-minute freshness requirement where a stale number causes a wrong operational decision. One of the few clear-cut cases.
90M rows, 40 columns, 12 neededImportImport only the 12 columns. VertiPaq will compress this comfortably — this is a fraction of the size the raw figure suggests. Do not let the row count alone drive the decision.
Patient data that may not be copiedDirectQueryThe constraint decides it. But verify the policy is real rather than assumed — it is worth one conversation.
Report on a certified published modelLive connectionReuse the governed model. Adding a second definition of the same metrics is the problem governance exists to prevent.
2B IoT rows plus fast monthly summariesCompositeDirectQuery the detail fact, Import an aggregated monthly table, set dimensions to Dual. Most queries hit the aggregation and are fast; drill to detail still works.
Note how the third scenario is designed to mislead
"90 million rows" sounds like a DirectQuery case and is not. Once you drop 28 unneeded columns, VertiPaq's compression makes this a perfectly ordinary Import model. Row count alone is a poor guide — column count and cardinality matter far more, which Module 11 Lesson 2 covers properly.

The correct move when someone says "it's too big to import" is to try it on a filtered, column-reduced subset and measure, rather than accepting the assertion.
09

Common Mistakes

Choosing DirectQuery because someone said "real-time"
The requirement is almost never genuinely real-time. You pay a large, permanent cost in performance and capability for freshness nobody needed.

Fix: Ask the precise question: "if this were an hour old, what decision would go wrong?" Design around the actual answer.
Judging "too large to import" by row count
Row count is a poor proxy. Column count, cardinality and the years you actually need matter far more, and compression is very effective.

Fix: Import a filtered, column-reduced version and measure the result before concluding. Most "too large" tables are not.
Leaving dimensions in Import mode in a composite model
The engine cannot join an Import dimension to a DirectQuery fact server-side, so every slicer interaction becomes expensive.

Fix: Set small dimensions to Dual. One dropdown per table, and often a large improvement.
Writing Import-style DAX on a DirectQuery model
Heavy iterators and complex time intelligence that are instant on Import can be unusably slow when each iteration becomes a query.

Fix: Keep DirectQuery measures simple. Push complexity into the source — a view, a computed column in the database, or an aggregation table.
Not telling the DBA you are using DirectQuery
Every report interaction becomes queries against their production database. Usage scales with report popularity, and the first they hear of it is a performance incident.

Fix: Tell them before you build. Agree on which tables, expected concurrency, and whether a read replica would be better. This is a courtesy that pays back repeatedly.
10

Professional Tip

Import a subset before declaring anything impossible
When someone tells you a table is too large to import, spend twenty minutes testing it rather than accepting it.

Connect, filter to the period you actually need, keep only the columns you actually need, import, and look at the resulting model size. The compression ratio on a real table is routinely far better than people expect — high-cardinality columns compress poorly, but most columns are not high-cardinality.

The number of projects that go to DirectQuery on an untested assumption is genuinely large, and the cost of that assumption is paid every day the report is used.

Two more:

  • If you must use DirectQuery, push work into the source. A view that pre-joins and pre-aggregates turns a slow DirectQuery model into a fast one. The database is better at this than Power Query is.
  • Document the storage-mode decision in the README. Whoever inherits the model needs to know it was a deliberate choice and why — otherwise they will assume it was an accident and "fix" it.
11

Knowledge Check

Knowledge Check

5 questions. Answer each one to see why the right answer is right.

A stakeholder says they need "real-time" sales figures. What should you ask?

Why is Import mode usually faster than DirectQuery, even against a well-tuned database?

In a composite model with a DirectQuery fact table, what storage mode should small dimension tables usually use?

You are told a 90-million-row table is "too big to import". What should you do first?

What is a Live connection?

12

Practical Challenge

Practical Challenge

Write a storage mode recommendation for a mixed requirement

A manufacturer asks for one Power BI solution covering three needs from the same data platform. Produce a storage mode recommendation with justification, and name the risks.

Requirements
  • Need A: a plant-floor display showing machines currently in fault state, which operators act on within minutes
  • Need B: monthly production and yield reporting for the leadership team, from 400 million rows of sensor history
  • Need C: a quality team that occasionally drills into individual sensor readings for a specific machine and hour
  • Recommend a mode for each need, with one sentence of justification
  • State whether this should be one model or several, and why
  • Name two risks and how you would mitigate them

Resist the urge to pick one mode for everything. The three needs have genuinely different freshness and volume characteristics.

For the one-model-or-several question, think about who maintains it, who consumes it, and whether a single model would force compromises that hurt all three needs.

13

Solution

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

The recommendation

NeedModeJustification
A — plant-floor fault displayDirectQueryGenuine sub-minute freshness requirement with a real operational consequence. This is one of the few clear-cut DirectQuery cases, and the query is simple — current state of a few hundred machines
B — monthly leadership reportingImport, on an aggregated tableMonthly reporting does not need row-level detail. Import a pre-aggregated table at machine-hour or machine-day grain and the 400 million rows become a few million
C — occasional quality drill-downComposite — DirectQuery detail behind the imported aggregateRare, narrow queries against detail. DirectQuery is fine because concurrency is low and the filter is always tight (one machine, one hour)

One model or several?

Two models, not one or three
Model 1 — Operations. A small, simple DirectQuery model serving need A only. It queries current machine state, has almost no DAX, and refreshes continuously. Keeping it separate means the wall display cannot be slowed down by anything the analytics model does, and its simplicity makes it reliable — which matters when operators depend on it.

Model 2 — Analytics. A composite model serving needs B and C: an imported aggregate table for the monthly reporting, a DirectQuery detail table for drill-down, and dimensions in Dual mode. These two needs share dimensions, definitions and audience, so splitting them would duplicate work and risk divergent metrics.

Why not one model? Need A's freshness requirement would force the whole model toward DirectQuery, destroying the performance need B depends on. Why not three? B and C share everything except grain; separating them would mean two definitions of yield.

Risks and mitigations

RiskWhy it mattersMitigation
DirectQuery load on the production platformThe wall display queries continuously, and the quality team's drill-downs hit detail tables. If this is the same system running the plant, a performance incident is a production incidentAgree the pattern with the platform team before building. Point DirectQuery at a read replica if one exists. Set a sensible refresh interval on the display — 'continuous' rarely needs to mean every second
The aggregate table drifting from the detailIf the monthly aggregate is built by a separate job, a bug or a late-arriving batch means leadership and the quality team see different numbers for the same period — the most corrosive kind of reporting failureBuild the aggregation as a scheduled job with a reconciliation check that compares aggregate totals against detail totals and alerts on divergence. Surface the last successful reconciliation date on the report itself

The judgement being tested

The easy answer is to pick one mode and apply it everywhere. The correct answer recognises that storage mode is a per-requirement decision, not a per-project one — and that the right architecture here has two models with different characteristics serving different audiences.

The second risk is the more sophisticated observation. Aggregate tables are the standard solution to this problem and they introduce a new failure mode: two sources of truth that can silently disagree. Naming that risk, and proposing a reconciliation check rather than just hoping, is what a reviewer would look for.

14

Lesson Summary

Key takeaways
  • Import copies data into an in-memory columnar engine. Fast, full DAX, full Power Query, no ongoing source load. This is the default and usually correct choice.
  • DirectQuery leaves data in the source and queries it live. Current data, unlimited volume — at the cost of speed, DAX capability and continuous source load.
  • Live connection points at an already-published semantic model. Right when governed definitions already exist.
  • Ask the freshness question precisely: "if this were an hour old, what decision would go wrong?"
  • Row count is a poor guide to importability. Test with a filtered, column-reduced import before concluding.
  • In a composite model, set small dimensions to Dual so they can join server-side to a DirectQuery fact.
  • Storage mode changes are largely one-way. Decide deliberately at the start.
  • Tell the DBA before you build a DirectQuery model against their production database.

Syllabus topics delivered

Import vs DirectQueryLive connectionChoosing the right sourceStorage mode trade-offs
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 19% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.