← All Kits · Power BI Kit

Connecting Power BI to a Database: Import or DirectQuery, and What Breaks Later

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

Get Data, SQL Server, type the server name, and then a dialog asks whether you want Import or DirectQuery. It looks like a technical detail. It decides how fast your report is, how fresh the data is, which DAX you are allowed to write, and what happens when you publish.

What you do: choose Import unless a specific requirement forbids it, connect to views rather than to hand-written SQL, and set up the gateway before you promise anybody a refresh.

The short version. Import copies the data. DirectQuery borrows it, every click.

The two modes

The DAX restriction is the one people meet last and mind most. Several time intelligence and table functions are unavailable or slow in DirectQuery, so a measure that works in a demo file fails in the real report.

Choose DirectQuery when

1. The data genuinely must be current to the minute, and somebody can name the decision that requires it.
2. The table is far too large to import, and pre-aggregating is not possible.
3. A security rule says the data may not be copied out of the source system.

Those are the three. "It feels more real time" is not one, and it costs the audience several seconds on every click.

Composite models let you mix the two: import the dimensions, DirectQuery the very large fact table. That is the right answer more often than either mode alone, and it is worth knowing before you commit a whole report to DirectQuery.

Connecting well

1. Connect to views, not to raw tables, where you can. A view is a contract: the database owner can change the underlying tables without breaking your report.
2. Do not paste SQL into the connector unless you must. Native SQL blocks query folding, so every later Power Query step is done locally instead of by the database. See refresh errors for what that costs.
3. Select only the columns you need. Model size is driven by columns and their cardinality far more than by rows, and a high-cardinality text column such as a free-text note can dominate the file.
4. Filter early, in the source or the first query step, so folding pushes it back to the database.
5. Use a parameter for the server and database names, so moving from test to production is one edit rather than twelve.

Credentials, and the gateway

This is where most published reports fail. Desktop uses your Windows or database credentials on your machine; the Service has its own separate credential store and no route into your network.

1. Publish the report.
2. In the Service, open the dataset settings and set Data source credentials.
3. For any source inside your network, install the on-premises data gateway on a machine that stays on.
4. Map the data source in the gateway configuration, matching the server and database names exactly as they appear in the file. A mismatch here is the usual cause of a gateway that is installed and still not used.
5. Set the refresh schedule, and turn on failure notifications.

A gateway on a personal laptop works and will fail every time the laptop sleeps. If the report matters, the gateway belongs on a server.

Security

• Use a read-only account for reporting. A report has no reason to be able to write.
• Prefer a service account over a personal one, or the report breaks when somebody leaves.
• Remember that an imported model contains the data. Anyone with access to the report file has the rows, whatever the source system permits.
• Row-level security is applied in the model, not in the source, unless you deliberately pass the user identity through. See row-level security.

How to apply this to your own work

1. Check the storage mode on your current report. It is shown in the status bar at the bottom right.
2. If it is DirectQuery, ask which of the three reasons applies. If none does, test an Import version and compare the click response.
3. Ask your database owner for views rather than table access, and explain that it lets them refactor safely.
4. Remove columns you do not use and watch the file size fall.
5. Set up the gateway and credentials before promising a refresh schedule, not after the first failure email.

The one habit to keep

Ask where the query will run. That single question explains report speed, refresh failures, database load and half the DAX restrictions, and it is the question the connection dialog was really asking.

Do you know which mode your most-used report is in?