How to Structure a Fabric Lakehouse for a Multi-Plant Manufacturer

The group operations director asks which plant had the worst OEE last month. Four days later there are three answers, and the argument is about definitions, not performance. That is the standard condition of a group with three to fifteen plants: each site has its own ERP instance or company code, an MES it chose, a naming convention it invented, and a controller who has reported the same number the same way for eleven years.

Microsoft Fabric will not settle that argument. What it does is make the settlement enforceable once reached — one definition, one security model, one physical layout, applied across every site rather than re-implemented in twelve Power BI files.

It means deciding five things before code is written: how many lakehouses and workspaces exist and how domains group them; how multiple ERP instances land into one conformed model; what grain each fact table carries; how tables are partitioned; and how row-level security works. Everything else is recoverable.

The medallion layering underneath matches any single-site build — Bronze, Silver, Gold. What changes in a group is what happens between Bronze and Silver: the harmonisation of many plants' conventions into one comparable model.

For most groups of three to fifteen plants, the workable topology is one Bronze lakehouse per plant or source system, then a single shared Silver and Gold lakehouse, each layer in its own workspace. Plant autonomy lives in Bronze; group comparability lives in shared Silver and Gold.

A full lakehouse per plant all the way through Gold is politically easy and analytically fatal — fifteen Gold layers means fifteen definitions of scrap, and the group number becomes a hand-maintained spreadsheet. A single lakehouse for everything is the opposite failure: technically fine, operationally poor. Use Fabric domains as the organisational overlay (a domain groups workspaces by business area with its own admins and delegated settings such as default sensitivity labels), remembering a workspace belongs to only one domain at a time. Inside the shared lakehouses use schemas rather than name prefixes — tables are queryable cross-workspace as workspace.lakehouse.schema.table. And only separate capacities give workload isolation, chargeback and multi-region placement.

In a multi-plant group, ingestion is not the critical path — harmonisation is. Two SAP S/4HANA instances will represent the same physical material with different material numbers, units of measure, plant codes and cost-centre structures. Landing the sources takes days; a second S/4HANA instance is a second connection.

Three rules from estates I have worked in. Never overwrite the source key — every Silver row keeps the source system, the instance or company code, and the native key alongside the harmonised group key, so a plant controller can trace any group number back. Build the crosswalk as data, not code — a material crosswalk table with validity dates, a named owner and an approval flag is a governed asset. And accept partial harmonisation explicitly — some plants will never map cleanly on cost centre. In a 6–10 plant group, harmonisation typically runs 6–12 weeks, most of it waiting for decisions rather than writing code.

Fact grain and conformed-dimension keys are the most expensive decisions to reverse. Changing production fact grain after go-live invalidates every downstream measure, partition, report and reconciliation rule at every plant simultaneously.

The grains that hold up across a group: production at one row per production-order operation confirmation, per plant, with work centre, shift, material and quantity type; downtime at one row per event with start, end, native and harmonised reason code; quality at one row per inspection result. Build eight conformed dimensions once and share them — plant, work centre, material, calendar, shift, reason code, cost centre, customer — each carrying a group key and the source key, each with a named owner. The costly retrofit is adding a dimension after the facts are loaded and partitioned; batch is the usual one, and in food, pharmaceutical and packaging estates you put it in from day one, because sources that never captured batch cannot be backfilled at all.

Partition Delta tables only where each partition holds at least 1 GB and the partition column has low-to-moderate cardinality — tens to hundreds of values, not thousands. From Runtime 2.0 Microsoft recommends liquid clustering for read performance, with partitioning reserved mainly for isolating concurrent writers — which is exactly why plant-level partitioning earns its place, because fifteen plant pipelines writing into one production fact conflict unless the writes are separated. Partitioning by plant and day instead creates thousands of tiny directories — the small-file problem bought for nothing. Run OPTIMIZE and VACUUM as scheduled maintenance from week one, and watch the Direct Lake guardrails as plants are added.

Build plant-level access once, not once per report. OneLake security row-level security uses SQL WHERE-clause rules on Delta tables and is enforced consistently across Spark, the SQL endpoint and Direct Lake on OneLake. Two constraints decide the design: OneLake security roles do not restrict workspace Admins, Members or Contributors, and neither does semantic-model RLS — so plant users belong in Viewer, and the workspace role list is part of your security design. If you implement at the model layer, use dynamic RLS with USERPRINCIPALNAME() against a plant-access bridge table, not fifteen static roles. The failure I see most often is RLS implemented per report — twelve reports, twelve role definitions, four of them wrong, discovered when a plant manager sees another plant's scrap.

A multi-plant platform needs two layers of metric definition: a group definition identical across all plants, and local measures each plant uses to run its own day. Publish both, label both, and never quietly replace a plant's number with the group's — ship the group measure alongside a bridge showing which components differ (planned maintenance treatment, changeover, no-demand time, quality losses), so the conversation is about a named rule rather than a number's credibility. OEE is the most contested of these by a distance.

Time handling breaks more multi-plant models than any other factor. The traps, in the order they bite: the production day is not the calendar day (a 22:00 night shift belongs to one production day at one plant and the next at another); the working week differs (Sunday–Thursday in Saudi, Monday–Friday in Malaysia make "last week" two windows); public holidays are per country and per plant; daylight saving creates duplicate and missing local hours at UK or North American sites; and shift patterns change, so the shift dimension needs validity dates or historic capacity utilisation restates itself. Store UTC plus a shift-calendar dimension, not local timestamps only.

Plants rarely go live together, so the deployment model must add one without disturbing those already in production. Fabric deployment pipelines support 2–10 stages and track lakehouse metadata only — name, GUID, shortcut definitions, data-access roles — so promoting code never overwrites a live plant's data.

Three things to configure deliberately. Bind shortcuts and connections per stage with a Fabric variable library (a different active value set per stage), so a plant's dev, test and production shortcuts point at the right source without hand-editing. Rebind semantic models — a Direct Lake model deployed to the next stage stays bound to the source-stage lakehouse until a datasource rule redirects it. And treat plant onboarding as configuration, not code: adding plant nine should mean a connection, a crosswalk entry, an RLS role member and a calendar row — not a new pipeline. One regional constraint: a capacity's region is fixed at creation and non-Power BI Fabric items block a regional move, so decide before you create the capacity.

Plant MES coverage sets your ceiling — if three plants have a modern MES and four record downtime on paper transcribed into Excel weekly, group downtime analysis is limited to the weakest site. Harmonisation cannot fix data never captured. Comparability invites gaming — once plants are ranked on one measure, some optimise the measure. A staged rollout means an inconsistent group number for months. And capacity contention is real on a shared capacity — fifteen plant loads plus month-end reporting on one F-SKU throttles at the worst hour.

Above all, without a group data owner none of it lands. Every decision assumes someone can make a call and enforce it — a named group operations, supply-chain or finance director with three powers: to settle a definition dispute between two plants within a defined window, to mandate a group definition, and to hold a plant to it. Where that authority does not exist, the honest options are to build for plant-level excellence and defer group comparability, or pause and get the mandate first. Attempting comparability without it produces a platform that publishes numbers plants dispute — worse than the spreadsheets it replaced, because it looks authoritative.

Five questions to answer this week, before anyone opens Fabric:

• Can you produce one list of every ERP instance and company code, every MES, and which plant each covers — with the owner of each system named?
• For your top 50 materials by volume, how many distinct part numbers exist across instances, and does a crosswalk exist anywhere today?
• Who, by name, can settle a dispute between two plants over the definition of downtime — and has that person agreed to do it?
• What are the shift patterns, working weeks and public-holiday calendars at each site, and where are they documented?
• Which plant would you go live with second — not first — and what would it reveal that the first would not?

The fifth matters more than it looks: a second plant that differs structurally from the first — different ERP instance, country, shift pattern — exposes the harmonisation problems while the design is still cheap to change. First value in six weeks: two plants, one conformed production fact, one group measure with its component bridge published.