The challenge
The OEE templates found downtime two ways. Automatically: any step that ran longer than its planned baseline produced a downtime capsule over the overrun. Manually: operators logged downtime in Vantage with a reason code and an estimated duration. The two were published side by side, and neither knew the other existed.
Tracing the calculation from baseline to export showed it was worse than side by side. Each step could reach the downtime report as three rows: the whole step, the overage past baseline, and the operator’s entry. As an illustration, a step planned for 4 hours that ran 6, with 3 hours logged by the operator, reported 11 hours of downtime. Removing the overlap between the overage and the operator entry only gets that to 9. The right answer, 2 hours unexplained on top of what the operator logged, also meant deciding what to do with the whole-step row and capping the manual duration.
Underneath that, operators sometimes logged more downtime than the step could have lost, or logged overlapping entries for the same step. Nothing caught it. The totals just drifted.
Our approach
- Trace before touching anything We documented the live calculation end to end before writing a formula. That surfaced the three-rows-per-step problem, and a trap: one reporting item filtered on a misspelled property name, so a whole branch had never produced output. Fixing it would change the report, so it goes last in the rollout, not first.
- One number for both failure modes Per step: planned minus actual duration, plus operator downtime clipped to the step. Negative means unexplained downtime still owed a reason; positive means the operator logged more than the step lost. The operator’s time is credited against the automatic overage instead of added beside it.
- Prove it on synthetic data first Every scenario — clean step, partial entry, over-entry, overlapping entries, entries crossing a step boundary, and a no-entry control — was built on a dev server and checked against hand-calculated answers. The new formulas run as parallel copies beside the live ones, so cutover is one paste and rollback is one paste.
- Decide the boundary rules explicitly Time from the step start onward belongs to the step; time before it belongs to the gap before the step, if there is one; an entry that touches neither is flagged whole. Our first mechanism couldn’t be built, and the per-capsule workaround was far too slow at the tree root. The version that works splits each entry at the gap boundary and sums by key.
- Flag it; don’t overwrite it Over-entries and overlaps raise andons rather than silently correcting the operator’s number. A scheduled Python job matches andons to entries on asset, batch and step and writes the result onto the entry itself, so reviewers can filter to what needs attention.
The outcome
The reconciliation is built and proven on synthetic data, and it is moving into a pilot on the client’s own items. Operators still log a best-guess estimate and move on. A support team reconciles the next morning in a triage meeting that already exists, starting from a view filtered to flagged entries, grouped by step and batch, with the adjustment each one needs.
- Operator downtime is credited against automatic downtime, not stacked
- One per-step number shows unexplained downtime and over-entry
- Andons are written onto the entry, so review is a filter, not a hunt
- Every synthetic scenario matched its hand-calculated answer
- Cutover is a paste into the existing items, with one-paste rollback