Building a Real-Time OEE Dashboard with SQL

A real-time OEE dashboard with SQL means computing Overall Equipment Effectiveness—Availability × Performance × Quality—continuously from live machine event streams, with sub-second refresh. RisingWave, a PostgreSQL-compatible streaming database, calculates all three OEE components as incrementally maintained materialized views queryable from any BI tool.

Why Real-Time OEE Matters

Overall Equipment Effectiveness is the gold standard KPI for manufacturing efficiency. An OEE of 85% is considered world class. Most manufacturers operate at 60% or below—meaning 40% of their capacity is lost to downtime, speed losses, and quality defects.

The problem: most OEE systems calculate the score at the end of a shift or day. By the time a supervisor sees that yesterday's OEE was 58%, it is too late to correct the micro-stoppages, speed reductions, and yield losses that caused it. Real-time OEE turns that around—supervisors see the live score every minute, enabling immediate intervention.

Real-time OEE requires continuous computation across three components simultaneously: uptime tracking (Availability), cycle time monitoring (Performance), and pass/fail tracking (Quality). Streaming SQL is the natural fit.

How Streaming SQL Solves This

RisingWave ingests machine state events—running, stopped, fault—and production events—cycle complete, part OK, part NG—from Kafka. Three materialized views compute each OEE component continuously. A fourth view multiplies them for the composite score. All views update with each new event; the dashboard always shows the current shift's OEE in real time.

Step-by-Step Tutorial

Step 1: Connect Your Data Source

-- Machine state transitions
CREATE SOURCE machine_states (
    machine_id    VARCHAR,
    line_id       VARCHAR,
    shift_id      VARCHAR,
    state         VARCHAR,   -- 'RUNNING', 'PLANNED_STOP', 'UNPLANNED_STOP', 'FAULT', 'SETUP'
    reason_code   VARCHAR,
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'machine-state-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

-- Production cycle events
CREATE SOURCE production_cycles (
    machine_id    VARCHAR,
    line_id       VARCHAR,
    shift_id      VARCHAR,
    event_type    VARCHAR,   -- 'CYCLE_COMPLETE', 'PART_OK', 'PART_NG'
    part_serial   VARCHAR,
    cycle_seconds DOUBLE,
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'production-cycles',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Build the Real-Time View

Availability: ratio of actual running time to planned production time within the current shift window.

CREATE MATERIALIZED VIEW availability_by_shift AS
SELECT
    machine_id,
    line_id,
    shift_id,
    window_start,
    window_end,
    -- Count seconds in RUNNING state vs total planned window
    SUM(CASE WHEN state = 'RUNNING' THEN 1 ELSE 0 END) * 1.0
        / NULLIF(COUNT(*), 0) AS availability_rate,
    COUNT(*) FILTER (WHERE state = 'FAULT')        AS fault_count,
    COUNT(*) FILTER (WHERE state = 'UNPLANNED_STOP') AS unplanned_stop_count
FROM TUMBLE(machine_states, event_time, INTERVAL '1 HOUR')
GROUP BY machine_id, line_id, shift_id, window_start, window_end;

Step 3: Window-Based Aggregations

Performance: ratio of actual cycle time to ideal cycle time. Requires an ideal takt time reference.

CREATE MATERIALIZED VIEW performance_by_shift AS
SELECT
    machine_id,
    line_id,
    shift_id,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'CYCLE_COMPLETE')  AS actual_cycles,
    AVG(cycle_seconds)                                      AS avg_cycle_sec,
    -- Ideal cycle = 40 seconds; performance = ideal / actual (capped at 1.0)
    LEAST(1.0,
        40.0 / NULLIF(AVG(cycle_seconds) FILTER (WHERE event_type = 'CYCLE_COMPLETE'), 0)
    )                                                       AS performance_rate
FROM TUMBLE(production_cycles, event_time, INTERVAL '1 HOUR')
GROUP BY machine_id, line_id, shift_id, window_start, window_end;

Quality: ratio of good parts to total parts.

CREATE MATERIALIZED VIEW quality_by_shift AS
SELECT
    machine_id,
    line_id,
    shift_id,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE event_type = 'PART_OK')         AS good_parts,
    COUNT(*) FILTER (WHERE event_type = 'PART_NG')         AS defective_parts,
    COUNT(*) FILTER (WHERE event_type IN ('PART_OK', 'PART_NG')) AS total_parts,
    COUNT(*) FILTER (WHERE event_type = 'PART_OK') * 1.0
        / NULLIF(COUNT(*) FILTER (WHERE event_type IN ('PART_OK','PART_NG')), 0)
                                                            AS quality_rate
FROM TUMBLE(production_cycles, event_time, INTERVAL '1 HOUR')
GROUP BY machine_id, line_id, shift_id, window_start, window_end;

Composite OEE score: join all three components:

CREATE MATERIALIZED VIEW oee_dashboard AS
SELECT
    a.machine_id,
    a.line_id,
    a.shift_id,
    a.window_start,
    a.window_end,
    ROUND(a.availability_rate  * 100, 2) AS availability_pct,
    ROUND(p.performance_rate   * 100, 2) AS performance_pct,
    ROUND(q.quality_rate       * 100, 2) AS quality_pct,
    ROUND(a.availability_rate * p.performance_rate * q.quality_rate * 100, 2) AS oee_pct,
    p.actual_cycles,
    q.good_parts,
    q.defective_parts,
    a.fault_count,
    a.unplanned_stop_count
FROM availability_by_shift a
JOIN performance_by_shift  p
    ON  a.machine_id  = p.machine_id
    AND a.shift_id    = p.shift_id
    AND a.window_start = p.window_start
JOIN quality_by_shift      q
    ON  a.machine_id  = q.machine_id
    AND a.shift_id    = q.shift_id
    AND a.window_start = q.window_start;

Step 4: Alerts and Sinks

Alert when OEE drops below 65% (below-average threshold):

CREATE MATERIALIZED VIEW oee_alerts AS
SELECT
    machine_id,
    line_id,
    shift_id,
    oee_pct,
    availability_pct,
    performance_pct,
    quality_pct,
    window_end AS detected_at,
    CASE
        WHEN availability_pct < 70  THEN 'LOW_AVAILABILITY'
        WHEN performance_pct  < 70  THEN 'LOW_PERFORMANCE'
        WHEN quality_pct      < 95  THEN 'LOW_QUALITY'
        ELSE 'LOW_OEE'
    END AS root_cause
FROM oee_dashboard
WHERE oee_pct < 65.0;

CREATE SINK oee_alerts_sink
FROM oee_alerts
WITH (
    connector = 'kafka',
    topic = 'oee-alerts',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

How do I handle the ideal cycle time varying by part type?

Store ideal cycle times in a CREATE TABLE keyed by machine_id and part_type. Join this table in the performance_by_shift view to apply the correct takt time for each production run.

What is the correct way to handle shift boundaries in tumble windows?

Align your tumble window interval with your shift duration (e.g., INTERVAL '8 HOURS') and set the window origin to your shift start time. Alternatively, use shift_id as a group key alongside the window to ensure OEE is always calculated within a shift boundary, not across shifts.

Can I display the OEE dashboard in Grafana?

Yes. Connect Grafana to RisingWave via the PostgreSQL data source plugin. Query oee_dashboard directly. The sub-second refresh of the materialized view means Grafana's auto-refresh at 5–10 second intervals always shows current data.

Key Takeaways

• RisingWave computes all three OEE components—Availability, Performance, Quality—as separate tumble window materialized views that join into a composite OEE score refreshed continuously.
• The OEE dashboard view is queryable from any PostgreSQL-compatible BI tool, enabling Grafana, Tableau, or Metabase dashboards with live data.
• Automated alerts on OEE drops include root cause classification, directing supervisors to the specific component causing the loss.
• The entire pipeline from machine event to live OEE score is expressed in SQL with no custom aggregation code.

Get started with RisingWave | Join the community on Slack