Back to Projects

Data Architecture · AWS

Manufacturing Analytics Platform

Outcome

60% reduction in reporting time. Real-time OEE dashboards live within 90 days.

AWS RedshiftdbtQuickSightS3Glue

Context

A mid-size German manufacturer with ~800 employees was operating 12 different production systems — each with its own reporting silo. The Operations team had no unified view of Overall Equipment Effectiveness (OEE) across production lines. Reports were compiled manually in Excel every Monday morning, taking 4 hours per report.

The Problem

  • No single source of truth for production KPIs
  • 12 data sources with different formats, update frequencies, and owners
  • Manual reporting prone to errors and delays
  • No ability to detect production issues in real time
  • Data engineering capability was zero — the team had no AWS experience

Solution Architecture

Phase 1: Foundation (Weeks 1–4)

Built the data platform foundation on AWS:

Production Systems → S3 (Raw) → AWS Glue (ETL) → Redshift → QuickSight
  • S3 Bronze Layer: Raw data from all 12 source systems via scheduled Glue jobs
  • S3 Silver Layer: Cleaned, standardized data
  • AWS Redshift: Central analytics warehouse with OEE data model
  • dbt: All transformations version-controlled, documented, and tested

Phase 2: OEE Data Model (Weeks 5–8)

Designed a production-specific data model:

  • fct_production_events — every machine event, downtime, output
  • dim_machines — machine master data and production line assignment
  • dim_shift_calendar — shift schedules for accurate OEE calculation
  • fct_oee_daily — pre-aggregated OEE metrics by machine, line, and plant

Phase 3: Dashboards (Weeks 9–12)

Built 5 QuickSight dashboards:

  1. Plant Overview — Real-time OEE heatmap across all production lines
  2. Machine Deep-Dive — Availability, Performance, Quality breakdown
  3. Downtime Analysis — Top causes of unplanned downtime
  4. Shift Report — Automated Monday morning report (replaced the Excel)
  5. Trend Analysis — 12-month OEE trend with target tracking

Results

| Metric | Before | After | |---|---|---| | Monday report time | 4 hours manual | 0 minutes (automated) | | Data freshness | Weekly | Near real-time (15 min) | | OEE visibility | None | All 12 lines, live | | Reporting errors | Regular | Zero |

Key Learnings

1. Start with the most painful report. The Monday morning report was the biggest pain point. Automating that first created immediate buy-in from leadership.

2. Invest in a proper data model early. The OEE data model took 2 weeks to get right. Teams that skip this step spend months fixing it later.

3. Train the team, not just the tool. We ran 4 workshops with the production team to ensure they could maintain and extend the dashboards themselves.