How to Connect ERP and MES Data into a Fabric Lakehouse

The ERP says Line 3 produced 4,820 cases last week. The MES says 5,090. The gap is definitional, not a data-quality defect: the MES counted every unit past the end-of-line sensor, including cases later held for a label defect and units run as line-start setup pieces; the ERP counted what was confirmed against the production order and goods-received.

This is why ERP-to-MES work scoped as "integration" overruns. The connectors are the easy half — Fabric will land both systems in OneLake inside a fortnight. The hard half is making one believable number out of two systems that describe the same shift in incompatible terms.

They overlap on one object — the production order — and diverge everywhere around it.

The overlap is thin and treacherous — order number, product, plant, quantity, date — and all five are traps.

Four failures account for most ERP–MES join defects. Identifiers — ERP order numbers are commonly zero-padded fixed-width strings, while the MES operator keyed the same order without leading zeros, or the line PLC appended a sequence suffix. Timestamps — two problems wearing one coat: different time zones, and different event definitions of "start". Batch and lot — MES generates lot IDs during the run for traceability, and in many estates they never travel back to ERP, which knows only the order and the material. Units of measure — ERP keeps a base stock-keeping unit plus alternative units with conversion factors, while MES counts what the sensor counts.

For every shared entity, operations must decide which system is authoritative, the tolerance, and what happens when the two disagree beyond it. An engineer cannot make this call. Take this to the Operations Director, the plant quality lead and the ERP functional owner, and do not leave without a decision in each row.

Write the decisions down and put a name against each. That document stops the argument reopening in month four, and it is the deliverable most estates never produce.

ERP typically arrives via database mirroring, a native link, or Copy job with change data capture — Fabric supports mirroring from SQL Server, Oracle, Azure SQL, PostgreSQL, Snowflake, Cosmos DB and SAP, with more in preview. MES typically arrives via direct database extraction, a vendor API, a file drop referenced by a OneLake shortcut, or an historian bridge through Eventstream.

Most mid-market MES products run on SQL Server or Oracle underneath, which puts mirroring or Copy job on the table immediately — subject to the vendor's support position, which you check before enabling CDC on a system somebody else warrants. On latency: mirroring is near real-time (conditioned on network, change volume and gateway), Copy job is as fresh as its schedule.

Six conformed dimensions carry an ERP–MES model: work order, product, plant and work centre, shift calendar, machine or asset, and reason code. Each is built once in the Silver layer with a surrogate key and a documented crosswalk from every source identifier, so facts from ERP and MES land on the same keys.

Bronze holds both systems raw. Silver is where the crosswalk work happens: identifier normalisation (padding rules applied once, not in fourteen Power BI measures), time-zone conversion to one stated standard with local time retained alongside, dated UOM conversion factors, and the machine-to-work-centre mapping. The crosswalk tables are small and boring — a few thousand rows — and the highest-value asset in the estate, because they cannot be regenerated from the source systems.

OEE credibility is decided by the downtime reason-code taxonomy, not by the calculation. Most plants have accumulated 80 to 300 free-text or duplicated reason codes across lines, with an "Other" bucket absorbing 20–40% of recorded stops. The failure is predictable: each line was commissioned by a different integrator, so "no material" exists as NOMAT, NO-MAT, Material Shortage and Starved.

Do three things. Collapse the codes into a two-level taxonomy of category and reason — roughly 8 categories and 40–60 reasons for a mid-market plant. Map every legacy code to the new taxonomy in a crosswalk. And fix capture at the line so new stops land in the taxonomy, not back in free text — otherwise the cleanup decays within a quarter.

The Gold layer holds one fact table per grain — production confirmation, downtime event, quality result, material movement — joined to the conformed dimensions, with OEE, first-pass yield, scrap rate and schedule adherence calculated once as certified measures. ISO 22400-2 defines OEE as availability × effectiveness × quality rate, and the OPC UA machine-tool companion specification notes that planned busy time and planned run time must come from the connected MES or ERP, not from the machine.

That is this article's argument restated by a standards body: the machine knows when it was executing; the ERP knows the plan; the OEE denominator needs both. Calculate once, in the semantic model, and let Power BI, Excel and any agent read the same measure — the moment OEE exists in two places it will exist in two values and the plant will trust neither.

Reconciliation is a decision, not a calculation, and it can stall indefinitely — if the Operations Director and the plant manager disagree on whether setup pieces count as production, no architecture resolves it. MES vendor support positions bite late — enabling CDC on a warranted MES database, or reading it directly, may sit outside your support agreement. Nightly batch will not support intervention — yesterday's OEE informs a weekly review, not a shift decision.

Retrospective genealogy cannot be recovered — if MES lot IDs were never linked to ERP shipments, the lakehouse cannot invent the link for last year. A conformed model does not correct master data — duplicate material masters and routings with standard times last reviewed in 2018 replicate faithfully into OneLake. And most of this work is conversations, not pipeline development: reconciliation and modelling agreement typically consumes 50–70% of elapsed time, ingestion 20–30%.

Five questions, answerable this week, none requiring a purchase:

• Pull last month's produced quantity for one line from ERP and MES and put them side by side — the size and direction of the gap tells you which reconciliation rules matter most
• Export the distinct downtime reason codes from MES and count them — if over 100, or "Other" exceeds 20% of stopped minutes, taxonomy work is the first deliverable
• Find out what your MES actually runs on — SQL Server, Oracle, a proprietary store, or an API only — and what the vendor permits you to read
• Write down the definition of "start" in both systems; if you cannot get an unambiguous answer in a day, that is the finding
• Name the person allowed to decide which system is authoritative for produced quantity; if there is none, appoint one before scoping anything

A sensible first slice: one line, one product family, one month of history, reconciled against the plant's existing manual OEE calculation until the two agree or the difference is explained. The second line then takes days rather than weeks. We run the reconciliation workshop before the first pipeline, because that sequence produces a number the plant defends rather than disputes.