Skip to content
Tutorials Center
My learning

Topics

Lesson 45 of 56 ยท Service, Sharing and Security

Scheduled Refresh and Gateways

Keep published Power BI data current: scheduled refresh, the on-premises data gateway (personal vs standard), credentials, refresh failures and dataflows.

Importing data copies it. The moment the copy is made, it starts to age. A report published on Monday still shows Monday's numbers on Friday unless something refreshes the semantic model. In the Power BI service that something is a scheduled refresh, and when the data lives on a server the service cannot reach from the internet, it is a gateway.

This lesson explains how scheduled refresh works, what the on-premises data gateway does, the difference between personal and standard mode, how credentials are stored, why refreshes fail and what dataflows are. All of this is a feature of the hosted service and your own network, so the steps are descriptions and any configuration is read-only. The runnable Python examples are stand-ins that model a refresh schedule and a failure log, so the ideas become concrete.

What a refresh does

A refresh re-runs the model's Power Query queries against the data sources, loads the new rows into the semantic model and replaces the old data. The report itself is not touched; its visuals simply read the new numbers. Import tables are refreshed. DirectQuery and Live Connection models do not hold data, so they do not need a data refresh (see Import, DirectQuery and Live Connection), although cached tile values on dashboards can be refreshed on a schedule.

Refresh can be started in three ways:

  • Manual: Refresh now in the service, or Refresh in Desktop for the local copy.
  • Scheduled: set days and times in the semantic model's settings.
  • Programmatic: by the REST API, Power Automate, or Fabric pipelines, often right after a data load finishes.

Limits depend on the licence. Shared (Pro) workspaces allow a smaller number of scheduled refreshes per day (typically eight) than Premium or Fabric capacities (typically up to 48), and a single refresh has a time limit. Check the current numbers for your tenant before you plan.

What a refresh needs

A schedule Days, times and a time zone.
Credentials Stored per data source.
A route Direct to the cloud, or via a gateway.
Failure alerts Someone must be told when it breaks.
1 / 2

Why gateways exist

Cloud sources such as Azure SQL Database, SharePoint Online or Salesforce are reachable from the service over the internet, so no extra software is needed. But a SQL Server in your office, an Excel file on a network share or an Oracle database in a data centre sits behind a firewall that does not accept connections from the internet.

An on-premises data gateway is a program you install on a machine inside your network. It makes an outbound connection to the cloud (so you do not open inbound ports), waits for refresh requests, runs the query against the local source and sends the results back, encrypted. Gateways also serve DirectQuery and live connections to on-premises models.

sequenceDiagram
    participant S as Power BI service
    participant G as Gateway (on-premises)
    participant D as SQL Server
    S->>G: Refresh request (queued in the cloud)
    G->>D: Run Power Query query
    D-->>G: Rows
    G-->>S: Encrypted data
    S->>S: Replace the model's data

The gateway pulls the request out of the cloud, talks to the local source and sends only the results back.

Personal versus standard mode

There are two kinds of gateway.

On-premises data gateway (standard) On-premises data gateway (personal mode)
Users Shared by many people and models One user only
Needs A server that is always on Any computer (often a laptop)
Supports Import, DirectQuery, live connection Import only
Management Central: admins add data sources and users None: the owner manages it
Use for Production reports Quick tests and personal use

A standard gateway is installed once on a dedicated machine, registered to your tenant and managed in the service. Admins add data sources to it (a server name, a database, credentials) and decide who may use each one. Production should also use a gateway cluster, which is several gateway machines acting as one so a single reboot does not stop refreshes. A personal gateway runs only while its owner is signed in and the machine is on, and it cannot be shared, which is why it is only suitable for experiments. Newer options, such as virtual network (VNet) data gateways managed by Microsoft, are also available for Azure-based private data, without a machine for you to maintain.

Do not install a standard gateway on a laptop. It sleeps, moves between networks and goes offline when its owner travels. Use an always-on server, preferably close to the data source for lower latency, and make sure its service account can reach everything the queries use.

Credentials and privacy levels

Every data source in a refresh needs credentials: a way to prove the service may read it. In the semantic model settings, under Data source credentials, you choose an authentication method for each source, for example Basic (user name and password), Windows, OAuth2 or an account key. With a gateway, the credentials are stored encrypted in the cloud and sent to the gateway only when needed; they are not stored in your .pbix file.

Things to get right:

  • Use a service account with read-only access, not a person's account. When someone leaves or changes a password, refreshes that use their login fail.
  • With OAuth2, tokens expire, and the account's sign-in may be refreshed only by that user. Cloud sources that use expiring tokens are a typical cause of "credentials are no longer valid" errors.
  • The data source in the service must match the one in Desktop exactly (same server and database names), and parameters in the query (see Data Source Settings, Privacy Levels and Parameters) must not change them in ways the gateway cannot match.
  • Privacy levels (Public, Organizational, Private) still decide whether data from two sources may be combined in one query. They are set per source and can block a refresh with a "formula.firewall" message.

Setting up a scheduled refresh

In the service, open the semantic model's Settings and work through the sections:

  1. Gateway and cloud connections: pick the gateway and map each source to its data source entry.
  2. Data source credentials: sign in or confirm.
  3. Refresh: switch on Keep your data up to date, pick the frequency, time zone and times, and add people to the failure notification list.

Choose times when the source is quiet and just after upstream loads finish. If the sales database finishes its nightly load at 02:00, a refresh at 01:30 reads half-finished data. Because refresh runs through the cloud, the model's own size and the transformation work you do in Power Query decide how long it takes; query folding and incremental refresh (covered in Query Folding, Incremental Refresh and Query Performance) are the two main tools for making it quicker.

Power BI also disables the schedule automatically after several consecutive failures, or after a long period (typically two months) when nobody opened the report. Do not assume a schedule you set a year ago is still running.

Why refreshes fail

Most failures come from a short list.

  • Credentials expired or changed. Fix: update them in the model settings, preferably by a service account with a non-expiring password.
  • The gateway is offline. The machine rebooted, the service stopped, or the network changed. Fix: monitor the gateway, use a cluster and keep it updated (old gateway versions are not supported).
  • The source changed. A column was renamed or removed, and a Power Query step now fails with "The column ... of the table wasn't found". Fix: edit the query, and consider tolerant steps for optional columns.
  • Timeouts and size. The refresh runs longer than the limit, or the model outgrows its memory limit. Fix: reduce data, fold queries and use incremental refresh.
  • Data privacy or firewall errors. Two sources combine in a way the privacy levels do not allow. Fix: set privacy levels correctly, or restructure the query so one query reads each source.
  • Data source mismatch. The gateway's data source definition does not match the model's. Fix: use identical server and database names, with no extra spaces or alias differences.

When a refresh fails, the service emails the owner and anyone on the notification list, and Refresh history in the settings keeps the error message and the request ID. The message usually names the query and the step. Read it before changing anything: it normally points straight at the cause.

Dataflows in one page

A dataflow is Power Query running in the cloud. Instead of every report having its own copy of the "clean customers" query inside a .pbix file, you build the query once, in the service, in a workspace; the result is stored as tables (in Microsoft's storage) and refreshed on its own schedule. Many semantic models can then read the same table.

Dataflows help when:

  • The same cleaning logic is needed by several models, and you want one definition.
  • A slow, heavy source should be read once, by one refresh, rather than by ten.
  • You want to separate preparation (dataflow) from modelling and measures (semantic model).

They add one more thing to refresh and monitor: if the dataflow refresh fails or is late, every model that reads it gets old data even though its own refresh succeeds. Order the schedules so a dataflow finishes before the dependent model starts.

Stand-in: a refresh schedule and its log

The service stores a schedule and a history. This example models both: it generates the times a daily schedule would run in a day, and then reads a small history table to find which refreshes failed and why. The data is invented; the point is how you read a failure log.

Python Stand-in: schedule times and a refresh history

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last lines show why a repeated failure is serious: a long streak is what makes the service stop trying. The real rule has its own threshold, so the example only counts the streak.

Stand-in: stale data and the order of refreshes

When a dataflow feeds a model, the order of the two refreshes matters. The second example simulates a source, a dataflow table and a model, and shows what the report sees if the model refreshes before the dataflow does.

Python Stand-in: dataflow before model

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The report is correct only after the second model refresh. In practice you avoid this by scheduling the dataflow well before the model, or by triggering the model refresh from the dataflow's completion with Power Automate or a pipeline.

Good habits

  • Keep one standard gateway cluster for production and document who owns it.
  • Use service accounts and record where each credential is used.
  • Add two people to failure notifications, so a holiday does not hide a failure.
  • Schedule refreshes after upstream loads, and keep the model small enough that it finishes quickly.
  • Check Refresh history after any change to a source, a query or a parameter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a SQL Server inside your company network usually need a gateway for scheduled refresh?

    The service has no route into a private network. The gateway sits inside it, connects out to the cloud, runs the queries locally and sends the results back.
  2. 2. Which statement about personal-mode and standard gateways is correct?

    A standard gateway runs on an always-on server for production and many users. Personal mode serves one person, supports only Import and is suited to quick tests.
  3. 3. A refresh fails with the message that a column was not found in a table. What is the most likely cause?

    Power Query steps refer to columns by name. If the source renames or drops one, the step fails on refresh and the history shows which query and step.
  4. 4. What is a dataflow?

    A dataflow moves Power Query into the cloud: one definition, one refresh, and shared output tables for several semantic models.

Summary

  • A scheduled refresh re-runs the Power Query queries and replaces the Import data; DirectQuery and live models do not need a data refresh.
  • An on-premises data gateway sits inside your network, connects outward and relays queries to private sources.
  • Use a standard gateway (preferably a cluster on an always-on server) for production; personal mode is single-user and Import only.
  • Credentials are stored per data source, ideally for a read-only service account; privacy levels and exact source names also affect refresh.
  • Typical failures: expired credentials, offline gateway, changed sources, timeouts and privacy errors. Read Refresh history first.
  • Dataflows share cleaned tables between models, so schedule them before the models that read them.
  • The Python examples are stand-ins for schedules and logs, not the real service.

Next, decide who may see which rows in Row-Level Security.