BI Lead Journey: Plant Performance and OEE Analytics

{
  "source": {
    "type": "opc_ua",
    "config": {
      "endpoints": [
        {"plant": "plant_1", "url": "opc.tcp://scada-p1.apex.internal:4840"},
        {"plant": "plant_2", "url": "opc.tcp://scada-p2.apex.internal:4840"},
        {"plant": "plant_3", "url": "opc.tcp://scada-p3.apex.internal:4840"},
        {"plant": "plant_4", "url": "opc.tcp://scada-p4.apex.internal:4840"}
      ],
      "node_patterns": [
        "ns=2;s=Machine.*.Status",
        "ns=2;s=Machine.*.CycleCount",
        "ns=2;s=Machine.*.AlarmCode"
      ],
      "polling_interval_ms": 5000
    }
  },
  "destination": {
    "type": "s3_parquet",
    "config": {
      "bucket": "apex-data-lake",
      "prefix": "raw/scada/",
      "partition_by": ["plant_id", "date"]
    }
  }
}

  OEE = Availability x Performance x Quality
  ════════════════════════════════════════════

  Availability = Run Time / Planned Production Time
  ┌──────────────────────────────────────────────────────┐
  │  Planned Production Time (shift hours - planned DT)  │
  │  ┌──────────────────────────────────────────┐        │
  │  │  Run Time (available - unplanned DT)     │        │
  │  └──────────────────────────────────────────┘        │
  └──────────────────────────────────────────────────────┘

  Performance = (Ideal Cycle Time x Total Pieces) / Run Time
  ┌──────────────────────────────────────────────────────┐
  │  Theoretical Max Output at ideal speed               │
  │  ┌──────────────────────────────────┐                │
  │  │  Actual Output (slower + stops)  │                │
  │  └──────────────────────────────────┘                │
  └──────────────────────────────────────────────────────┘

  Quality = Good Pieces / Total Pieces
  ┌──────────────────────────────────────────────────────┐
  │  Total Pieces Produced                               │
  │  ┌──────────────────────────────────────┐            │
  │  │  Good Pieces (passed inspection)     │            │
  │  └──────────────────────────────────────┘            │
  └──────────────────────────────────────────────────────┘

-- Discover status code mappings per plant
SELECT
    plant_id,
    status_code,
    status_description,
    COUNT(*) AS occurrences
FROM machine_status_events
GROUP BY plant_id, status_code, status_description
ORDER BY plant_id, occurrences DESC

-- Semantic Layer: OEE metric definitions
-- These are registered as reusable metrics, not ad-hoc queries
 
-- METRIC: availability_rate
-- Dimensions: machine_id, line_id, plant_id, shift, date
SELECT
    machine_id,
    line_id,
    plant_id,
    shift_id,
    DATE_TRUNC('hour', event_timestamp) AS hour,
    -- Run time = total time in RUNNING state
    SUM(CASE WHEN standardized_state = 'RUNNING'
        THEN duration_minutes ELSE 0 END) AS run_time_minutes,
    -- Planned production time = shift duration - planned downtime
    SUM(CASE WHEN standardized_state NOT IN ('DOWN_PLANNED')
        THEN duration_minutes ELSE 0 END) AS planned_production_minutes,
    -- Availability = run time / planned production time
    ROUND(
        SUM(CASE WHEN standardized_state = 'RUNNING'
            THEN duration_minutes ELSE 0 END) * 100.0
        / NULLIF(SUM(CASE WHEN standardized_state NOT IN ('DOWN_PLANNED')
            THEN duration_minutes ELSE 0 END), 0),
        2
    ) AS availability_rate
FROM machine_status_standardized
GROUP BY machine_id, line_id, plant_id, shift_id,
         DATE_TRUNC('hour', event_timestamp)

-- METRIC: performance_rate
SELECT
    po.machine_id,
    e.line_id,
    e.plant_id,
    DATE_TRUNC('hour', po.end_time) AS hour,
    SUM(po.actual_qty) AS total_pieces,
    -- Ideal cycle time from product specs (seconds per piece)
    AVG(ps.ideal_cycle_time_seconds) AS ideal_cycle_time,
    -- Performance = (ideal cycle time * pieces) / run time
    ROUND(
        (AVG(ps.ideal_cycle_time_seconds) * SUM(po.actual_qty))
        / NULLIF(SUM(ms.run_time_seconds), 0) * 100.0,
        2
    ) AS performance_rate
FROM production_orders po
JOIN product_specs ps ON po.product_id = ps.product_id
JOIN equipment_registry e ON po.machine_id = e.machine_id
JOIN machine_run_time ms ON po.machine_id = ms.machine_id
    AND DATE_TRUNC('hour', po.end_time) = ms.hour
GROUP BY po.machine_id, e.line_id, e.plant_id,
         DATE_TRUNC('hour', po.end_time)

-- METRIC: quality_rate
SELECT
    qi.machine_id,
    e.line_id,
    e.plant_id,
    DATE_TRUNC('hour', qi.inspection_timestamp) AS hour,
    COUNT(*) AS total_inspected,
    SUM(CASE WHEN qi.pass_fail = 'PASS' THEN 1 ELSE 0 END) AS good_pieces,
    ROUND(
        SUM(CASE WHEN qi.pass_fail = 'PASS' THEN 1 ELSE 0 END) * 100.0
        / NULLIF(COUNT(*), 0),
        2
    ) AS quality_rate
FROM quality_inspections qi
JOIN equipment_registry e ON qi.machine_id = e.machine_id
GROUP BY qi.machine_id, e.line_id, e.plant_id,
         DATE_TRUNC('hour', qi.inspection_timestamp)

-- METRIC: composite_oee (combines all three)
SELECT
    a.machine_id,
    a.plant_id,
    a.hour,
    a.availability_rate,
    p.performance_rate,
    q.quality_rate,
    ROUND(
        (a.availability_rate / 100.0)
        * (p.performance_rate / 100.0)
        * (q.quality_rate / 100.0)
        * 100.0,
        2
    ) AS oee_percentage
FROM availability_metrics a
JOIN performance_metrics p ON a.machine_id = p.machine_id AND a.hour = p.hour
JOIN quality_metrics q ON a.machine_id = q.machine_id AND a.hour = q.hour

  OEE Pipeline Architecture
  ═════════════════════════

  Tier 1: Real-time (every 5 minutes)
  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐
  │ SCADA status │───▶│ Standardize  │───▶│ Compute live │
  │ events       │    │ status codes │    │ availability │
  └──────────────┘    └──────────────┘    └──────────────┘

  Tier 2: Near-real-time (hourly)
  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐
  │ Production   │───▶│ Join with    │───▶│ Compute      │
  │ order data   │    │ cycle times  │    │ performance  │
  └──────────────┘    └──────────────┘    │ + quality    │
  ┌──────────────┐───▶│                   └──────────────┘
  │ Quality      │    │
  │ inspections  │    │
  └──────────────┘    │
                      ▼
  Tier 3: Aggregation (daily / shift-end)
  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐
  │ Hourly OEE   │───▶│ Shift / day  │───▶│ Plant / line │
  │ by machine   │    │ rollups      │    │ summaries    │
  └──────────────┘    └──────────────┘    └──────────────┘

{
  "workflow": "oee_pipeline",
  "tiers": [
    {
      "name": "realtime_availability",
      "schedule": "*/5 * * * *",
      "activities": [
        {
          "name": "ingest_machine_status",
          "type": "sql_transform",
          "config": {
            "query": "transforms/standardize_machine_status.sql",
            "output_table": "oee.machine_status_current"
          }
        },
        {
          "name": "compute_availability",
          "type": "sql_transform",
          "config": {
            "query": "transforms/availability_5min.sql",
            "output_table": "oee.availability_live"
          }
        }
      ]
    },
    {
      "name": "hourly_oee",
      "schedule": "5 * * * *",
      "activities": [
        {
          "name": "compute_performance",
          "type": "sql_transform",
          "config": {
            "query": "transforms/performance_hourly.sql",
            "output_table": "oee.performance_hourly"
          }
        },
        {
          "name": "compute_quality",
          "type": "sql_transform",
          "config": {
            "query": "transforms/quality_hourly.sql",
            "output_table": "oee.quality_hourly"
          }
        },
        {
          "name": "compute_composite_oee",
          "type": "sql_transform",
          "config": {
            "query": "transforms/composite_oee.sql",
            "output_table": "oee.oee_hourly"
          }
        },
        {
          "name": "validate_oee_completeness",
          "type": "data_quality",
          "config": {
            "expectations": [
              {"type": "expect_column_values_to_be_between", "column": "oee_percentage", "min": 0, "max": 100},
              {"type": "expect_column_values_to_be_between", "column": "availability_rate", "min": 0, "max": 100},
              {"type": "expect_column_values_to_not_be_null", "column": "machine_id"},
              {"type": "expect_table_row_count_to_be_between", "min": 180, "description": "At least 180 machines reporting per hour"}
            ]
          }
        }
      ]
    },
    {
      "name": "daily_summaries",
      "schedule": "0 0 * * *",
      "activities": [
        {
          "name": "shift_rollups",
          "type": "sql_transform",
          "config": {
            "query": "transforms/shift_day_rollups.sql",
            "output_table": "oee.oee_shift_daily"
          }
        },
        {
          "name": "plant_summaries",
          "type": "sql_transform",
          "config": {
            "query": "transforms/plant_summary.sql",
            "output_table": "oee.oee_plant_daily"
          }
        }
      ]
    }
  ]
}

  OEE Benchmark Comparison (Apex Manufacturing)
  ══════════════════════════════════════════════

  World-Class:        ████████████████████████████████████████████  85%

  Apex Avg:           ████████████████████████████████████████      72.4%
  Plant 1:            ████████████████████████████████████████      74.2%
  Plant 2:            ██████████████████████████████████████        72.1%
  Plant 3:            ████████████████████████████████████          69.7%
  Plant 4:            ████████████████████████████████████████      73.3%

  Gap to World-Class: 12.6 percentage points

-- Identify bottom 10 machines by OEE with root cause breakdown
SELECT
    m.machine_id,
    m.machine_type,
    e.plant_id,
    e.line_id,
    ROUND(AVG(m.oee_percentage), 1) AS avg_oee,
    ROUND(AVG(m.availability_rate), 1) AS avg_availability,
    ROUND(AVG(m.performance_rate), 1) AS avg_performance,
    ROUND(AVG(m.quality_rate), 1) AS avg_quality,
    -- Identify the weakest OEE component
    CASE
        WHEN AVG(m.availability_rate) <= LEAST(AVG(m.performance_rate), AVG(m.quality_rate))
            THEN 'AVAILABILITY'
        WHEN AVG(m.performance_rate) <= LEAST(AVG(m.availability_rate), AVG(m.quality_rate))
            THEN 'PERFORMANCE'
        ELSE 'QUALITY'
    END AS bottleneck_factor
FROM oee.oee_hourly m
JOIN equipment_registry e ON m.machine_id = e.machine_id
WHERE m.hour >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY m.machine_id, m.machine_type, e.plant_id, e.line_id
ORDER BY avg_oee ASC
LIMIT 10

-- Pareto analysis of downtime reasons
SELECT
    downtime_reason,
    COUNT(*) AS event_count,
    SUM(duration_minutes) AS total_downtime_minutes,
    ROUND(SUM(duration_minutes) * 100.0
        / SUM(SUM(duration_minutes)) OVER (), 1) AS pct_of_total,
    SUM(SUM(duration_minutes)) OVER (ORDER BY SUM(duration_minutes) DESC)
        * 100.0 / SUM(SUM(duration_minutes)) OVER () AS cumulative_pct
FROM downtime_events
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
  AND planned = false
GROUP BY downtime_reason
ORDER BY total_downtime_minutes DESC

  Downtime Pareto (Last 30 Days)
  ══════════════════════════════

  Tool breakage     ████████████████████████████  28%  ──── 28% (cumulative)
  Setup/changeover  ██████████████████████        22%  ──── 50%
  Bearing failure   ███████████████               15%  ──── 65%
  Material shortage ██████████                    10%  ──── 75%
  Software fault    ████████                       8%  ──── 83%
  Coolant issue     ██████                         6%  ──── 89%
  Operator error    █████                          5%  ──── 94%
  Other             ██████                         6%  ──── 100%

  Focus: Top 4 reasons account for 75% of all unplanned downtime

  ┌─────────────────────────────────────────────────────────────────┐
  │  APEX MANUFACTURING - PLANT 1        OEE: 74.2%    Shift: A    │
  │                                      Target: 85%   14:32       │
  ├──────────────────────┬──────────────────────────────────────────┤
  │ Machine Status Grid  │  Active Alarms                          │
  │ ┌─┬─┬─┬─┬─┬─┬─┬─┬─┐ │  M-0033: DOWN - Tool breakage (47 min) │
  │ │G│G│Y│G│G│R│G│G│G│ │  M-0041: SETUP - Product changeover     │
  │ ├─┼─┼─┼─┼─┼─┼─┼─┼─┤ │  M-0055: DOWN - Coolant alarm (12 min) │
  │ │G│G│G│G│Y│G│G│G│G│ │                                          │
  │ ├─┼─┼─┼─┼─┼─┼─┼─┼─┤ │──────────────────────────────────────── │
  │ │G│G│G│R│G│G│G│Y│G│ │  Shift Production                       │
  │ ├─┼─┼─┼─┼─┼─┼─┼─┼─┤ │  Produced:  1,247 / 1,500 planned      │
  │ │G│G│G│G│G│G│G│G│G│ │  ████████████████████░░░░░  83%          │
  │ └─┘ └─┘ └─┘ └─┘ └─┘ │  On track: Yes (if no further downtime) │
  │ G=Running Y=Setup    │                                          │
  │ R=Down               │                                          │
  ├──────────────────────┴──────────────────────────────────────────┤
  │  OEE Components:  Avail: 88%  |  Perf: 91%  |  Quality: 93%   │
  └─────────────────────────────────────────────────────────────────┘

{
  "alerts": [
    {
      "name": "oee_drop_critical",
      "condition": "plant_oee < 70% for current shift",
      "recipients": ["plant-manager", "shift-supervisor"],
      "channel": ["sms", "dashboard_flash"],
      "severity": "critical"
    },
    {
      "name": "unplanned_downtime_extended",
      "condition": "any machine DOWN_UNPLANNED > 30 minutes",
      "recipients": ["maintenance-lead", "shift-supervisor"],
      "channel": ["sms", "email"],
      "severity": "high"
    },
    {
      "name": "quality_rate_drop",
      "condition": "quality_rate < 95% for any line (rolling 1 hour)",
      "recipients": ["quality-engineer", "shift-supervisor"],
      "channel": ["email", "dashboard_highlight"],
      "severity": "high"
    },
    {
      "name": "energy_demand_peak",
      "condition": "plant_demand_kw > 90% of contracted capacity",
      "recipients": ["facilities-manager"],
      "channel": ["sms"],
      "severity": "high"
    },
    {
      "name": "supplier_delay_warning",
      "condition": "delivery > 2 days past promised date AND material inventory < 5 days supply",
      "recipients": ["procurement-manager", "production-planner"],
      "channel": ["email"],
      "severity": "medium"
    }
  ]
}

  Shift Leaderboard - Plant 1 (This Week)
  ═══════════════════════════════════════

  Rank  Shift    Crew Lead      OEE    vs Target  vs Last Week
  ──────────────────────────────────────────────────────────────
   1    Shift A  J. Martinez    78.3%    -6.7%       +2.1%
   2    Shift B  R. Kim         76.1%    -8.9%       +3.4%

  Best Machine OEE This Week: M-0012 (Lathe, Line A) - 91.2%
  Most Improved: M-0033 (Lathe, Line B) - 62.1% -> 71.8% (+9.7%)

-- Identify energy waste: machines consuming power while idle
SELECT
    e.machine_id,
    e.machine_type,
    e.plant_id,
    SUM(CASE WHEN ms.standardized_state = 'IDLE' THEN ec.kwh ELSE 0 END)
        AS idle_kwh_30d,
    SUM(ec.kwh) AS total_kwh_30d,
    ROUND(
        SUM(CASE WHEN ms.standardized_state = 'IDLE' THEN ec.kwh ELSE 0 END)
        * 100.0 / NULLIF(SUM(ec.kwh), 0),
        1
    ) AS idle_energy_pct,
    -- Estimated annual savings if idle power reduced by 50%
    ROUND(
        SUM(CASE WHEN ms.standardized_state = 'IDLE' THEN ec.kwh ELSE 0 END)
        * 12 * 0.5 * 0.12,  -- 12 months, 50% reduction, $0.12/kWh
        0
    ) AS estimated_annual_savings_usd
FROM energy_consumption ec
JOIN equipment_registry e ON ec.machine_id = e.machine_id
JOIN machine_status_standardized ms ON ec.machine_id = ms.machine_id
    AND ec.timestamp = ms.event_timestamp
WHERE ec.timestamp >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY e.machine_id, e.machine_type, e.plant_id
HAVING SUM(CASE WHEN ms.standardized_state = 'IDLE' THEN ec.kwh ELSE 0 END) > 100
ORDER BY idle_kwh_30d DESC
LIMIT 10