Key takeaways
A system that stores oee_percent as a column on a shift row cannot be audited.
A defensible OEE is derived every time it is asked for, from event tables that each hold one fact recorded by one source at one moment.
Five objects are enough. Everything else in a plant data model is context hanging off them.
| Object | What it answers |
|---|---|
| shift_ | How much time was the machine supposed to produce? |
| machine_ | What was it doing, minute by minute? |
| count_ | How many pieces, where, and of what kind? |
| part_ | How fast should this part run on this tool? |
| scrap_ | Which pieces were not good, and why? |
Notice what is missing from that list: a table of OEE results.
You may cache results for speed, but the cache must be rebuildable from the five objects, and a rebuild must land on the same value.
The surrounding schema, the asset, meter, plan and part tables that a maintenance system adds around these five, is covered in our CMMS data model guide.
Plants rarely argue about multiplying three factors together.
They argue about whether the Tuesday afternoon changeover was planned production time, and whether the two hours with no order should count.
That argument is a row in the shift calendar, which is why this page starts there and not with the formula.
For the formula itself, with every step and the rounding rules, see our OEE calculation guide.
This table is the denominator of your plant.
It answers one question per asset per shift: how many minutes was this machine expected to make parts?
| Field | What it holds |
|---|---|
| calendar_ | Own key, one row per asset per shift |
| asset_ | Machine or line the shift applies to |
| date | Production date, not the clock date |
| shift_ | 1, 2 or 3 on this asset |
| shift_ | Timestamp with time zone |
| shift_ | Timestamp with time zone |
| planned_ | Minutes removed from the denominator |
| break_ | Paid stops removed from the denominator |
| is_ | False on a plant holiday or shutdown |
Planned production time is then a formula, not a typed value.
planned production time = (shift_end minus shift_start) minus planned_downtime_min minus break_min.
Store the two deductions separately, because breaks and planned stops get argued about separately and audited separately.
A night shift that starts at 22:00 on Monday and ends at 06:00 on Tuesday belongs to one production date.
If the date is derived from each event timestamp, that shift splits across two days and every daily report is wrong at the edges.
Three plants with identical machines report three different OEE numbers because they deduct different things.
| Policy | What is deducted | Effect |
|---|---|---|
| Strict | Breaks only | Lowest OEE, changeovers punished |
| Common | Breaks plus scheduled changeover | Middle |
| Loose | Breaks, changeover, planned maintenance, no demand | Highest OEE, least useful |
None of the three is wrong as arithmetic. All three are wrong if the policy is not written down and versioned.
Changing this one table moves OEE further than most improvement projects do, and it moves it in an afternoon.
That is why the calendar needs an owner, a change record, and a rule that every deduction carries a reason.
This is the table that carries Availability, and it is the one most often built wrong.
Each row is a period during which the asset was in exactly one state.
| Field | What it holds |
|---|---|
| event_ | Own key, one row per state period |
| asset_ | The machine the state belongs to |
| state | From a fixed list, never free text |
| state_ | Timestamp with time zone |
| state_ | Null while the state is open |
| duration_ | Derived, stored for query speed |
| reason_ | Mandatory on every stopped state |
| entered_ | Person, null when fully automatic |
| source | PLC, sensor, vision, terminal, inferred |
The source field earns its place the first time somebody disputes a stop.
A stop the PLC reported and a stop an operator typed at a terminal are different evidence, and only one of them can be replayed.
The reason code list itself is a separate design problem with its own rules, covered in our guide to downtime reason code design.
| State | Counts as |
|---|---|
| RUNNING | Run time |
| DOWN_UNPLANNED | Availability loss |
| DOWN_PLANNED | Availability loss, or deducted |
| IDLE_NO_ORDER | Deducted when the policy says so |
| OFF | Outside planned production time |
Keep the state list short and stable, and let the reason code carry the detail.
Adding states is how a five state model becomes a forty state model that nobody can map to the six big losses.
State events for one asset must be contiguous and non overlapping, with no gaps.
Written as a rule your database can enforce: for one asset, the next event's state_start equals the previous event's state_end, and the events cover the whole calendar window.
The reason is not tidiness. It is that Availability can be computed two ways, and the two answers must agree.
| Method | Run time |
|---|---|
| Sum RUNNING durations | 392.0 min |
| Planned time minus stopped durations | 450 minus 58 = 392.0 min |
Now let one breakdown overlap the following RUNNING event by 40 seconds, because the operator closed the stop after the line had already restarted.
The first method returns 392.67 minutes and reports Availability as 87.3%. The second still returns 392.0 minutes and reports 87.1%.
A fifth of a point is not the problem. The problem is that your system now has two Availabilities and no way to say which is true.
Overlaps do not arrive one at a time. Thirty overlaps of 40 seconds in one shift are 20 minutes, and the first method then reports 91.6% against the second method's 87.1%.
Run both methods nightly on every asset and alert on any disagreement above a few seconds. It is the cheapest data quality check in the whole model.
Counts look like the simple part of the model and are usually the part that quietly breaks it.
| Field | What it holds |
|---|---|
| count_ | Own key, one row per counting pulse or batch |
| asset_ | The machine that produced it |
| part_ | What was being made |
| wo_ | Order the pieces belong to |
| station | Where on the line the counter sits |
| count_ | good, total or reject |
| qty | Pieces, in the part's own unit |
| ts | Timestamp with time zone |
| source | PLC, sensor, vision, terminal |
The station field is the one people leave out, and leaving it out is how two departments end up with two OEE numbers for one line.
A count taken at the infeed and a count taken after the checkweigher are different quantities, and they are not interchangeable.
| Station | Counts | Belongs in |
|---|---|---|
| Infeed | Pieces started | Neither factor |
| Machine outfeed | Total pieces made | Performance |
| Checkweigher or final gate | Good pieces | Quality |
Performance asks how fast the machine ran, so it needs the total count at the machine outfeed, good and bad together.
Quality asks how many of those survived, so it needs the good count at the last gate that judges them.
If a plant has only one counter and it sits after the checkweigher, it does not have a Performance number. It has a combined figure wearing a Performance label.
Keep count_type = reject as a summary line only, and put the reason on a separate scrap record that points back at the count.
One reject count of 62 pieces can carry four different causes, and a single column cannot hold four.
The code list that makes those causes comparable is covered in our scrap reason code taxonomy.
Ask a plant where the ideal cycle time lives and most will point at the machine.
That is wrong for any machine that runs more than one part, which is almost all of them.
The same press runs a two cavity mould at one rate and a four cavity mould at another, on the same asset, on the same day.
| Field | What it holds |
|---|---|
| part_ | The part being made |
| tool_ | Mould, die or fixture used |
| ideal_ | Seconds per piece at full speed |
| rated_ | Pieces, kg, metres or litres |
| effective_ | First date this rate applies |
| effective_ | Null while current |
The key is the pair, part_no plus tool_no, and the two date fields make it a history rather than a setting.
Never update an ideal cycle time in place. Close the old row with an effective_to and insert a new one, or you have silently rewritten every OEE the plant ever reported for that part.
Take the shift in the worked example: 392 minutes of run time and 1,715 pieces made.
The master says 12.0 seconds per piece, so the ideal count is 1,960 and Performance is 87.5%.
Suppose the mould was reworked last year and the machine now genuinely cycles in 11.4 seconds, but nobody closed the old row.
The true ideal count is 2,063 pieces and Performance is 83.1%, so the stale row is flattering the line by 4.4 points every shift it runs.
A stale ideal cycle time is invisible in every report, because the number it produces is perfectly plausible.
The only defence is a measured rate with a date on it, which is what a time study data model exists to produce.
Store rated_rate_uom next to the number and make every query read it.
A line that reports in kilograms and a master that holds seconds per piece will produce an OEE, and it will be meaningless.
Five objects are only a model once you can say which key meets which key.
| From | To | On |
|---|---|---|
| state event | calendar | asset_id, start inside the window |
| count event | calendar | asset_id, ts inside the window |
| count event | part or tool master | part_no and tool_no, ts in the date range |
| count event | work order | wo_no |
| scrap record | count event | count_id |
| every table | asset | asset_id |
The join to the part master is on three things, not two: the part, the tool and the moment, because the rate has a validity window.
A rebuild that joins on part and tool alone will pick today's rate and apply it to last year's shift.
The join to the asset must survive a plant reorganisation, so point it at a stable surrogate key, as set out in our asset hierarchy data model.
The order side of the join, the fields a production order needs before any of this works, is covered in our production work order data model.
Asset FIL‑02 runs part PART‑4471 on tool MLD‑12 for one day shift.
Nothing below is typed by a supervisor. Every figure is derived from rows in the five tables.
| Input | Value |
|---|---|
| Shift window | 06:00 to 14:00, 480 min |
| break_min | 30 min |
| RUNNING events | 392 min total |
| DOWN_PLANNED, changeover | 25 min |
| DOWN_UNPLANNED, breakdown | 18 min |
| DOWN_UNPLANNED, no material | 15 min |
| ideal_cycle_s | 12.0 s, so 5 pieces per minute |
| Total count, machine outfeed | 1,715 pieces |
| Reject count, checkweigher | 62 pieces |
The four state durations add to 450 minutes, which is the first thing to check before computing anything.
Planned production time is 480 minus 30 breaks, with nothing else deducted.
That gives 450 minutes, and the changeover is treated as an Availability loss.
Check it in one step, which is the calculation an auditor will actually run.
1,653 good pieces × 12 s = 19,836 s = 330.6 minutes of earned time, and 330.6 ÷ 450 = 73.5%.
Now set planned_downtime_min to 25 on the same calendar row, because the plant decides a scheduled changeover is not production time.
Planned production time becomes 480 minus 30 minus 25 = 425 minutes, and the changeover leaves the numerator and the denominator together.
The one step check agrees: 330.6 ÷ 425 = 77.8%.
The same machine, the same 1,653 good pieces, the same 330.6 minutes of earned time, and 4.3 more points of OEE.
The two results reconcile exactly, because the numerator never moved: 73.5% × (450 ÷ 425) = 73.5% × 1.0588 = 77.8%.
That ratio is the whole trick. Any OEE gain that equals the ratio of two denominators is a calendar edit, not an improvement.
Hold the calendar at 450 minutes and split the stops differently, and the OEE refuses to budge.
Suppose the micro stop threshold is lowered so that 48 short stops of 25 seconds each, 20 minutes in total, are finally captured as stop events instead of hiding inside RUNNING.
Availability lost 4.4 points and Performance gained 4.7, and the product stayed where it was.
The reason is an identity worth writing on the wall: Availability × Performance = total count × ideal cycle time ÷ planned production time.
In this shift that is 1,715 × 12 s ÷ 450 min = 76.2%, whatever you do to the state table.
Last, move the counter instead of the calendar.
Take the checkweigher count of 1,653 and feed it into Performance as if it were the total made, which is what happens when a line has one counter at the end.
A plant in this state reports perfect quality forever and sends improvement teams to chase a speed problem that is really 62 scrapped pieces.
Two counters, one at the outfeed and one at the final gate, fix it.
| Change | OEE |
|---|---|
| Changeover inside planned time | 73.5% |
| Changeover deducted | 77.8% |
| Micro stops captured | 73.5% |
| Counting good pieces as total | 73.5% |
Only the calendar changed the score. The other two edits moved loss between factors and left the total alone.
That is the sentence to remember when a vendor shows you an OEE dashboard: ask what is in the denominator before you look at anything else.
Every state model needs a number that says how short a stop can be and still become an event.
Store it as micro_stop_threshold_s on the asset, never as one global setting.
A bottling line that cycles in 1.2 seconds and a press that cycles in 90 seconds cannot share a threshold, because a 20 second pause means two different things on them.
| Asset type | Sensible threshold |
|---|---|
| High speed packaging | 5 to 10 s |
| Discrete assembly | 20 to 60 s |
| Slow cycle press or oven | 2 to 5 min |
Now take one unrecorded 25 second stop on FIL‑02, whose threshold is set at 30 seconds.
Availability does not move at all, because the 25 seconds sits inside a RUNNING event.
Performance absorbs it instead: at 5 pieces per minute those 25 seconds are just over 2 pieces that never appear in the count.
Repeat that 48 times in a shift and you have the 20 minutes and 100 pieces from the example above, filed as slow running.
Two pieces are noise and 100 pieces are a project, which is why the threshold is a field with an owner and not a default somebody accepted at install.
Our guide to micro stops covers what to do once you can see them.
The real test of an OEE data model is not today's dashboard.
It is whether somebody can reproduce a specific shift from last March, to the same decimal, after the calendar policy has changed twice and three parts have new cycle times.
| What you must keep | Why |
|---|---|
| Raw state events with source | Recompute run time independently |
| Counts per station | Split Performance from Quality |
| The calendar row as it stood | The denominator of that day |
| The cycle time row valid that day | The earned time of that day |
| Reason code list version | Make old Paretos comparable |
| calc_ | Detect a formula change |
A calendar edited in place and a cycle time updated in place both rewrite history silently, and neither leaves a trace in any report.
Make both of them insert only, with effective_from and effective_to, and make every OEE query pass the shift date into the lookup.
When you do cache an OEE result, store the identifiers it was built from next to it.
At minimum: calendar_id, the cycle time row key, the count of state events consumed and a calc_version.
Then a rebuild that disagrees with the cache tells you which input changed, instead of starting an argument about whether the dashboard is broken.
A downtime event is not a failure record, and merging them costs you both.
One stop can produce no failure record and one repair can produce two, so keep the link nullable and keep the fields apart, as described in our equipment failure record data model.
The fifth one sorts the rest, because a system that cannot rebuild the past was never storing events in the first place.
If you are still choosing how to collect the events, our comparison of machine monitoring systems covers the PLC, sensor and camera options.
Fabrico collects the events this model is built on, straight from the machine, through PLC connections, IoT sensors and computer vision cameras.
It holds machine states, stop reasons and counts as records, and calculates availability, performance, quality and OEE in real time from them.
Micro stops are detected and tracked rather than absorbed into slow running, and downtime is reported per machine and per reason.
Because a full CMMS sits in the same platform, your team can raise a work order or a preventive plan against the machine that keeps stopping, and technicians reach the machine record by scanning its QR code from the iOS or Android app.
The AI assistant answers questions about one machine's losses in plain language, and the platform is ISO 27001 certified.
Want to see your own shift rebuilt from events instead of typed into a spreadsheet? Book a 30 minute demo with a Fabrico consultant, no commitment, or contact us with your current table structure and we will tell you what is missing.
Five: a shift calendar, a machine state event table, a count event table, an ideal cycle time master keyed on part and tool, and a scrap record.
OEE itself should be derived from these on demand, not stored as a field.
On the combination of part number and tool number, with effective_from and effective_to dates, not as a single field on the machine.
A machine that runs several parts has several ideal cycle times, and each one needs its own validity window.
Because Availability can be computed by summing run time or by subtracting stop time from planned time, and overlaps or gaps make those two methods disagree.
A 40 second overlap in one shift already shifts Availability from 87.1% to 87.3%, and thirty of them shift it to 91.6%.
Performance needs the total count at the machine outfeed and Quality needs the good count at the last inspection gate, so a complete model holds both.
With only an end of line counter, Quality reports 100% and the scrap hides inside Performance.
The raw state and count events, plus the shift calendar row and the ideal cycle time row that were valid on that production date.
If either of those two is edited in place rather than versioned, historical OEE cannot be reproduced.
Connexion
Blog
