Transport dashboards answer whether the load moved on time. Supply chain analytics answer whether you will have stock, whether the plan is still true, and whether a tier-2 supplier is about to break a finished-goods promise. Mixing both on one page usually produces a mural that neither planner nor dispatcher trusts.
Keep OTIF, dwell, and freight spend on the logistics dashboard build guide. This page is inventory, demand, and supplier visibility — the planning meeting, not the yard meeting.
Inventory that matches WMS and ERP
ERP on-hand and WMS bin quantity disagree when receipts, adjustments, and in-transit are timed differently. Model inventory at SKU × location × day (or snapshot grain you can actually refresh). Separate available, reserved, in-transit, and quality-hold. A single “stock” card that mixes those four will lose the materials meeting in five minutes.
Ageing and turns belong next to shortage risk: days of cover against a signed demand stream (forecast, customer orders, or both — pick one for the first release). Do not DirectQuery the live WMS for a weekly S&OP pack. Import a snapshot after the warehouse cutoff. Operational stock-out walls can be Dual or DirectQuery later if the WMS is indexed on SKU/location.
Demand vs plan without a second Excel forecast
Demand planning files usually live in Excel or a dedicated APS. Treat them as a source with a version key (plan week, freeze fence). Compare actual shipments or consumption from ERP/TMS against that version — not against whatever file landed in email on Monday. If the APS cannot be connected, land a governed SharePoint table with RangeStart/RangeEnd if the history is large enough for incremental refresh.
Bias and MAPE are useful once grain is stable. They are useless if “actual” is invoiced quantity in one region and shipped quantity in another. Document the actuals definition the same way you document OTIF on the transport side.
Multi-tier supplier data
Tier-1 ASN and PO data often exists in ERP. Tier-2 visibility is usually a portal extract or a supplier scorecard spreadsheet. Do not pretend you have a live multi-tier graph. Build a supplier-item bridge, then show on-time inbound, quality NCR counts, and promised vs received at the tier you can actually join. Flag missing tiers as a coverage gap on the page so planners stop assuming the model sees everything.
RLS for buyers and plant materials teams usually filters PlantId or PurchasingOrg — not the same ClientId pattern used in 3PL transport. Test View as for a buyer who covers two plants and a corporate planner who sees the network. Bidirectional relationships “to make the vendor slicer work” are a common leak path.
S&OP versus the dock — two cadences
Weekly S&OP can tolerate a T-1 snapshot. The materials huddle cannot. Do not force both onto one DirectQuery model. Ship the snapshot pack first (Import, documented as-of time on every page). If inbound docks need a live backlog, that is a second dataset with a thinner grain and Query reduction on slicers. Mixing them is how planners wait 40 seconds for a card that finance does not even use.
Freeze fences belong in the model. If demand inside the fence is customer orders and outside the fence is forecast, write that in the measure description. A single “demand” measure that switches silently by week number will lose the S&OP room the first time someone drills to SKU.
Power Query and warehouse hygiene
ERP on-hand extracts are often nested (lots, bins, statuses). Expand only the columns you need, and after a SQL projection if you can. Table.Buffer on a 2-million-row inventory snapshot to “make the merge faster” is how gateway timeouts start. Stage inventory in SQL or a dataflow; keep the PBIX thin.
Supplier scorecards that arrive as emailed Excel should land in a SharePoint folder the gateway can see. Personal OneDrive paths will Status = Failed the first Monday the buyer is on leave. Version the file name or a PlanWeek column so last week’s forecast is not overwritten without a trail.
What a first supply-chain release should include
Do not start with a network-optimization science project. Start with inventory truth, one demand version, inbound OTIF, and a shortage list with owners. That is enough for a weekly materials meeting. Multi-echelon optimization, if you ever need it, is a later model — and it will fail if these four are still disputed.
- SKU × location inventory snapshot with available vs reserved vs in-transit.
- Days of cover against one signed demand version.
- Inbound OTIF from ASN/PO receipts (ERP), not from the carrier’s marketing portal.
- A shortage exception list with owner and expected receipt date.
For the transport control-tower side of the same network, start at Power BI for logistics. Plant OEE and scrap are a different grain — that lives on manufacturing, not in this model.