OEE Data Model: Events, Fields and the Joins That Matter

Key takeaways

• OEE is not a stored number. It is a calculation over five objects: a shift calendar, machine state events, count events, an ideal cycle time master and a scrap record.
• The argument in every plant is about the first object, not the formula. Moving one 25 minute changeover out of planned production time lifts the same shift from 73.5% to 77.8%, with nothing changed on the floor.
• Availability times Performance is fixed at 76.2% in that shift no matter how the stops are split. The state table decides which factor carries the loss, the calendar decides the number.
• Where you put the counter changes the answer. Counting good pieces as total pieces reports Quality 100.0% and hides 62 rejects inside Performance.
• To rebuild last Tuesday six months later you need the calendar row and the ideal cycle time row that were valid that day, not the ones valid now.

OEE is a calculation, not a field

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.

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.

The fight is over the first object

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.

The shift calendar table

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?

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.

Why the date field is not the clock date

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.

The policy that decides everything

Three plants with identical machines report three different OEE numbers because they deduct different things.

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.

The machine state event table

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.

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.

The state list

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.

The contiguity rule

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.

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.

The count event table

Counts look like the simple part of the model and are usually the part that quietly breaks it.

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.

Which station belongs in which factor

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.

Rejects belong to the scrap record

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.

Ideal cycle time belongs to the part and the tool

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.

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.

What a stale ideal cycle time does

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.

Units are part of the contract

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.

Work center and machine: the hierarchy OEE rolls up through

ERP usually books orders against a work center. Availability, performance and quality are measured on a machine. The model has to hold both, and say how one rolls up into the other.

Three fields on the asset table are enough to hold this: asset_id, parent_asset_id and asset_level. Add work_center_code where it differs from your ERP key, so the order join does not depend on a name.

Rolling up parallel machines

Do not average machine OEE. Add up earned minutes and planned minutes, then divide.

Example: FIL-02 earns 330.6 minutes of 450, so 73.5%. A second machine in the same work center earns 200 minutes of 300 planned, so 66.7%. The work center is 530.6 divided by 750, which is 70.7%. A plain average says 70.1%, and the gap grows as planned time differs.

Machines in series

Do not add machines in a line together. Line OEE uses the rate of the step that limits the line, and the good count at the end of the line. Keep the machine OEE too, because that is where you find the loss.

Then decide which level carries the ideal cycle time, and write it down. If the order sits on the work center and the state sits on the machine, join them on asset_id and time.

The joins

Five objects are only a model once you can say which key meets which key.

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.

Worked example: one shift, rebuilt from events

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.

The four state durations add to 450 minutes, which is the first thing to check before computing anything.

1. Version one: the changeover stays inside

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.

• Availability = 392 ÷ 450 = 87.1%
• Ideal count over run time = 392 × 5 = 1,960 pieces
• Performance = 1,715 ÷ 1,960 = 87.5%
• Good pieces = 1,715 minus 62 = 1,653
• Quality = 1,653 ÷ 1,715 = 96.4%
• OEE = 87.1% × 87.5% × 96.4% = 73.5%

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%.

2. Version two: the changeover is deducted

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.

• Availability = 392 ÷ 425 = 92.2%
• Performance = unchanged at 87.5%
• Quality = unchanged at 96.4%
• OEE = 92.2% × 87.5% × 96.4% = 77.8%

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.

3. What does not move

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.

• Run time drops to 372 minutes, so Availability = 372 ÷ 450 = 82.7%
• Ideal count drops to 1,860, so Performance = 1,715 ÷ 1,860 = 92.2%
• OEE = 82.7% × 92.2% × 96.4% = 73.5%, unchanged

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.

4. The station swap

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.

• Performance = 1,653 ÷ 1,960 = 84.3%, down 3.2 points
• Quality = 1,653 ÷ 1,653 = 100.0%, up 3.6 points
• OEE = 330.6 ÷ 450 = 73.5%, unchanged again

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.

5. What the four versions say together

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.

Micro stops and the threshold field

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.

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.

Rebuilding last Tuesday six months later

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.

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.

Stamp the result with its inputs

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.

Failures are a different table

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.

Five questions to ask a vendor about the OEE data model

1. Show me the shift calendar table. Which fields decide planned production time, and can I change the policy without editing history?
2. Are state events contiguous and non overlapping? Compute Availability both ways on a real shift and show me that the two agree.
3. Where does the count come from? Which station, and can I hold a total count and a good count on the same line at the same time?
4. Where does the ideal cycle time live? If it is a field on the machine rather than on the part and tool pair, the Performance figure will drift with every product mix change.
5. Can you rebuild a shift from twelve months ago? Ask for the same shift twice, with a calendar change in between, and check that the old number still reproduces.

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.

How Fabrico helps

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.