A Power BI report built on Sage 50 works fine on your own desktop. The problem comes when you publish it: the Power BI Service sits in the cloud, your Sage 50 data sits behind your firewall, and the Service has no way to reach it. Without a bridge, “refreshing” means opening the file on the machine that can see Sage, refreshing, and republishing — every time.
The bridge is the Microsoft on-premises data gateway. This guide covers installing it, testing it, and setting a refresh schedule.
It assumes you already have a working ODBC connection to Sage 50. If you don’t, start with how to create a Sage 50 ODBC connection and come back.
If you are using Sage Cloud
Not every client has a server. Where Sage Cloud is in use, the data is reached via a client PC running Sage 50 — and the gateway is still required. The architecture is the same; only the machine changes.
Two things matter more in this setup:
The PC has to be on. Whenever a refresh runs, scheduled or manual, the machine holding the DSN and the gateway must be awake and connected. A server that’s always on makes this a non-issue; someone’s desktop does not.
Sync lag sets the ceiling on how current the data is. Sage Remote Data Access (aka Sage Cloud) syncs changes between users, and the ODBC connection reads what has landed locally. The latest transaction available to your report is the latest one that has fully synced — so a refresh immediately after someone posts a batch may not include it.
The architecture
For Sage 50, we almost always recommend the direct route:
Sage 50 data → System DSN → on-premises data gateway → semantic model → reports
No staging database, no intermediate layer. Two reasons.
Shorter cycle time. Every hop between Sage and the report adds latency to how quickly a posted transaction appears in the numbers. Direct is the shortest path there is.
Fewer points of failure. Each intermediate component is another thing that can break at 6am, and another place to look when it does. For a finance team that prides itself on timeliness, that matters more than architectural elegance.
If multiple reports are needed, they can usually spawn from the same semantic model rather than each maintaining its own connection.
Larger environments are different. Where there are many users and several reports hanging off the same data, the target might be a Dataflow Gen2, a datamart, or a company data warehouse. But the architecture up to the point where the Power BI Service calls the gateway is the same in every case — so everything in this guide holds regardless of what you build downstream.
Before you start
- A working System DSN on the machine that holds the Sage 50 data. It must be a System DSN — the gateway cannot see User DSNs.
- A machine that’s always on, or at least reliably on whenever a refresh is scheduled.
- Sage 50 credentials with the right table permissions. The login used by the connection must have permission to view every table you’re reading via ODBC.
- A Power BI Pro licence for whoever owns the semantic model, and ultimately for anyone who will need access to the report(s).
- Administrator rights on the machine where the gateway will be installed.
Step 1 — Install the gateway
Download the gateway from Microsoft and choose standard mode, not personal mode. Personal mode ties the connection to one user account and can’t be shared.
Install it on the same machine as the DSN.
You will need to sign in as a user with a work Power BI account, in the tenant where the reports will live, and then register a new gateway. Store the recovery key somewhere durable — a password manager, not a note on the server. Without it you cannot recover or migrate the gateway.
Step 2 — Give the gateway access to the Sage data folder
This is the step that catches almost everyone. In our experience it’s the problem about half the time.
The gateway runs as a Windows service account called NT Service\PBIEgwService. That account needs read permission on the ACCDATA folder holding your Sage 50 company data. Your own Windows account having access is not enough — the service is not you.
- Find the ACCDATA folder (in Sage 50: Help → About → Program Details → Data Directory)
- Right-click the folder → Properties → Security → Edit → Add
- Enter
NT Service\PBIEgwServiceand click Check Names - Grant read access and apply
If you’d rather not stop to arrange this now — permissions often need someone else’s involvement — run the test in Step 3 first and come back here if it fails.
Step 3 — Test the connection through to Sage, before you build anything
Do this while you’re still setting up the gateway — particularly if you’ve had to book an external IT resource to help, since it’s the moment when everyone who can fix a problem is still available.
The temptation is to test by publishing a report. Don’t: a complex report and semantic model takes time to publish, bind and refresh, and when it fails you’re left guessing which of half a dozen things went wrong.
Far quicker is a single query that asks Sage one trivial question. Create a dataflow, choose a blank query, select your gateway, and use:
let
Source = Odbc.Query("dsn=<<YourCompanyDSN>>", "SELECT NAME FROM COMPANY")
in
SourceIf the connection is working all the way through to Sage, this returns a single-row, single-column table containing the company name. If it isn’t, you know the problem is in the gateway, the DSN or the permissions — not in your report.
While Gen1 dataflows are still available, that’s what we’d use for this. If they aren’t, spin up a trial premium workspace — one or two clicks — run the same test with a Gen2 dataflow, and delete the workspace afterwards.
Step 4 — Publish the report and bind it to the gateway
Publish your report from Power BI Desktop to a workspace.
You don’t need to create the ODBC connection in the Service beforehand — publishing creates the semantic model, and the data source comes with it. What’s left is to map it to the gateway: open the semantic model’s settings and find the gateway connection section, then point the data source at the gateway you installed.
Step 5 — Set the refresh schedule
In the semantic model settings, switch on scheduled refresh and add your times.
On Power BI Pro you can refresh up to eight times a day.
Every refresh is a full refresh, and there’s no point trying to make it otherwise. Incremental refresh depends on query folding to filter at source, and the Sage 50 ODBC connection doesn’t support folding on dates or transaction numbers. Configuring incremental refresh against Sage 50 gains you nothing: it’s everything or nothing on each run.
Set the failure notification email to someone who will act on it. A refresh that has been quietly failing for a fortnight is worse than no refresh at all, because people carry on trusting the numbers.
Show users how current the data is
A scheduled refresh raises a question for anyone reading the report: how old is this?
Include AUDIT_JOURNAL[RECORD_MODIFY_DATE] in the data you pull from Sage, and surface the maximum value as a timestamp on the report. It tells the reader the date and time of the last transactional edit in Sage that the report has picked up.
You can also surface the last transaction number into the report, as a cross-check against the number in the “About” page in Sage 50. This can be useful if you are trying to troubleshoot issues relating to Sage Cloud syncing.
It’s a small addition that does a lot for confidence in the report.
Budgets, forecasts and chart of account mappings
Almost every Sage 50 reporting project needs data that doesn’t come out of Sage.
Budgets and reforecasts. Sage can hold budgets, including departmental budgets, but managing them there is cumbersome and you can’t hold multiple budgets, reforecasts or scenarios. Most companies therefore keep them in Excel — which makes those spreadsheets a data source in their own right.
Chart of account mappings. Often, a motivating factor behind adopting Power BI is if Sage can’t express the reporting structure that a management P&L needs. We provide a configurator tool to define any custom chart of accounts you want, which produces a .json file that the report consumes directly — a topic for a separate article.
Where you put these files matters more than it first appears. Store them in SharePoint or OneDrive rather than on a network drive:
They refresh without the gateway. SharePoint and OneDrive are cloud sources, so the Power BI Service can reach them directly. Only the Sage connection needs the gateway.
No local file locks. A budget file on a network drive that someone has open in Excel can block the refresh. The same file in SharePoint doesn’t, so the refresh doesn’t fail because the FD happened to be editing next year’s budget at 7am.
Where dataflows fit
The main reason to use a dataflow is access. Report developers frequently don’t have a direct connection to the on-premises Sage data — an external consultant, or an analyst working from a different site — and a dataflow gives them a way to build against Sage data without one. Secondarily, several reports can share a single connection rather than each maintaining its own.
That position has changed. In April 2026 Microsoft moved Dataflow Gen1 into a Legacy state — no further innovation, with retirement dates still to be confirmed. Existing Gen1 dataflows continue to work, and Premium capacity customers were promised at least twelve months’ notice. Gen2 requires a Fabric capacity, which Power BI Pro does not include.
For a finance team on Pro licences — which is most Sage 50 clients — that leaves no confirmed like-for-like path. Microsoft has said it intends to introduce Gen2 paths for Pro and PPU scenarios, but at the time of writing it isn’t clear whether that means included in Pro or at additional cost.
Our position: dataflows remain useful for development and testing, as in Step 3 above, and we still use them that way. But we don’t recommend building a production Sage 50 architecture on Gen1 today. The direct connection described here works on Pro, has no deprecation exposure, and is simpler to support.
When the refresh fails
Work through it in this order.
- Read the error message in the Power BI Service, especially if the refresh has worked before and has now stopped. It often names the problem outright and saves you the rest of the list.
- Check the gateway status in the Service. Offline usually means the service didn’t start — commonly after a server restart. This is quick, and you can check it from anywhere.
- Check the ACCDATA permissions for
NT Service\PBIEgwService. - Test the DSN on the machine itself, if you can. Note that many Sage servers don’t have Excel or Power BI Desktop installed, so this often isn’t available — which is another reason the dataflow test in Step 3 is worth keeping to hand.
Where to go from here
At this point you have Sage 50 data refreshing automatically into reports your team can open from anywhere. The harder work is what the reports say — a data model that handles fiscal periods properly, a P&L that ties to Sage, and something the wider management team will actually use.
If you’d rather build it yourself, our Power BI finance templates and training for accountants are at accountinginsights.pro.
If you’d rather not, that’s what we do. Book a 30-minute call and we’ll tell you honestly whether we’re the right fit.