Power BI data refresh: the on-site SQL Server database connects through an encrypted gateway to the Power BI dashboard in the cloud, and reports are updated regularly

Power BI Data Refresh: Keeping Reports Current with a Gateway

Why are Power BI reports not updating? A 6-step SME guide to the on-premises data gateway, refresh scheduling, failure alerts and security.

In short: Power BI reports run in the cloud; to reach an on-site SQL Server or Logo ERP database they need a bridge, the on-premises data gateway. When reports are not up to date in the morning, the cause is usually a switched-off gateway machine, an expired password or a refresh schedule nobody really planned. Installing the gateway on the right machine, scheduling refreshes around business hours and getting notified about failures fixes the problem for good.

In many SMEs the first weeks with Power BI are exciting: a sales dashboard is built and management starts checking the numbers every morning. Then, some time later, someone says: “These figures are not from yesterday, they are from last week.” Refreshes have been failing for days without anyone noticing. The computer running the gateway was shut down, the person who built the report left the company, or the SQL user’s password was changed. In this article we explain how Power BI data refresh works, why reports stop updating and how to set up a reliable refresh routine step by step.

Contents

What are Power BI data refresh and the gateway?

When a report built in Power BI Desktop is published, the report and its data model (the semantic model) move to the Power BI service, Microsoft’s cloud. If the model uses Import mode, the data is frozen as it was at the moment of publishing. To keep the numbers current, the cloud has to reconnect to the source at set intervals and load the data again. This is called data refresh.

If the source is already in the cloud (for example an Excel file in SharePoint or a cloud database), the refresh can run directly. But if the source is a SQL Server or Logo database on a company server, the cloud cannot reach it on its own. This is where the on-premises data gateway comes in: a small service installed on a Windows machine in the company network. It receives the refresh request from Power BI, connects to the local database, runs the query and sends the result to the cloud over an encrypted connection. Because the gateway opens outbound connections, you usually do not need to open inbound ports on the firewall.

How the data gets into the model decides how much the refresh matters:

Connection modeWhere does the data live?FreshnessBest suited for
ImportInside the Power BI modelAs fresh as the last refreshMost SME reports; fast dashboards
DirectQueryIn the source databaseLive query on every viewWell-tuned databases where real-time data is needed
CompositePartly in the model, partly at the sourceDepends on the tableLarge transaction table live, dimension tables imported

With both Import and DirectQuery, access to an on-site source goes through the gateway. With DirectQuery a stopped gateway means the report does not open at all; with Import the report opens but shows old data. The second case is more dangerous, because nobody sees an error.

Why are Power BI reports not updating?

Most refresh problems we see in practice come down to a few recurring causes:

  • The gateway machine is switched off: the gateway was installed on an employee’s laptop; when it is shut down in the evening, the overnight refresh fails.
  • Personal mode: the gateway runs on behalf of a single user only. When that person is on leave or leaves the company, nobody owns the refresh.
  • Credential problems: the password of the SQL or Windows account used for the data source has changed, or the account is locked or disabled.
  • Timeouts and load: the model is larger than it needs to be, the full history is reloaded every time and the refresh exceeds the allowed duration. If the ERP is busy at the same time, the database slows down as well.
  • Structural changes at the source: a column was renamed or a view was dropped; the query now fails.
  • The schedule switches itself off: Power BI can disable scheduled refresh for models that fail repeatedly or have not been viewed for a long time.

What these causes have in common is not technology but ownership: nobody decided who owns the refresh and who gets notified when it fails.

A reliable Power BI refresh routine in 6 steps

Power BI refresh schedule: refreshes placed outside business hours across the day, with an alert and a retry when a refresh fails

1. Install the gateway on the right machine in standard mode

Install the gateway on an always-on, regularly patched server or virtual machine, not on a personal computer. A separate server on the same network is often a better choice than the database server itself. Choose standard mode; it serves several reports and users and is managed centrally. Store the recovery key created during setup in the company password vault; you will need it when moving or reinstalling the gateway.

2. Give more than one person ownership of the gateway

Define at least two administrators for the gateway in the Power BI admin settings. Even if the report author leaves, the gateway and its data sources keep an owner. For critical reports, installing a second node and forming a gateway cluster keeps refreshes running while one machine is under maintenance.

3. Define data sources with a read-only account

For the SQL connection on the gateway, create a separate database user that is used only for reporting and can read only the required tables or views. Do not use the admin account of the Logo database or an employee’s personal account. If you have a password rotation policy, put this account on the calendar; when the password changes, the definition on the gateway has to be updated the same day.

4. Make the model refresh-friendly

Load only the columns the report actually uses, and use views prepared on the SQL side instead of raw transaction tables. For sales and stock movements that grow over the years, set up incremental refresh so that only the most recent days are reloaded each time. A well-designed star schema speeds up both the reports and the refresh; see our Power BI star schema guide for details.

5. Plan the schedule around business hours

Your licence limits the number of daily refreshes: Power BI Pro allows up to 8 scheduled refreshes per day, while Premium capacity and Premium Per User allow more. Instead of spreading them at random, align them with how the business works: one refresh before the working day starts, one around lunch and one after the end-of-day closing is enough for most SMEs. Keep heavy refreshes away from peak ERP hours and from the nightly backup window.

6. Turn on failure notifications and monitoring

Enable failure notifications in the scheduled refresh settings and send them to a team mailbox rather than a single person. Add a small “data as of” card to your reports so users see at a glance which date the figures belong to. Include the gateway server in your monitoring system along with the other servers, so that disk, memory and service status are tracked. Our article on server and network monitoring shows how.

Security, data protection and risks

The gateway is a door between your on-site database and the cloud, and it matters what that door opens onto. Key points to watch:

  • Least privilege: the gateway account should only be able to read the data reporting needs; it should have no write or delete rights.
  • Personal data: fields such as customer names, phone numbers, national ID numbers or salaries should not be loaded into the model unless the report truly needs them. Personal data moved to the cloud and cross-border transfers should be reviewed with your legal adviser under the data protection law that applies to you (in Turkey, the KVKK).
  • Access: whose screens does the refreshed data end up on? Workspace permissions and, where needed, row-level security (RLS) should be planned together.
  • Gateway server maintenance: the gateway software receives regular updates and very old versions can fall out of support. Operating system patches on that server should follow the same schedule as your other servers.
  • Single point of failure: if a single gateway machine stops, every report is affected. Critical reports need a cluster or at least a documented reinstallation procedure.

Summary and checklist

A reliable Power BI refresh routine depends on getting a few simple decisions right from the start:

  • Does the gateway run in standard mode on an always-on server?
  • Is the recovery key stored in the password vault, and are there at least two gateway administrators?
  • Are data sources defined with a read-only account that is not tied to a person?
  • Is incremental refresh used for large tables?
  • Are refresh times planned around working hours, ERP load and the backup window?
  • Do failure notifications go to a team mailbox, and do reports show the data date?

We covered the connection, model and first dashboard for Logo or other ERP data in Power BI reports from Logo data. As part of our business intelligence (BI) consulting, ÇAP Teknoloji helps with gateway setup, refresh planning and health checks of existing reports.

Frequently Asked Questions

Can I install the gateway on the SQL Server machine itself?

Technically yes, but the gateway uses CPU and memory during refreshes. If the database server is already busy, a separate server or virtual machine on the same network is the healthier choice.

Do reports fed from Excel files also need a gateway?

Yes, if the file sits on a computer or an internal file share. If it is stored in OneDrive or SharePoint, Power BI can connect to it directly and no gateway is needed.

The report needs to refresh more often during the day. What should I do?

First clarify whether you really need near real-time data. If so, a licence with a higher refresh limit, DirectQuery for critical tables or a composite model are options. Because they put more load on the database, decide together with a performance test.

How do I know a refresh has failed?

The refresh history in the model settings of the Power BI service shows the result and error message of every attempt. If you enabled failure notifications, you also receive an email. A data date card in the report lets end users spot the problem too.

ÇAP Teknoloji, T.C. Sanayi ve Teknoloji Bakanlığı Teknogirişim Rozeti belgesi