OEE Reporting and Shift Rollups for Manufacturing Analytics

Asset-level OEE that is correctly computed can still lie the moment it is rolled up to a shift, a line, or a plant, because the roll-up is a second, separate arithmetic problem with its own failure modes. This section sits above Downtime Classification & OEE Calculation and covers the reporting layer that consumes validated Availability, Performance, and Quality figures and turns them into numbers a plant manager will act on. The problem is narrow: given per-asset OEE components for a time window, produce a shift report, a line summary, and a plant scorecard that are internally consistent, auditable back to raw telemetry, and immune to the single most common reporting bug in this domain — averaging ratios instead of aggregating the totals that produced them.

That bug has a name worth stating up front. If Asset 101 ran at 90% OEE for two hours and Asset 102 ran at 30% OEE for eight hours, the line’s OEE is not $(90 + 30) / 2 = 60\%$. A mean of two percentages throws away the fact that Asset 102 dominated the period, and it is not recoverable after the fact — the only fix is to never compute it that way. Every rollup in this section is built from summed durations and summed counts, with ratios formed only at the very end.

Rollup tiers at a glance #Permalink to this section

Every row aggregates the same way: sum the numerator and denominator components independently across the grain being rolled up, then divide once. The weighting basis differs by factor because Availability is a time ratio and Performance/Quality are count ratios — collapsing them onto one weight (say, always using run time) misrepresents Quality on a line where one asset runs long hours at high yield and another runs briefly at low yield.

None of these tiers are free-standing calculations. A shift report is the finest grain a human reads, and it is also the reconciliation point against the plant’s MES: total and good counts reported here must match, within tolerance, the production-order counts the MES tracks independently. Where the two diverge — double-counted parts at a hand-off station, or a rework loop the sensor stream never saw — the fix belongs in a dedicated MES reconciliation pass upstream, not in the rollup arithmetic itself. Reporting assumes its inputs are already reconciled; it should never be the layer that first discovers a count mismatch, because by the time a plant-level report ships, tracing a discrepancy back to one asset’s one shift is far more expensive than catching it at the source.

Core concept and design contract #Permalink to this section

Reporting sits downstream of OEE formula validation, which guarantees that each per-asset, per-shift record is internally sound — bounded to $[0, 1]$, computed in Decimal, and reconciled against raw state intervals. The reporting layer’s contract is narrower but just as strict:

1. Aggregation is additive-first. A rollup at any tier is computed by summing the additive quantities that feed OEE — run time, downtime, planned time, total count, good count — across the child grain, and only then forming ratios. This is the general form of the identity used throughout this pipeline:

$A_{\text{group}} = \frac{\sum_i \left(\text{Planned}_i - \text{Downtime}_i\right)}{\sum_i \text{Planned}_i} \qquad Q_{\text{group}} = \frac{\sum_i \text{Good}_i}{\sum_i \text{Total}_i}$

$P_{\text{group}} = \frac{\sum_i \left(\text{IdealCycleTime}_i \times \text{Total}_i\right)}{\sum_i \text{RunTime}_i} \qquad \text{OEE}_{\text{group}} = A_{\text{group}} \times P_{\text{group}} \times Q_{\text{group}}$

No step in that chain averages a percentage. $A_{\text{group}}$ is a time-weighted Availability; $Q_{\text{group}}$ is a count-weighted Quality; $P_{\text{group}}$ folds each member’s own ideal cycle time into a group-level ideal-time total before dividing by summed run time, which correctly handles a line where a fast and a slow asset are mixed. This is the identity that the weighted-aggregation rollup guide works through with a full numeric counterexample against the naive mean.

2. Every window is timezone-correct and DST-safe. A shift report must reflect the same wall-clock shift the floor experienced, not a UTC day boundary. Rollups depend on shift boundary logic having already sliced state intervals to the correct local window; the reporting layer must not re-derive shift boundaries independently, or a fall-back DST transition will be counted twice in one pipeline and once in the other.

3. Partial and missing data are visible, not silently dropped. If an asset reports only eleven of twelve expected hours in a shift, the rollup must either exclude it explicitly (and say so) or weight it by its actual reporting time — never treat a partial shift as a full one. A report that goes out with an unlabeled data gap is worse than a report that is late.

4. Reports are reproducible from stored inputs. Given the same raw state intervals and count records, regenerating a report for a closed shift must produce byte-identical output. This is what makes a report defensible in a plant-floor dispute months later, and it is why the implementation below reads from durable continuous aggregates rather than an ephemeral cache.

5. A published report is a versioned artifact, not a live view. Late-arriving MES corrections and reopened shifts mean the “same” shift report can legitimately change after it first ships. The contract requires every report to carry a generation timestamp and an explicit revision counter, and a corrected report must supersede rather than silently overwrite the original — auditors and shift supervisors need to know a number moved, and by how much, not just see a new value appear.

Within the ISA-95 hierarchy, shift and line rollups are a Level 3 (MES/historian) concern, and plant/enterprise rollups cross into Level 4 (business systems) reporting. The validation gate described in the parent section must run before Level 3, so a rollup job can trust every input row without re-checking bounds.

Implementation #Permalink to this section

The reference implementation has two layers: a TimescaleDB continuous aggregate that pre-computes the additive quantities per asset per hour, and a Python rollup function that sums those quantities across whatever grain a report needs (line, plant, arbitrary date range) and forms the ratios once, at the end.

-- Hourly, per-asset additive totals. This is the only place duration and
-- count arithmetic happens against raw state intervals; every report reads
-- from here, never from raw telemetry directly.
CREATE MATERIALIZED VIEW oee_hourly_asset
WITH (timescaledb.continuous) AS
SELECT
    asset_id,
    time_bucket('1 hour', interval_start) AS bucket,
    SUM(planned_seconds)                  AS planned_seconds,
    SUM(downtime_seconds)                 AS downtime_seconds,
    SUM(run_seconds)                      AS run_seconds,
    SUM(total_count)                      AS total_count,
    SUM(good_count)                       AS good_count,
    -- ideal cycle time is constant per asset per hour in practice; carry
    -- it alongside the counts so performance can be recomposed correctly
    AVG(ideal_cycle_time_sec)             AS ideal_cycle_time_sec
FROM oee_interval_facts
GROUP BY asset_id, bucket
WITH NO DATA;

SELECT add_continuous_aggregate_policy('oee_hourly_asset',
    start_offset => INTERVAL '3 hours',
    end_offset   => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

from dataclasses import dataclass
from decimal import Decimal
from datetime import datetime
from zoneinfo import ZoneInfo

import asyncpg


@dataclass(frozen=True)
class RollupTotals:
    """Additive quantities summed across whatever grain is being rolled up."""
    planned_seconds: Decimal
    downtime_seconds: Decimal
    run_seconds: Decimal
    total_count: int
    good_count: int
    ideal_time_seconds: Decimal  # sum(ideal_cycle_time_i * total_count_i)


def compose_oee(totals: RollupTotals) -> dict[str, Decimal]:
    """Form Availability, Performance, Quality, OEE from summed totals only.

    This function never sees a per-asset ratio; it only ever divides sums.
    That property is what prevents the mean-of-means fallacy from creeping
    back in through a refactor.
    """
    if totals.planned_seconds <= 0:
        return {k: Decimal("0") for k in ("availability", "performance", "quality", "oee")}

    availability = (totals.planned_seconds - totals.downtime_seconds) / totals.planned_seconds
    performance = (
        totals.ideal_time_seconds / totals.run_seconds
        if totals.run_seconds > 0
        else Decimal("0")
    )
    quality = (
        Decimal(totals.good_count) / Decimal(totals.total_count)
        if totals.total_count > 0
        else Decimal("0")
    )

    def clamp(v: Decimal) -> Decimal:
        return min(max(v, Decimal("0")), Decimal("1")).quantize(Decimal("0.0001"))

    a, p, q = clamp(availability), clamp(performance), clamp(quality)
    return {"availability": a, "performance": p, "quality": q, "oee": clamp(a * p * q)}


async def rollup_line_shift(
    pool: asyncpg.Pool,
    line_asset_ids: list[str],
    shift_start_local: datetime,
    shift_end_local: datetime,
    tz: str = "America/Chicago",
) -> dict[str, Decimal]:
    """Sum hourly asset totals across a line for one shift, then compose ratios."""
    zone = ZoneInfo(tz)
    start_utc = shift_start_local.astimezone(zone).astimezone(ZoneInfo("UTC"))
    end_utc = shift_end_local.astimezone(zone).astimezone(ZoneInfo("UTC"))

    rows = await pool.fetch(
        """
        SELECT
            SUM(planned_seconds)                             AS planned_seconds,
            SUM(downtime_seconds)                             AS downtime_seconds,
            SUM(run_seconds)                                  AS run_seconds,
            SUM(total_count)                                  AS total_count,
            SUM(good_count)                                   AS good_count,
            SUM(ideal_cycle_time_sec * total_count)           AS ideal_time_seconds
        FROM oee_hourly_asset
        WHERE asset_id = ANY($1::text[])
          AND bucket >= $2 AND bucket < $3
        """,
        line_asset_ids, start_utc, end_utc,
    )
    r = rows[0]
    totals = RollupTotals(
        planned_seconds=Decimal(str(r["planned_seconds"] or 0)),
        downtime_seconds=Decimal(str(r["downtime_seconds"] or 0)),
        run_seconds=Decimal(str(r["run_seconds"] or 0)),
        total_count=int(r["total_count"] or 0),
        good_count=int(r["good_count"] or 0),
        ideal_time_seconds=Decimal(str(r["ideal_time_seconds"] or 0)),
    )
    return compose_oee(totals)

The SUM(ideal_cycle_time_sec * total_count) term is the detail that keeps Performance honest at the line grain: it builds an “ideal production time” per asset (how long the line should have taken to produce what it actually produced, at each asset’s own rated speed) before dividing by the summed run time of the group. A step-by-step scheduled version of this job, including HTML/CSV rendering and idempotent re-runs, is in automating OEE shift reports with Python and TimescaleDB.

Edge cases and failure modes #Permalink to this section

• The mean-of-means fallacy re-enters through dashboards. Even when the batch job above is correct, a BI tool that averages a oee_pct column across rows in a GROUP BY line_id will silently reproduce the fallacy. Publish only the additive totals to the semantic layer, or expose a pre-computed weighted view, so no downstream tool can average a ratio by accident.
• Partial shifts skew the weight, not just the number. An asset that reported only three of eight shift hours due to a network outage still contributes its (small) run time and count into the sums, which is correct — but the report must flag the shift PARTIAL when a member’s expected-vs-actual reporting duration falls outside tolerance, mirroring the PARTIAL flag used in OEE formula validation. Silently rolling up a partial asset alongside full ones understates the line’s true availability without any visible signal.
• Overlapping planned-maintenance windows double-subtract at the rollup, not just the asset. If two assets on a line share one physical maintenance window (a shared conveyor lockout, for instance) and each asset’s record independently subtracts the full window from its own planned time, the line-level sum is still correct per asset — but a plant-level report that also subtracts the shared window as if it were a separate plant-wide outage double-counts it. The overlapping maintenance windows treatment must be applied once, at the finest grain that owns the window, and never re-applied during rollup.
• Bottleneck lines need a different weight than parallel lines. Rolling up a serial line (where one slow station gates the whole line’s throughput) by summed run time is correct; rolling up parallel redundant lines the same way can mask that one line was idle while its twin absorbed all demand. The topology-aware treatment is covered in the weighted-aggregation guide.
• A zero-count asset must not vanish from the denominator silently. An asset that was scheduled but produced zero parts (a changeover that ran the entire shift) contributes total_count = 0 and good_count = 0; the group Quality ratio correctly ignores it since it contributes nothing to either sum, but the group Availability ratio must still include its planned and downtime seconds, or the plant-level Availability will overstate performance by quietly excluding a bad asset.
• Time zone drift between the report window and the continuous aggregate bucket. time_bucket('1 hour', ...) buckets in UTC by default; a shift that starts at 6:30am local time straddles UTC hour boundaries, so the rollup query must filter on the converted UTC range (as shown above) rather than assume hourly buckets align with shift starts.
• A late-arriving correction reopens a report that already shipped. MES rework or scrap events routinely post minutes to hours after a shift closes. If the rollup job has already generated and distributed the shift report, a naive re-run overwrites history with no trace that the number moved. Treat every report as append-only: write a new revision with a superseded_report_id pointer back to the original, and let consumers subscribe to “latest revision for shift X” rather than a single mutable row.
• Asset-to-line and line-to-plant mappings change over time. A line reconfiguration that reassigns Asset 103 from Line 1 to Line 2 mid-quarter must not retroactively rewrite last month’s Line 1 report using the new mapping. Rollup queries need a temporal join against a versioned asset_line_map (valid-from / valid-to columns), not a static lookup table, or historical reports silently drift every time the floor is re-organized.

Verification and testing #Permalink to this section

Two layers of verification matter: a unit test that pins the weighted-vs-mean distinction with a known-answer case, and a reconciliation query that recomputes a published rollup from the hourly aggregate independently of the report job’s code path.

from decimal import Decimal


def test_line_rollup_is_time_weighted_not_averaged():
    # Asset 101: 2h run, 90% OEE-equivalent inputs. Asset 102: 8h run, low yield.
    totals = RollupTotals(
        planned_seconds=Decimal("36000"),   # 10h combined planned
        downtime_seconds=Decimal("3600"),   # 1h combined downtime
        run_seconds=Decimal("32400"),       # 9h combined run
        total_count=9000,
        good_count=6300,                    # 70% quality blended
        ideal_time_seconds=Decimal("25920"),  # sum(ideal_cycle * count) per asset
    )
    result = compose_oee(totals)

    naive_mean = (Decimal("0.90") + Decimal("0.30")) / 2  # what a bad dashboard would show
    assert result["oee"] != naive_mean.quantize(Decimal("0.0001"))
    assert Decimal("0.85") < result["availability"] <= Decimal("1.0")
    assert result["quality"] == Decimal("0.7000")

At the SQL layer, reconcile a published shift report’s headline number against a from-scratch aggregation over oee_interval_facts, bypassing the continuous aggregate entirely, to catch a stale refresh or a bug in the materialized view definition:

-- Independent reconciliation: recompute Availability directly from raw
-- interval facts for one line-shift and compare to the published report.
SELECT
    line_id,
    SUM(planned_seconds - downtime_seconds)::numeric / NULLIF(SUM(planned_seconds), 0) AS availability_recomputed
FROM oee_interval_facts f
JOIN asset_line_map m ON m.asset_id = f.asset_id
WHERE f.interval_start >= '2026-07-14T22:00:00Z'
  AND f.interval_start <  '2026-07-15T06:00:00Z'
  AND m.line_id = 'LINE-07'
GROUP BY line_id;

Any divergence beyond the quantization epsilon between this query and the published oee_hourly_asset-derived report indicates either a stale continuous-aggregate refresh or a rollup bug, and should page the reporting pipeline owner rather than the floor.

Performance and scale considerations #Permalink to this section

The continuous aggregate is the load-bearing optimization: without it, every shift report would scan raw state-interval rows, which on a fifty-asset plant logging sub-second state changes can run into tens of millions of rows per day. time_bucket-based materialized views keep the rollup query bounded to a handful of hourly rows per asset per shift, regardless of how granular the raw telemetry is — the mechanics of choosing bucket widths and refresh cadence are covered in continuous aggregates and downsampling retention.

Refresh policy matters as much as the view definition: a 5-minute schedule_interval with a 5-minute end_offset keeps near-real-time line dashboards current without competing for I/O with the raw ingestion path, while plant and enterprise rollups can refresh hourly or on a nightly batch since their consumers do not need minute-level freshness. Keep the additive columns as integer seconds and integer counts in storage — only the final compose_oee step should touch Decimal division — the same precision discipline applied throughout this pipeline, because summing millions of pre-divided floating-point ratios reintroduces the exact representation error the additive design was meant to avoid.

For enterprise-wide scorecards spanning hundreds of assets across multiple plants, partition the rollup query by plant and parallelize, then combine plant-level totals with one more additive sum rather than re-scanning the full asset-level history — the same tiered structure shown in the diagram above applies recursively at every level, and it is the reason a correctly weighted rollup costs no more at the enterprise grain than at the line grain: it is always one more sum away.

Related #Permalink to this section

• Downtime Classification & OEE Calculation — parent section covering state resolution and OEE math upstream of reporting.
• Automate OEE shift reports with Python and TimescaleDB — the scheduled job that produces per-shift reports from this section’s aggregates.
• Rolling up OEE from line to plant with weighted aggregation — the mean-of-means counterexample and topology-aware weighting in full.
• OEE formula validation — the upstream guarantees this reporting layer depends on.
• Shift boundary logic — timezone- and DST-safe windows that reports must reuse, not re-derive.