In this guide 6 sections
Import copies data into the BI tool on a schedule. Reports open fast and the source stays quiet, but figures are as old as the last refresh. Live query reads the source each time. Figures are current, yet slow queries and renamed columns show up in front of users.
What each mode actually does
Import, which Power BI calls Import mode and Tableau calls an extract, pulls rows into a compressed store inside the BI service. Reports then run against that copy. The source is touched only when a refresh runs.
Live query, called DirectQuery or a live connection, sends SQL to the source each time someone opens a page or changes a filter. Nothing is stored. Every click becomes a query.
Some tools mix the two, importing small dimension tables such as customers and dates while querying a large fact table of invoice lines live.
Freshness against speed
Import is as fresh as its schedule. Refresh at 6 a.m. and a sale booked at noon is invisible until tomorrow. Most finance reviews can live with that. A dispatch board cannot.
Live query shows the current row, but each visual waits for the database. A page with twelve charts fires twelve queries. If the source is a busy PostgreSQL or SQL Server instance without tuned indexes, that page can take half a minute.
Ask how stale a number can be before a dispatcher or a collections clerk makes a wrong call, because that answer picks the mode.
- Month-end review: import
- Daily sales and collections: import with an early refresh
- Live stock at the dispatch bay: live query or a short refresh
- Executive pack read on a phone: import
Load on the source system
A refresh reads a large amount of data once, ideally overnight, so the cost is predictable and you can place it outside trading hours when nobody is billing. Incremental refresh reads only rows changed since the last run, which keeps even a large invoice lines table cheap enough to refresh several times a day.
Live query spreads small reads across the working day, and they arrive in bursts when a manager opens a dashboard in a meeting. Run those against your transactional database and you may slow invoice entry for everyone else.
Need live? Point it at a read replica or a reporting copy. Never at the primary.
What breaks when a column is renamed
Rename a column in the source and the next import refresh fails, while the report keeps showing the last good copy and an alert goes to the owner. Annoying, but contained.
A live report fails on the spot. Every visual that used the old name shows an error while people are looking at it, and calculated measures that reference the field break too.
Either way, put a view between the source tables and the BI tool, so column names stay stable when the table underneath changes. Only the view needs editing.
A simple way to choose
Default to import. Move a table to live query only when staleness costs you something real and the source can carry the traffic. Hybrid covers most cases.
Before you decide, test it. Load one real report both ways, time ten openings in the morning rush, and note the source database’s CPU during the test. Write the results next to the freshness need you agreed.
Review the choice whenever data volume doubles. Do the same when a scheduled refresh starts taking longer than its overnight window.
Connection mode decision sheet
A single sheet, one line per dataset. Columns hold source system, row count, longest acceptable staleness and peak users. Others hold chosen mode (import, live, hybrid), refresh time, view in front (yes or no) and timed test result. A last box names who gets the failed-refresh alert.
Open a blank worksheet to printQuestions owners ask
Is live query always slower than import?
Usually, yes, because every visual runs a query. A well-indexed reporting database can feel quick, but an in-memory import is generally faster for large pages.
How often should an import refresh?
As often as the slowest decision needs and as rarely as the business allows. Daily suits most sales and finance reports. Hourly is worth it only if someone acts on the change.
Can we import some tables and query others live?
Yes. A common pattern imports customers, products and dates, then queries a single large transactions table live or refreshes it incrementally.
Does import copy sensitive data out of our database?
It does store a copy in the BI service. Check where that service hosts data, apply row-level security there, and limit who can download the dataset or publish it to a personal workspace.