Power BI connects to an ERP either directly through a built-in connector, through an on-premises data gateway, or through an intermediate database or API layer. The choice depends on where the ERP runs, how fresh the data must be and how often the dataset refreshes. A well-designed star-schema model and realistic refresh schedules matter as much as the connection itself.
Key takeaways
- An on-premises ERP usually needs an on-premises data gateway before the Power BI service can refresh it.
- Querying the production ERP database directly puts reporting load on the system that runs your transactions.
- A star-schema model with measures written in DAX is easier to maintain than a flat table of ERP exports.
- Refresh schedules must respect the documented refresh limits for your licence and the batch windows of the ERP.
- Every connection needs a named owner, a dedicated service account and a documented failure procedure.
What are the ways to connect Power BI to an ERP?
There are three practical routes: a native connector or generic connector to the ERP database or API, a staging database that receives scheduled ERP extracts, or file and API exports that Power BI reads on a schedule. Each route trades setup effort against freshness, control and the load placed on the ERP.
Direct connections are the quickest to build. They work well when the ERP is cloud-hosted, exposes a supported API and the reporting volume is modest. The failure mode is coupling: a schema change, a renamed field or a changed API version breaks every report that depends on it.
A staging database adds a layer of work but isolates reporting from the transactional system. Extracts run at controlled times, transformations happen in one place and reports keep working while the ERP is patched. Its weakness is latency, because the data is only as current as the last successful load.
API exports depend on the rate limits and pagination rules of the ERP. Large histories can take a long time to pull, and a single failed page can leave a gap that is hard to notice. Check the vendor's own API documentation before you commit to this route.
When do you need an on-premises data gateway?
You need a gateway when the Power BI service must refresh data from a source it cannot reach over the public internet, such as an ERP database on your own network. Sources that the service can reach directly, typically cloud-hosted ones with a supported connector, usually do not need one.
The gateway is software installed on a Windows machine inside your network. Microsoft documents how the on-premises data gateway works and what it needs; according to Microsoft's on-premises data gateway documentation, it connects outward to the service, so the design does not normally require opening inbound firewall ports. Confirm this with your network team against the current page before you rely on it.
Two gateway modes matter. A standard gateway serves shared datasets and multiple users, and it is the mode you want for ERP data that a team relies on. A personal gateway serves only one user and cannot support scheduled refresh for shared content, so it is unsuitable for production reporting.
Gateway failures are usually operational rather than technical. Common causes include a server that sleeps or restarts, a pending update that waits for a reboot, a rotated database password, or a VPN that drops overnight. A gateway cluster on two machines reduces the single point of failure, at the cost of managing both.
- Use a dedicated service account with read-only access to the reporting tables.
- Keep the gateway machine on a fixed schedule with monitoring and alerts for offline status.
- Document who rotates the database credentials and how the gateway data source is updated afterwards.
Designing the data model for ERP data
ERP tables are normalised for recording transactions, not for analysis. A reporting model reshapes them into fact tables, such as sales lines, purchase lines or journal entries, and dimension tables, such as customer, product, account, date and company. This star-schema structure gives Power BI a predictable way to filter and aggregate.
Keep transformations in the query layer simple and foldable where the source supports it. Power Query steps that the source can execute are pushed down to it, which reduces the data transferred. Steps that cannot fold, such as complex merges of large tables, run on the refresh machine and lengthen every refresh.
Measures belong in DAX rather than in calculated columns. Calculated columns are stored in the model and grow it, while measures are evaluated at query time. Create a proper date table and mark it as a date table, or time intelligence functions will behave unpredictably.
Multi-company ERP setups need row-level security designed early. Map users to companies or entities through a security table, then test each role with a sample user before publishing. Retrofitting security into a model with many reports is slow and error-prone.
How often can a dataset refresh from an ERP?
The refresh frequency is limited by your Power BI licence and by the refresh rules that Microsoft documents for the service. Check the Power BI scheduled refresh documentation for the current limits, because they change, and design the schedule against your slowest source rather than the maximum allowed.
Refresh duration is the constraint most teams hit first. Full refreshes of large transaction histories can exceed the time allowed and fail. Incremental refresh, which reloads only recent partitions, reduces the work per refresh, but it requires a reliable date column and a stable key for each record.
Schedule around the ERP's own batch windows. Month-end closing, nightly inventory runs and payroll jobs can lock tables or slow queries, so a refresh that overlaps them may time out or return incomplete figures. Agree the window with the people who run the ERP.
Import mode gives fast visuals and full model features, but data is only as current as the last refresh. DirectQuery gives near-current data, but every visual becomes a query against the source, so the ERP must handle that load and some modelling features are restricted.
| Criterion | Import mode | DirectQuery | Staging database |
|---|---|---|---|
| Data freshness | Set by the refresh schedule | Near current | Set by the load schedule |
| Load on the ERP | Concentrated in refresh windows | Continuous, with each visual | Concentrated in load windows |
| Model features | Full | Restricted | Full, after load |
| Effort to maintain | Moderate | Moderate to high | Higher, with extra infrastructure |
Hypothetical examples of ERP reporting setups
Here are two hypothetical scenarios:
In the first, an operations lead needs stock and order-status reports from an ERP that runs on a server in the company network. The team installs a standard gateway cluster, creates a read-only service account and models orders and stock movements as facts. Reports refresh in the early morning, before the operations shift, and an alert tells the IT lead if the gateway goes offline.
In the second, a finance team uses a cloud-hosted ERP with a documented API. Instead of connecting to the API directly from every report, the team exports journal and invoice data into a staging database each night, applies the transformations once and points several Power BI datasets at the staging layer. A schema change in the ERP then breaks only the staging job, which is easier to repair.
What should you check before going live?
- The connection route is written down, with the owner of each source.
- The gateway runs on a monitored machine, with a service account and a documented credential rotation process.
- The model follows a star schema, uses measures for calculations and has a marked date table.
- Row-level security has been tested with at least one real user per role.
- Scheduled refreshes sit outside the ERP's batch windows and within the limits in the current Microsoft documentation.
- A named person receives failure alerts and a runbook covers the most common failures.
If you want an outside review of an existing setup, our ERP and CRM integration service covers this kind of design work, and our data and business intelligence articles cover modelling in more depth.
Frequently Asked Questions
Can Power BI connect to an ERP without a gateway?
Yes, if the Power BI service can reach the ERP directly, for example a cloud-hosted ERP with a supported connector or API. An ERP on your own network normally needs an on-premises data gateway for scheduled refresh in the service. Desktop authoring can connect without one, but shared refresh depends on how the data source is reachable.
Does an on-premises data gateway need inbound firewall ports?
Usually not. Microsoft's documentation describes the gateway as making outbound connections to the service, so the design normally does not require inbound ports. Confirm the current requirements on the official page and agree the outbound rules with your network team before installation.
Should Power BI query the production ERP database directly?
It is usually better not to. Direct queries add reporting load to the system that runs your transactions, and schema changes break reports without warning. A staging database or a controlled extract isolates reporting, schedules load work outside peak hours and gives you one place to repair transformations when the ERP changes.
Why do scheduled refreshes fail after working for weeks?
The most common causes are changed credentials, a gateway that went offline, a growing dataset that now exceeds the refresh time allowed, or an overlap with an ERP batch job. Check the gateway status and the refresh history first. Then compare the duration trend and the schedule against the ERP's batch windows.
Conclusion
Connecting Power BI to an ERP is mostly a series of decisions about where the data comes from, how it reaches the service and how the model is shaped. A gateway solves reachability, a staging layer solves isolation, and a star-schema model with a realistic refresh plan solves reliability. Write those decisions down before building, and test each failure path. If you want help reviewing or designing the connection, request a project consultation at the Rui Codex contact page.
Sources
- What is an on-premises data gateway? - Microsoft Learn
- Scheduled refresh in Power BI - Microsoft Learn