Guide · 8 min read
Connecting your ERP to Microsoft Fabric
Getting data out of the ERP is usually the hardest part of a Fabric project, and the part that decides the timeline. Here is how it is done.
Short answer
How do you get ERP data into Microsoft Fabric?
There are four common routes: the ERP's REST API, a direct database connection (through an on-premises data gateway if the ERP runs on your own servers), scheduled file exports, and controlled Excel uploads for data the ERP cannot provide any other way.
Most projects use more than one. The aim is to land the data untouched in a bronze layer, the first layer of a medallion architecture, then clean and shape it in Fabric, so the ERP itself is never changed.
The four routes
Ways to get data out of an ERP
REST API
The cleanest route for cloud ERPs. Fabric pipelines or notebooks call the API on a schedule and load only what changed. The limit is what the API exposes and how fast it responds.
Database connection
For ERPs on SQL Server or similar, Fabric reads the database directly, through an on-premises data gateway if the server is on site. Fast and complete, but you need to understand the ERP's table structure, and it must be read-only.
Scheduled exports
Where there is no usable API or database access, the ERP exports reports or files to SharePoint on a schedule and Fabric picks them up.
Controlled Excel uploads
For data the ERP cannot provide, such as budgets, mappings or a missing ledger, a locked-down SharePoint folder with set templates keeps manual input controlled and auditable.
ERP notes
What we have learned on specific ERPs
| ERP | Route we use | Watch out for |
|---|---|---|
| Jim2 | REST API, plus controlled uploads for gaps | General ledger transactions are not fully exposed through the API, so P&L and balance sheet may need an interim upload |
| ECi M1 | Database or API through an on-premises data gateway | On-premise installs need a gateway; no cloud migration is required |
| SAP | Read-only extraction of jobs, parts and costs | Agree read-only access and security early; write-back is a separate decision |
| MYOB Acumatica | REST API | Plan for API rate limits on large history loads |
When the API is not enough
Do not let one missing table stop the project
It is common for an ERP API to cover sales, stock and jobs but not the full general ledger, or to time out on some records. Rather than wait, we load what is available, use a controlled upload or lookup table for the gap, and switch to the API when the vendor fixes it.
Source speed matters too. On one project, a fix to the ERP's API is expected to cut the nightly load from about an hour to 20 to 25 minutes, more than any change on the Fabric side would.
Before you start
Questions to answer first
- Is the ERP cloud-hosted or on your own servers?
- Does it have a documented API, and does it cover the data you need?
- Can we get read-only credentials, and who approves them?
- Which data is missing, and who will own the interim uploads?
- How fresh does the data need to be: overnight, or during the day?
Questions we get asked
Frequently asked questions.
Do we need to move our ERP to the cloud to use Microsoft Fabric?
No. On-premise ERPs connect through an on-premises data gateway installed on your network, so the ERP stays where it is.
Will connecting Fabric slow down our ERP?
Not if it is done properly. Loads are scheduled outside busy hours, only changed data is loaded where possible, and access is read-only.
Does Fabric write anything back to our ERP?
Not for reporting. Fabric reads from the ERP. Writing back, for example creating a job from a won quote, is a separate integration with its own approval.
Which ERPs can be connected to Microsoft Fabric?
Almost any ERP with an API, a database or the ability to export files. We have worked with Jim2, ECi M1, SAP, MYOB Acumatica, MYOB EXO, Epicor and Ostendo.
Next steps
Tell us which ERP you run.
In a 30-minute call we'll tell you which route fits your ERP, what data is likely to be missing, and how long the first load would take.
Related reading
- Microsoft Fabric consultants
- What is an API? A plain-English guide
- System integration
- Production, inventory and job-cost reporting
Prefer to talk it through? A 30-minute call is usually enough.
Bring us the process that is slowing you down.
A 30-minute discovery call is enough to work out whether automation is the right answer, and what it would take.