Module 8: Performance, security and sharing · Lesson 8.4 · about 14 min
Scheduled refresh and gateways
Keep published reports up to date automatically. Set scheduled refresh, connect on-premises data through a gateway, use incremental refresh for big tables, and fix the usual refresh failures.
Module 8 · about 14 minutes · read first: 8.3. You’ll be able to keep reports fresh and fix failed refreshes.
1. Why this matters
Stale data destroys trust faster than anything. A BI developer is responsible for refreshes running, and for noticing first when they don’t.
2. Import, DirectQuery and refresh
- Import (the default): data is copied into the model; reports are fast; data is as fresh as the last refresh.
- DirectQuery: every visual queries the source live; always current, slower, with some DAX limits.
- Direct Lake (Microsoft Fabric): reads Delta tables in OneLake directly, close to import speed without copying.
For import, set scheduled refresh on the semantic model in the Service: Settings > Refresh, up to 8 times a day with Pro (48 on a Premium or Fabric capacity). Turn on failure emails.
3. Gateways
Cloud sources (Azure SQL, SharePoint Online, many SaaS apps) need only credentials. Sources inside the company network need an on-premises data gateway: a Windows service installed on an always-on machine inside the network, managed by IT. In the Service you map each data source in the model to a gateway connection.
4. Incremental refresh
For big fact tables, define parameters RangeStart and RangeEnd in Power Query, filter the date column with them, and set an incremental refresh policy (for example, store 5 years, refresh the last 10 days). The Service then reloads only recent partitions. It needs a date column and a source that can filter by date efficiently (query folding).
Principle
Principle: a refresh replays your Power Query steps against the source as it is now. Anything that changed in the source (a renamed column, a new value type, a moved file, an expired password) can break a step, so build steps that are robust (Lesson 2.1) and monitor refresh history.
Try it: list three source changes that would break a refresh. (A renamed column, a file path change, credentials that expired.)
5. Worked example
Monday 7 AM, the overview shows Friday’s data. Refresh history says “Data source error: the credentials are invalid”. The service account’s password was rotated over the weekend. Update the credentials in the gateway connection, refresh now, and send a note to the report’s readers. Then ask IT to tell the BI team before rotations, and add the refresh time to the report (“Data as of 2025-12-08 06:00”) so readers can see freshness.
6. Practice
- Your source is an Excel file on someone’s desktop. Why is that a problem for scheduled refresh, and what’s better?
- When would you choose DirectQuery over import?
- Write a measure for “Data as of” using the latest date in Sales.
Answers
- The file is only reachable when that computer is on and a gateway can see it. Put it in SharePoint or OneDrive for work, or load it into a database.
- When data must be near real time or is too big to import, and the source is fast enough.
Data As Of = "Data as of " & FORMAT(MAX(Sales[Date]), "yyyy-MM-dd").
Check yourself
Sources
- Microsoft Learn, Data refresh in Power BI: https://learn.microsoft.com/power-bi/connect-data/refresh-data
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.