Multi-Warehouse Inventory Visibility on Microsoft Fabric: What the Implementation Involves

The question sounds trivial: "how much saleable stock of this item do we have, and where is it?" In a distributor or manufacturer running four own warehouses, a bonded store, two 3PL sites and a dozen containers on the water, it takes three phone calls and most of a morning. Someone pulls the ERP stock report, someone else checks the 3PL portal, someone remembers the bonded consignment.

The cost is not reporting inconvenience. It is safety stock held in the wrong place because planners buffer against a number they do not trust.

This is not a forecasting problem and not an ERP replacement problem. Stock records live in five systems with five definitions of "available" — and the work is to reconcile them into one governed position that finance will accept.

Multi-warehouse inventory visibility is a single governed stock position covering every location holding your inventory — own warehouses, bonded and free-zone stores, 3PL sites, in-transit and consignment — modelled at a defined grain of item, location, batch and stock status, with one written definition of "available" and a daily reconciliation to the ERP.

The join is the easy part. What decides whether the build survives contact with finance is the definitional work: the grain, the meaning of "available" at each site, and whether the number ties out to the ERP every morning.

Two of these are the usual project killers. 3PL reports arrive on the provider's terms and change without notice; and bonded or free-zone stock is a legal state, not a warehouse code, so it has to be modelled as such.

Commit to a stock grain before you build anything. The working grain for most estates is item × location × batch/lot × stock status × owning entity, with an "as at" date for snapshot rows. Adding batch later is a rebuild, not a column addition — every measure, partition and reconciliation rule changes. Model location as a hierarchy (legal entity, country, site, storage location, bin) with attributes for ownership and legal availability, so "bonded" and "3PL" are properties of a location, not exclusions hard-coded into a DAX measure.

Define "available" in writing, then reconcile it to the ERP. Publish three measures, not one: on hand (everything physically there, blocked and bonded included), unrestricted (on hand minus blocked, quarantined, expired and damaged), and available to sell (unrestricted minus reservations, allocations, consignment and legally unavailable stock). The trap is that the ERP already computes something like the third, reflecting years of configuration — publish a different number without explaining the difference and supply chain will use it while finance will not.

Handle units, status and inter-company deliberately. Conversion factors between pieces, cases, pallets and weight change when packaging changes, so hold them in a dimension with validity dates — a factor applied retrospectively will quietly restate your historic stock cover. Status belongs to a batch at a location, not to an item. And stock moving between two of your own legal entities is often on nobody's records in transit, or on both — decide whether the group view eliminates inter-company positions, and stamp every row with the owning entity.

Ingest each source on its own terms. For ERP on SQL Server, Azure SQL, Oracle or Snowflake, Fabric mirroring replicates the database into OneLake as Delta tables with continuous change capture. For SAP, mirroring runs through SAP Datasphere. A WMS with no mirroring path uses a Copy job with incremental copy and CDC where available. 3PL files and portal exports land through Data Factory pipelines over SFTP, FTP or REST. Data you already hold elsewhere — S3, ADLS Gen2, Blob, GCS, Dataverse, on-premises — is referenced with OneLake shortcuts rather than copied.

Then Bronze, Silver, Gold. Bronze holds what arrived, untouched, partitioned by source and load date. Silver does the work that makes the rest possible: item mastering across ERP, WMS and 3PL codes; conversion to a base unit; every source's status codes mapped into one controlled vocabulary; ownership flags; the location hierarchy.

Choose a snapshot and a ledger — you need both.

Keep the ledger in Silver for explanation and batch traceability; build the snapshot in Gold incrementally from the ledger, and re-base it periodically from a full ERP extract so drift cannot accumulate unnoticed. A Direct Lake semantic model reads the Gold Delta tables and refreshes by framing metadata rather than reloading — design against the F-SKU guardrails, because a batch-level daily snapshot is the quickest route to breaching them, and note that Direct Lake on SQL falls back to DirectQuery when SQL-based row-level security applies (relevant if each 3PL or country manager sees only their own sites). Run OPTIMIZE with V-Order and VACUUM on the snapshot tables from week one; small files degrade the SQL analytics endpoint.

Fabric will go faster than your operation will. Mirroring publishes near real time; the 3PL sends a file at 07:00 and counts stock on Thursdays. So match latency to the decision, per site.

Daily at 06:00 covers planning, allocation and expiry management. Intraday for own sites, daily for 3PL means mirrored ERP and WMS refresh every 15–60 minutes while external sites stay on their cadence — and the report labels each site with its own "as at" time. Near real time throughout is justifiable only where a decision is made within minutes, such as dock scheduling or allocation at pick confirmation.

For alerting, Fabric Activator triggers pipelines, notebooks, Copy jobs, Power Automate flows, Teams messages and email. Read one thing carefully: rules on Power BI reports run as periodic observations tied to the model's refresh schedule, so a stock-out alert is only as fresh as the refresh behind it.

Finance will not accept a number that does not tie out, and they are right not to. Build the reconciliation in the same sprint as the stock fact: per site, per day, the ERP quantity, the platform quantity, variance in units and value, and a classified reason — timing, item-mapping gap, status-rule difference, missing feed.

The first month of that page is uncomfortable, and that discomfort is the point. The failure to avoid is tuning the availability rules until the variance disappears — you will have hidden an operational problem inside a data definition.

First value in six weeks is achievable — a working slice, reconciled, not a slide. A full rollout across 6–12 locations and two or three 3PLs typically runs 4–6 months, longer wherever a 3PL needs a commercial conversation before it changes what it sends you.

3PL data quality and cadence set your ceiling — a weekly PDF with no batch and no "as at" timestamp cannot become a daily batch-level position, and a provider who reorders columns without notice breaks your load silently. Cycle-count discipline decides your accuracy, not the platform: what you are showing is record accuracy. Batch traceability has holes wherever a source lacks batch — if your WMS captures lot numbers and your 3PL does not, group-level traceability does not exist, and a recall exercise will discover that at the worst moment.

And visibility does not rebalance inventory. Someone must own the transfer decision, hold authority to move stock between sites and entities, and work to a rule for when an inter-site transfer beats an expedite. The platform exposes the duplicate safety stock and the expiry exposure; a person still has to act.

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

• Can you produce today a single list of every location holding your stock — 3PL bins, bonded stores and in-transit included — with the legal owner and availability status of each?
• Is your definition of "available to sell" written down anywhere, and when did supply chain and finance last agree on it together?
• For each 3PL: what arrives, how often, in what format, and does it carry batch and an "as at" timestamp?
• What decision would change if this were on a screen every morning — and who has authority to make it?

If the fourth question has no answer, build the reconciliation page first and nothing else — it will pay for itself in arguments avoided while you find one. We build these on Microsoft Fabric, OneLake and Power BI, with Power Automate and Copilot Studio closing the loop — unify the data, predict with AI, act with automation, in that order.