Ravindra BagaleCourses & study guides Track your progress

Guides

Import vs DirectQuery vs Dual Storage Mode in Power BI

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

Import caches data in the Power BI model, DirectQuery keeps data in the source and queries it live, and Dual lets a table behave as either in a composite model — pick the mode per table for speed, freshness and size.

Chala mitrano! Storage mode is the quiet setting that decides whether your report feels snappy or “talks to SQL all day”. Why? Import, DirectQuery and Dual are not cosmetics. How? We compare all three, then sketch a FreshBasket (fictional) composite with tiny city sales. Longer page on purpose — stay with me.

Quick answer

Vertical chooser:

  1. Fits in memory + scheduled refresh OK → Import.
  2. Huge / must stay near real time on the database → DirectQuery.
  3. Mix Import facts with DQ facts (or similar) → composite model.
  4. Shared dimensions in a composite → often Dual.
  5. Check Model view → table → Storage mode.
  6. Document why each table uses its mode.
Import = cached copy
DirectQuery = live source queries
Dual = Import or DQ behaviour as needed

Why mode matters (rows in, number out)

Same three sales rows:

Order ID City Amount
A Mumbai 100
B Mumbai 40
C Nashik 80
  1. Import: rows are copied into the model. Card asks the model. Number out for Mumbai: 140. Fast.
  2. DirectQuery: rows stay in SQL. Card asks SQL. Number out can still be 140, but each click may hit the server.
  3. Dual (dimension): City table can ride with Import facts sometimes and with DirectQuery facts other times in a composite model.

Why you care:

  1. Wrong mode = slow slicers or stale boards.
  2. Interviews ask for one sentence on each mode.
  3. Composite models are common in real warehouses.
Three storage modes Import DirectQuery Dual Importcached DirectQuerylive Dualflexible modes

Storage modes: Import (cached), DirectQuery (live), Dual (can act as either in a composite model).

What do I need before this guide?

Before and after (look at the tables first)

Before storage mode All rows visible; total 190.

Before

After Import Online Imported Online rows; total 140.

After

Import = cached copy (filter Online → 140). DirectQuery = live SQL. Dual = hybrid for dimension tables. Know which mode your table uses.

Import mode (deep but calm)

  1. Data is copied into VertiPaq inside the .pbix / dataset.
  2. Visuals hit the model — usually fast.
  3. You refresh to pull newer rows.
  4. Richest DAX / modelling experience for learners.

FreshBasket (fictional) example: nightly SQL extract imported. Managers accept “as of last refresh”. Mumbai still shows 140 until morning refresh adds a new order.

DirectQuery mode

  1. Data stays in SQL (or other DQ sources).
  2. Each visual interaction can generate source queries.
  3. Fresher numbers; more load on the database; some feature limits.
  4. Great when Import size or latency is the real problem.

Phone-bill analogy: Import is downloading last month’s PDF. DirectQuery is opening the live account page every time someone asks.

Dual mode and composite models

Composite Dual Facts DQ dimensions Dual Fact: DirectQuery Dim: Dual composite

Composite model: fact tables often DirectQuery; small dimensions set to Dual so Import visuals can still filter them efficiently.

  1. A composite model mixes storage modes.
  2. Dual tables can act like Import or DirectQuery depending on the query path.
  3. Classic pattern: large facts in DirectQuery; small dimensions in Dual so Import-side queries still filter them efficiently.
  4. Set storage mode in Model view table properties.
Mode chooser When to pick each storage mode Small / scheduled refresh → Import Huge / near real-time → DirectQuery Mix both → Dual on shared dimensions

Chooser: small/fast → Import; huge/near-real-time → DirectQuery; mix both → Dual on shared dimensions.

Live connection vs DirectQuery (one clarifying line)

  1. DirectQuery — table-level live access to a database in your model.
  2. Live connection — often means connecting to an already published Analysis Services / Power BI dataset experience.
  3. Related family, not identical twins — course lessons spell out the product nuances.

Decision workshop (vertical)

  1. Classroom sample under a few million rows → Import.
  2. Branch dashboard that must mirror POS within minutes → consider DirectQuery (with performance care).
  3. Historical Import mart + live inventory table → composite; Dual on shared dims.
  4. Unsure? Import first, measure pain, then redesign.

Mistakes and calm fixes

Symptom Likely cause Fix
Everything feels slow DQ + chatty visuals Fewer visuals; simpler measures; Import if possible
Cannot use a feature DQ limitation Check Microsoft docs; Import that table
Relationships weird Composite pitfalls Start simpler; Dual on shared dimensions
Stale numbers (Import) No refresh Schedule refresh / gateway
Confused Dual Treated like a fourth database Dual is a mode, not a new source

Ghabru naka 😅 — mode is reversible with care, but plan before you build twenty pages.

Ravindra Bagale's Tip

Interview line that scores: “Import for speed and full DAX; DirectQuery for large/live sources; Dual for dimensions in composite models.” Then give one example. Students who only say “DirectQuery is live” sound half-ready. Got it?

Practice task

  1. In Model view, inspect Storage mode on each table of a sample file.
  2. Write a three-row table: table name → mode → why.
  3. Sketch one composite: Orders DQ, Date Dual, Product Dual.
  4. Read the storage-modes course lesson and tick matching sentences.

Learn it properly

Course lessons:

Related guides: Connect SQL Server · File types

Got it? Import = cached. DirectQuery = live. Dual = flexible in composites. Choose on purpose, then learn the Report view panes. Let's continue.

Frequently asked questions

What is Import mode?

Power BI copies data into the model (VertiPaq). Visuals are fast; you refresh to get newer data.

What is DirectQuery?

Power BI leaves data in the source and sends queries when visuals need data — fresher, but slower and with feature limits.

What is Dual?

A table storage mode in composite models that can behave as Import or DirectQuery depending on the query path.

What is a composite model?

A model that mixes storage modes (for example Import dimensions + DirectQuery facts, or multiple DirectQuery sources).

Is Live connection the same as DirectQuery?

Live connection usually means connecting to an already-published model/AS dataset; DirectQuery talks to a database per table. Related but not identical.

Course deep dive?

Storage modes lesson and Import vs DirectQuery vs Live in the SQL chapter.