Power BI Connectivity and Gateways
Choose the mode that fits your freshness and size
Power BI connects to sources via import, DirectQuery, Live connection and dataflows; on-prem sources go through the on-premises data gateway. Choosing the right mode trades latency, size and freshness.
What you will learn
- Compare import, DirectQuery, Live and dataflow access
- Explain the gateway's role for on-prem data
- Choose a connectivity mode from workload constraints
What is this?
Power BI reaches data four ways: Import (data cached in memory, fully interactive, needs refresh), DirectQuery (queries the source per visual — no cache, live), Live (a live connection to a premium dataset/semantic model), and dataflows (shared, reusable prep stored in the service). Gateways bridge on-prem sources securely for both refresh and DQ.
Why do you need it?
- Import gives interactivity and speed but forces refresh cycles; DirectQuery stays at source freshness.
- Some dashboards need seconds-old data; others tolerate hourly — mode follows SLA.
- On-prem systems need a secure bridge (gateway), not open ports.
- Mis-matching mode to workload is a top source of performance/size problems.
Mental model
Reading a book from your shelf vs live study of the author
Import vs DirectQuery
Import is the copy on your shelf: instant, but it ages between editions (refreshes). DirectQuery is quizzing the author live: always current but every question goes across the wire. Live connections are like joining a shared class session — you read what the teacher projects.
How it works
- Import builds an in-memory copy; the model performs best; refresh pulls on schedule.
- DirectQuery sends DAX-compiled SQL to the source per query; refresh isn't needed but latency + source load apply.
- Live points a report at a shared semantic model in the service (no local copy).
- Dataflows centralize prep: one shared dataset refresh powers many reports.
- The on-prem gateway securely proxies compute to credentialed sources for refresh + DQ.
Connectivity modes across the Power BI product surface.
In production
- A fast interactive dashboard imports a nightly-aggregated model; SLA is 'data ≤ 1 hour from source'.
- An ops live board uses DirectQuery to a warehouse; every refresh of the page is fresh source data.
- A company standardizes dataflows so 12 teams consume one prepared dataset instead of 12 pullers.
Best practices
- Default to Import for interactive BI; use DirectQuery only when source freshness is a hard SLA.
- Pre-aggregate for DQ; sending heavy scans per visual defeats concurrency.
- Use dataflows for shared prep; gate pipelines to certified artifacts.
- Run the gateway on a managed host with least-privilege roles and monitoring.
Common mistakes
- DirectQuery against a busy OLTP database, tanking the app.
- Importing staggeringly wide raw tables and paying the refresh+memory price.
- No gateway HA: single point of failure for the 6am refresh.
- Mixing modes per table carelessly, splitting behavior unexpectedly.
Common issues
Service refresh fails; Desktop worksGateway missing or credentials scoped to the user only
Install/configure the gateway, use app-credentials with required scope.
DirectQuery dashboard is slow at 8amEvery visual queries live against a busy source
Pre-aggregate or switch hot views to Import with a freshness SLA.
Debugging tips
- Slow DQ visual → check source query cost and add pre-aggregation.
- Refresh fail to on-prem → check gateway health, credential scope, network.
- Live connection errors → verify dataset permissions in the service workspace.
Interview checkpoint
Quick quiz
A report must show source-fresh data with no refresh cycle. Best mode?
The on-premises gateway is only needed for refresh, never for DirectQuery.