Manufacturing
Production visibility across multi-plant ERP and MES
One plant view from ERP + MES + quality — so the huddle starts with exceptions, not “which file is right?”
- Operating since 2012
- Microsoft Power BI specialists
- NDA-ready data handling
- Fixed-scope first releases
- Independent — not affiliated with Microsoft
Table of contents
01 · Context
The situation
Mid-market industrial manufacturer · Manufacturing
A mid-market manufacturer runs several plants. Planners live in ERP. Supervisors live on the line in MES. Quality tracks NCRs and yield in another tool.
Every week, someone exported those systems into Excel and built the operations pack by hand. Plant, product, and shift keys did not match across extracts — so managers spent the first half of the meeting reconciling totals.
They needed a living report: OEE drivers, first-pass yield, material shortages, and line/shift detail — refreshed before the huddle, not rebuilt the night before.
02 · Solution
What we built
A scoring-free but decision-clear Power BI solution: one model, plant access rules, and dashboards for the huddle and the weekly plant review.
Shared plant model
ERP orders, MES downtime/scrap, and quality yield land on the same plant, line, and shift keys — so OEE and scrap stop disagreeing by extract.
Exception-first plant dashboard
Headline OEE, scrap, and shortage risk first. Then drill to line and shift. Green/amber/red thresholds owners already use in the huddle.
Plant-level access
Supervisors see their plant only. Corporate rolls up. No “everyone gets everything” workbook on email.
Plant performance
- OEE and downtime by line/shift
- First-pass yield and scrap drivers
- Material shortage signals next to schedule adherence
Supply & inventory
- Shortage and turns views for planners
- Supplier OTIF beside production risk
- Links from exception to order detail
Data sources
- ERP production and inventory tables
- MES downtime and scrap events
- Quality first-pass yield extracts
Connectors
- On-premises data gateway
- SQL Server connector
- Power Query staging queries
Model design
Shared site, product, and shift dimensions. Facts for throughput, downtime, and quality. Import mode so MES is not hit during peak shift. Plant managers only see their plant.
03 · Process
How we delivered
From the real meeting to a live report — with checkpoints, not a big reveal at the end.
- 01
Map the real meeting
Walked the daily huddle and weekly plant pack with ops and finance. Wrote down which numbers force action.
- 02
Clean the keys
Profiled ERP/MES/quality extracts. Fixed plant, product, and shift mismatches before building visuals.
- 03
Build and prove
Staged data, built the model, checked totals against the approved pack, then trained supervisors on the live report.
- 04
Handoff
Documented measures, refresh path, and a short backlog for the next plant or KPI.
04 · Technologies
Stack and why we chose it
Tools that serve the report — not a shopping list of buzzwords.
Power BI
Plant and supply dashboards with drill-through and plant-level access.
Why: Interactive enough for huddles; works with the Microsoft estate they already pay for.
SQL Server + staging
Clean tables for production, downtime, and quality before Power BI refresh.
Why: Keeps heavy joins off the live MES during the shift.
On-premises data gateway
Secure path from on-prem SQL into the Power BI service on a schedule.
Why: Plant data stays inside the network; refresh does not depend on a laptop.
05 · Challenges
What got hard — and how we fixed it
Real delivery friction, not a polished success-only story.
Challenge
ERP, MES, and quality used different plant and shift labels for the “same” line.
Resolution
Built a small mapping table owned by ops. Reports only use the conformed keys — raw labels stay in detail for audit.
Challenge
Live queries against MES during peak slowed both reporting and the line systems.
Resolution
Moved to overnight Import from staged tables. Huddle pages open fast; MES stays for the floor.
06 · Results
What changed
- Huddles start from one plant view instead of three exports.
- OEE and scrap use the same definitions across sites.
- Shortage risk shows up before materials scramble the schedule.
Continue exploring this topic
Related pages that deepen the same subject—services, industries, integrations, and proof.
Why teams trust Power BI Analytics
Enterprise delivery with clear ownership
Power BI Analytics combines Power BI craft with business process discipline—so leaders get numbers they can act on.
14
Years building software & automation
What to expect in an engagement
- Named business sponsor and delivery ownership
- KPI definitions validated against source totals
- Security-aware workspace and refresh design
- Documented handover, not a black-box model
- First release scoped to prove value quickly
- Response within one business day on qualified enquiries
Have a similar reporting mess?
Book a free scoping call — we will tell you honestly whether Power BI is the right fix.