Case study · Manufacturing
From month-end spreadsheets to a daily view of the plant
Most manufacturers already have the data: output and scrap in the ERP, downtime on the shop floor, costs in finance. What they lack is one place where it adds up. This is how we built that place in Power BI, with a data model the business can trust and dashboards people actually open.
Project overview
A Power BI reporting platform built on a governed data model.
- Industry
- Manufacturing
- Client
- Confidential
- Solution
- Operations analytics
- Platform
- Power BI + Microsoft Fabric
- Timeline
- 8–14 weeks
- Team
- 3 people
- Refresh
- Hourly to daily
- Sources
- ERP, MES or shop-floor sheets, finance
- Users
- Plant managers to board
Expected business outcomes
What the client set out to gain
Reporting projects are judged on time saved and decisions made sooner. These are the targets the project was planned against; the biggest variable is how consistently the plants record their data today.
-
60–80%
Less time spent building reports
The weekly and monthly packs that were assembled by hand in Excel refresh themselves from the same data model.
Target range; depends on how many reports move onto the model in the first release
-
Daily
Instead of monthly visibility
Output, scrap and downtime are visible the next morning, so a bad week is caught on Tuesday rather than at month-end.
Refresh frequency set per source; shop-floor data is often the limiting factor
-
5–15%
Downtime reduction targeted
Once downtime is recorded by cause and machine, the top few causes become obvious and can be worked on.
Target range once cause codes are recorded consistently; achieved by the plant teams, not the dashboard
Business challenges
How reporting worked before
A familiar picture in manufacturing: plenty of data, very little of it joined up.
Production
Output is in the ERP, but by work order, not by line or shift. Someone rebuilds the shift view by hand each week.
Quality
Scrap and rework are logged on paper or in a separate spreadsheet per plant, with different reason codes at each site.
Downtime
Recorded by machine operators in free text, if at all, so causes can't be counted.
Reporting
Analysts spend the first days of every month copying exports into the same Excel pack, and nobody trusts it until it is checked.
Finance
Margin by product arrives at month-end, too late to change anything that caused it.
Proposed solution
What the Power BI build covers
The dashboards are the visible part. Most of the work is the data model underneath, because that is what makes the numbers agree with finance.
-
Data pipelines
Scheduled loads from the ERP, the shop-floor system or standardised plant spreadsheets into a single store, with checks that flag missing or late data.
-
A governed data model
One definition of output, scrap, OEE and cost per unit, shared by every report, so two dashboards can never show two different numbers.
-
Plant dashboards
Output, scrap and downtime by line and shift, with drill-through to the work order. Built for the morning production meeting.
-
Cost and margin views
Material, labour and overhead per product and plant, reconciled to the ledger so finance signs off on them.
-
Row-level security
Plant managers see their plant, the operations director sees all of them, and nobody needs a separate report.
-
Mobile and alerts
Key figures on the Power BI app and alerts when scrap or downtime crosses a threshold.
Project requirements
What the client asked us for
The brief was "we want dashboards", but the real need is agreement: one number for yesterday's output that operations and finance both accept. So the first release is deliberately small, covering a handful of measures defined with the people who will be held to them.
Shop-floor data is often the weak point. Where plants record scrap and downtime on paper, we standardise a simple capture form first, because no dashboard can report causes that were never written down.
What the project had to deliver
- 01One agreed definition for every measure
- 02Next-morning figures for each plant and line
- 03Margin by product that reconciles to finance
- 04Reports that refresh themselves
Key challenges & considerations
What made it harder, and what we did
-
Different reason codes for scrap and downtime at each plant
What we did
One shared list of scrap and downtime reason codes was agreed with all plants and mapped back to each site's old codes.
-
ERP data that records work orders but not lines or shifts
What we did
Line and shift were added to production reporting, captured through a short form where the ERP could not hold them.
-
Spreadsheet reports that people are attached to
What we did
The first dashboards reproduced the trusted spreadsheet figures exactly, and the spreadsheets were retired only once the two matched.
-
Finance and operations using different cost figures
What we did
Cost definitions were agreed in workshops with finance and operations, and every margin figure was reconciled to the ledger before release.
What we watched for
- Agree definitions before building visuals
- Fix data capture on the shop floor first if it is on paper
- Reconcile to finance before anyone sees a margin figure
- Retire the old spreadsheet report once the dashboard is trusted
Development process
A first release in weeks, then expand
We ship something useful early, then widen the model plant by plant and measure by measure.
-
Measures and sources
Workshops with operations and finance to agree the first ten or so measures and where each one comes from. This is where most reporting projects succeed or fail.
-
Data foundation
Pipelines, a staging area and the core data model, with automated checks on completeness and freshness.
-
First dashboards
The morning production view and the plant scorecard, reviewed with the people who will use them and changed until they do.
-
Cost and margin
Product costing and margin views, reconciled line by line with finance before anyone relies on them.
-
Roll-out and handover
Remaining plants, security roles, training for report builders, and documentation of every measure.
Technology stack
Microsoft's data platform, used plainly
Power BI on top of Microsoft Fabric or Azure SQL, depending on data volume and what the client already licenses.
Reporting
- Power BI
- Power BI mobile app
- Paginated reports
Data platform
- Microsoft Fabric
- Azure SQL
- Dataflows
- Data pipelines
Sources
- ERP (Business Central, SAP or other)
- MES / shop-floor systems
- Standardised capture forms
Governance
- Row-level security
- Deployment pipelines
- Measure documentation
Quality assurance & deployment
Every figure reconciled before anyone relied on it
A dashboard is only useful if its numbers are believed. Each measure was reconciled to its source, and to finance where money was involved, before the report was released.
- Each measure reconciled to its source system
- Financial figures signed off by finance
- Data quality checks on every refresh: completeness and freshness
- Row-level security tested with real user accounts
- Report performance tested at full data volume
Deployment
- 01Cost and marginProduct costing and margin views, reconciled line by line with finance before anyone relies on them.
- 02Roll-out and handoverRemaining plants, security roles, training for report builders, and documentation of every measure.
- 03Deployment pipelinesReports moved from development to test to production through controlled pipelines.
- 04Adoption and warrantyTraining for report users and builders, usage reviewed, and six months of free bug fixing.
Final deliverables
A plant everyone reads the same way
At the end of the first phases, the morning meeting starts from yesterday's numbers instead of last month's, and operations and finance argue about what to do rather than about which figure is right.
The client's own analysts can add reports on the shared model without breaking the definitions, and every measure is documented so a new starter can find out what "scrap rate" means on this site.
- Yesterday's output, scrap and downtime every morning
- Margin by product that finance signs off
- Reports that refresh without anyone touching Excel
- An internal team that can extend the model
Handed over to the client
- 01Data model and pipelinesDocumented and scheduled
- 02Reports and dashboardsPublished with security roles
- 03Measure definitionsEvery figure documented
- 04TrainingFor report users and report builders
- 05Six-month warrantyFree bug fixing after delivery
Success metrics
How success is measured
| Measure | Outcome | How it is measured |
|---|---|---|
| Less time spent building reports | 60–80% | Target range; depends on how many reports move onto the model in the first release |
| Instead of monthly visibility | Daily | Refresh frequency set per source; shop-floor data is often the limiting factor |
| Downtime reduction targeted | 5–15% | Target range once cause codes are recorded consistently; achieved by the plant teams, not the dashboard |
Verified reviews
What clients say about our custom software work
Verified reviews from our clients on the same kind of work, published on Clutch.
-
Custom Software Development
Web-Based Coupon App Development
The work produced by the team was of exceptional quality.
Jon Thies
CTO, Model Rocket Feb 2022 – Mar 2023
-
Custom Software Development
Mobile & Web Platform Development & Design
Silver Scintilla was responsive, respectful, and enjoyable to work with, quickly addressing any questions or requests.
DeShawn Brown
CEO & Founder, Lithios Jun 2021 – Nov 2022
-
Custom Software Development
Web-Based HRM System Development
Silver Scintilla provided the best solution with scalable functionality, personalized features, and robust security.
Suheb Khan
Director, Collaborative Insight Technologies Jul 2020 – Jun 2022
Free discovery call
Have an idea? Let's turn it into AI-powered software.
Book a free discovery call with our experts. Share your idea and we will help you shape the scope, timeline and budget, under NDA.
- Free consultation
- NDA before we talk
- Transparent estimate