Reconciling OEE with MES Production Counts

A sensor-derived dashboard reporting 4,812 units for Line 7’s capper on the 06:00–14:00 shift, next to an MES shift-close report showing 4,790 for the same asset and window, is one of the fastest ways to lose a plant floor’s trust in OEE formula validation: the arithmetic can be flawless and the number still gets rejected because it disagrees with the system operators already sign off against. MES counts are usually the record of truth for payroll, inventory, and customer shipment reporting, while the sensor stream is what actually computes Availability, Performance, and Quality in near real time. When the two diverge, the fix is not to pick a winner by default — it is to build a deterministic reconciliation that explains every unit of the gap, matches on a stable key, and only alerts when the residual exceeds a documented tolerance. This page covers why sensor and MES counts drift apart, how to join them per shift and asset, and how to size the tolerance band so real defects trigger an alert without drowning the team in noise from rounding.

Sensor and MES counts converge on a reconciliation join, gated by a tolerance band Two source boxes, sensor-derived counts from the time-series database and MES shift-close counts from the ERP export, feed a central reconciliation join keyed on asset and shift. The join's delta output branches into a pass path when within tolerance and an alert path with a root-cause hint when the delta exceeds the documented band. Sensor-derived counts MES shift-close counts Reconciliation join Delta & tolerance test SUM(total_count, good_count) total_qty, good_qty (ERP export) key: asset_id + shift_id Δ = mes_qty − sensor_qty delta_pct vs documented band Within tolerance publish sensor count Exceeds tolerance alert + root-cause hint every reconciliation writes an audit row, whether it passes or alerts
Sensor-derived and MES shift-close counts are joined on a stable (asset, shift) key; the delta is tested against a documented tolerance band, and both outcomes are written to an audit trail rather than only the failures.

Where the counts actually diverge

Before writing a single reconciliation query, catalogue the mechanisms that separate the two sources — otherwise every alert becomes a manual investigation instead of a routed fix.

MQTT delivery duplicates the sensor side. A proximity sensor publishing part-complete events at QoS 1 guarantees at-least-once delivery; a broker reconnect after a network blip retransmits the last unacknowledged message, and a naive counter increments twice for one physical part. The MES total, sourced from a barcode scan at pack-out, does not see the duplicate. This is the same idempotency problem addressed in event-to-downtime mapping: every count increment needs a deduplication key, not just every state transition.

Rework re-triggers the same sensor without re-entering MES as new production. A part rejected at inspection, repaired, and sent back through the same station trips the completion sensor a second time, inflating the sensor total. The MES, which tracks the part by serial or lot ID, correctly counts it once. Sensor-only pipelines that lack part-level identity cannot distinguish this from a genuine second unit — they can only recognize the pattern by comparing against MES afterward.

Shift-boundary and timezone misalignment shifts the bucket, not the total. If the sensor aggregation buckets by UTC day while the MES export buckets by local shift calendar, a handful of parts made in the last minutes of a shift land in different reporting periods on each side. The daily totals eventually agree; the per-shift totals do not. Bucketing both sources against the same materialized shift calendar — the one built in shift boundary logic — removes this class of mismatch entirely.

Manual MES adjustments have no sensor equivalent. A supervisor voiding a lot for a quality hold, or manually crediting units for a known sensor outage, changes the MES total without any corresponding telemetry event. These are legitimate, documented deltas, not defects, and the reconciliation must be able to tag them as such rather than routing them to the same alert queue as an unexplained gap.

Sensor miscounts from mechanical noise. A jam that causes a part to bounce past a photoeye can trigger two pulses for one physical unit, or a marginal detection threshold can miss a part entirely under vibration. These are hardware-level faults that the Hampel filter and similar spike-rejection stages reduce but rarely eliminate to zero.

Building the reconciliation join

The join key must be identical on both sides: asset_id and shift_id, where shift_id is resolved from the same materialized shift calendar consumed everywhere else in the pipeline, not recomputed independently by the MES export job. Start from a TimescaleDB view that aggregates sensor counts per shift, then join it against the MES production-count table loaded from the nightly ERP export.

-- Sensor-side aggregate: one row per asset per shift, from raw counter events.
CREATE MATERIALIZED VIEW sensor_shift_counts AS
SELECT
    e.asset_id,
    c.shift_id,
    COUNT(*) FILTER (WHERE e.event_type = 'part_complete')      AS sensor_total,
    COUNT(*) FILTER (WHERE e.event_type = 'part_complete'
                      AND e.quality_flag = 'good')               AS sensor_good
FROM   sensor_events e
JOIN   shift_calendar c
       ON e.asset_id = c.facility_asset_id
      AND e.event_ts >= c.shift_start
      AND e.event_ts <  c.shift_end               -- half-open, matches shift_boundary_logic
WHERE  e.dedup_key IS NOT NULL                     -- only post-dedup rows are counted
GROUP  BY e.asset_id, c.shift_id;

-- Reconciliation: join sensor aggregate against the MES export, per shift.
SELECT
    s.asset_id,
    s.shift_id,
    s.sensor_total,
    m.mes_total,
    (m.mes_total - s.sensor_total)                                       AS delta_total,
    ROUND(100.0 * (m.mes_total - s.sensor_total)
          / NULLIF(m.mes_total, 0), 3)                                    AS delta_pct,
    s.sensor_good,
    m.mes_good,
    (m.mes_good - s.sensor_good)                                          AS delta_good
FROM   sensor_shift_counts s
JOIN   mes_production_counts m
       ON  m.asset_id = s.asset_id
      AND  m.shift_id = s.shift_id
WHERE  m.shift_id IN (
           SELECT shift_id FROM shift_calendar
           WHERE shift_start >= now() - INTERVAL '7 days'
       )
ORDER  BY ABS(m.mes_total - s.sensor_total) DESC;

The dedup_key IS NOT NULL filter matters more than it looks: it assumes deduplication already ran upstream, so the sensor aggregate this query reads is post-cleaning, not raw. Reconciling against raw, undeduplicated counts conflates a real MES discrepancy with a known, already-solved duplicate-delivery problem and wastes an investigation on it.

Setting tolerance bands and alerting

A fixed absolute tolerance (say, “flag any delta over 5 units”) over-alerts on high-volume lines and under-alerts on low-volume ones; a fixed percentage tolerance over-alerts on short shifts with small denominators. Combine both, and require the smaller trigger of the two to fire — the wider band wins, so a single stray unit on a 40-unit changeover shift does not page anyone.

from dataclasses import dataclass
from decimal import Decimal
from enum import Enum


class ReconciliationStatus(str, Enum):
    PASS = "pass"
    ALERT = "alert"
    ADJUSTED = "adjusted"  # a documented manual MES adjustment explains the delta


@dataclass(frozen=True)
class ToleranceBand:
    absolute_units: int = 3          # ignore deltas this small regardless of shift size
    relative_pct: Decimal = Decimal("0.5")  # or deltas below this percentage


def evaluate_reconciliation(
    asset_id: str,
    shift_id: str,
    sensor_total: int,
    mes_total: int,
    known_adjustments: dict[tuple[str, str], int],
    band: ToleranceBand = ToleranceBand(),
) -> dict[str, object]:
    """Classify a sensor-vs-MES delta against a documented tolerance band.

    A delta fully explained by a logged MES adjustment (rework credit,
    quality hold void) is reported as ADJUSTED, not ALERT, so the audit
    trail distinguishes a known cause from an unexplained gap.
    """
    delta = mes_total - sensor_total
    adjustment = known_adjustments.get((asset_id, shift_id), 0)
    residual = delta - adjustment

    within_absolute = abs(residual) <= band.absolute_units
    denom = Decimal(mes_total) if mes_total else Decimal(1)
    within_relative = (
        abs(Decimal(residual) / denom) * Decimal(100) <= band.relative_pct
    )

    if adjustment != 0 and within_absolute:
        status = ReconciliationStatus.ADJUSTED
    elif within_absolute or within_relative:
        status = ReconciliationStatus.PASS
    else:
        status = ReconciliationStatus.ALERT

    # Root-cause hint: sensor over MES suggests duplicate delivery or
    # rework re-trigger; MES over sensor suggests a sensor undercount.
    hint = None
    if status == ReconciliationStatus.ALERT:
        hint = "sensor_overcount" if residual < 0 else "sensor_undercount"

    return {
        "asset_id": asset_id,
        "shift_id": shift_id,
        "delta_total": delta,
        "residual_after_adjustment": residual,
        "status": status.value,
        "root_cause_hint": hint,
    }

Every call — pass, adjusted, or alert — is written to an append-only reconciliation ledger, not just the failures. When a shift is later disputed, the ledger is the evidence that the sensor and MES numbers were compared and found consistent, which is as valuable for an audit as catching the shifts that were not. Reconciled counts, once passed, are what should flow into line-to-plant OEE rollups — rolling up unreconciled sensor counts just propagates an unresolved discrepancy to a wider audience.

Gotchas & anti-patterns

  • Reconciling before deduplication runs. Joining raw sensor events against MES totals conflates a solved problem (duplicate MQTT delivery) with a real one, and the resulting alert volume trains the team to ignore the whole reconciliation report.
  • Using different shift calendars on each side. If the MES export buckets by a fixed UTC offset and the sensor aggregate buckets by the DST-aware shift boundary calendar, every shift near a boundary shows a phantom delta that has nothing to do with counting accuracy.
  • A single global tolerance for every asset. A high-speed filler running 200 units/minute and a slow CNC cell running 4 units/minute should not share one absolute tolerance; size the band per asset class or use the combined absolute-and-relative rule above.
  • Treating every delta as a defect. Legitimate manual MES adjustments (scrap credits, quality holds) are common and should be logged and matched, not funneled into the same alert queue as unexplained sensor gaps.
  • Reconciling only on failure. A ledger that records passes as well as alerts is what makes a reconciliation program auditable; one that only logs exceptions cannot prove the quiet shifts were ever checked.

Quick reference

Discrepancy pattern Likely cause Fix
Sensor total > MES total, small and constant QoS 1 duplicate delivery Idempotent dedup key on the sensor ingest path
Sensor total > MES total, spikes after jams Bounce/double-trigger on the photoeye Debounce threshold + Hampel filter on the raw pulse train
MES total > sensor total, steady offset Sensor undercount under vibration/marginal threshold Retune detection threshold; verify with a manual count sample
Delta only near shift boundaries Mismatched shift calendars between sensor and MES buckets Bucket both sides against the same materialized shift calendar
Delta matches a logged MES transaction Manual scrap credit or quality-hold void Match against the MES adjustment log; classify as ADJUSTED, not ALERT
Delta grows with shift length, MES higher Rework re-entering as new production credit Join on part/serial ID where available; else flag for manual audit

Related in this section