Import vs DirectQuery vs Live Connection
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.
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.
- 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
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.
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.
Concept
The three modes
| Mode | Where the data lives | When queries run | Model contains |
|---|---|---|---|
| Import | Copied into the model, compressed in memory | Against the in-memory model — instantly | The data itself |
| DirectQuery | Stays in the source | Against the source, every time a visual renders | Only metadata and query definitions |
| Live connection | In an existing published model | Against that model | Nothing — 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 | DirectQuery | |
|---|---|---|
| Visual performance | Very fast — in-memory columnar engine | Depends entirely on the source; every visual is a round trip |
| Data freshness | As of the last refresh | Current |
| Data volume | Limited by model size limits and memory | Effectively unlimited — data stays put |
| DAX available | All of it | Restricted — many functions unavailable or slow |
| Power Query transformations | All of them | Only what folds |
| Calculated columns | Full support | Limited; can hurt performance badly |
| Load on the source | Once per refresh | Continuous, proportional to report usage |
| Works offline in Desktop | Yes | No — needs the source reachable |
| Relationships | Full support | Some cross-source limitations |
| File size | Large — the data is in it | Tiny — metadata only |
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.
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.
Visual Explanation
Work through the decision. Each node explains what to ask and what the answer implies.
What DirectQuery costs you in DAX
| Area | Import | DirectQuery |
|---|---|---|
| Time intelligence (TOTALYTD, SAMEPERIODLASTYEAR) | Full | Works, but often slow; needs a date table in a compatible mode |
| Complex iterators (SUMX over a large table) | Fast | Can be very slow — each iteration may become a query |
| Calculated columns | Full | Restricted; some expressions unsupported |
| Calculated tables | Full | Largely unsupported on DirectQuery sources |
| Certain text and date functions | Full | Some have no SQL equivalent and are unavailable |
| Quick measures | Full | Some unavailable |
Power BI Demonstration
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.
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.
- A finance team needs monthly P&L reporting from a 400,000-row ledger table, refreshed each morning.
- A logistics operations centre needs a wall display showing vehicles currently delayed, updated continuously.
- A retail analyst needs three years of transactions — 90 million rows across 40 columns, of which 12 are needed — for trend analysis.
- A hospital needs reporting on patient data that, under its governance policy, may not be copied outside the clinical system.
- A marketing team wants a report built on the company's certified Sales semantic model, already published in the service.
- A supply chain team needs 2 billion rows of IoT sensor readings at detail level, plus fast monthly summaries.
Expected Result
| Scenario | Mode | Why |
|---|---|---|
| Monthly P&L, 400K rows, daily refresh | Import | Small table, daily freshness is ample. This is the default case and there is no reason to deviate. |
| Live delayed-vehicle wall display | DirectQuery | Genuine 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 needed | Import | Import 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 copied | DirectQuery | The constraint decides it. But verify the policy is real rather than assumed — it is worth one conversation. |
| Report on a certified published model | Live connection | Reuse the governed model. Adding a second definition of the same metrics is the problem governance exists to prevent. |
| 2B IoT rows plus fast monthly summaries | Composite | DirectQuery 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. |
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.
Common Mistakes
Fix: Ask the precise question: "if this were an hour old, what decision would go wrong?" Design around the actual answer.
Fix: Import a filtered, column-reduced version and measure the result before concluding. Most "too large" tables are not.
Fix: Set small dimensions to Dual. One dropdown per table, and often a large improvement.
Fix: Keep DirectQuery measures simple. Push complexity into the source — a view, a computed column in the database, or an aggregation table.
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.
Professional Tip
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.
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?
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.
- 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.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The recommendation
| Need | Mode | Justification |
|---|---|---|
| A — plant-floor fault display | DirectQuery | Genuine 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 reporting | Import, on an aggregated table | Monthly 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-down | Composite — DirectQuery detail behind the imported aggregate | Rare, 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?
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
| Risk | Why it matters | Mitigation |
|---|---|---|
| DirectQuery load on the production platform | The 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 incident | Agree 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 detail | If 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 failure | Build 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.
Lesson Summary
- 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
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.