“How much trouble has that pump actually caused?”
The work orders call it P-101A. The downtime sheet calls it “Transfer pump A”. Finance has a few costs against a work order and another invoice against the whole pumping package.
Someone puts the extracts beside each other. There are records in every system. Getting them to tell the same story takes a bit more work.
I'd start by asking what “that pump” means. The physical pump installed today? The duty position that several pumps have occupied? Or the package, including the motor and the standby pump?
That sounds a bit fussy until someone asks whether to replace it. Then the difference matters quite a lot.
Let me use a fictional example. All the identifiers, records, dates and amounts below are made up.
Start with the thing you're trying to understand
At the fictional North Quay site, pump EQ-017, serial SN-88, occupies transfer duty position P-101A until 1 July 2026. On that date, a replacement pump, EQ-042, serial SN-99, takes over. The duty position keeps its name.
For this example, an equipment record identifies a physical pump. A separate location record identifies the duty position. The installation history records which pump occupied that position and when.
You might have a different arrangement in your systems. I'd want someone to explain it before I started joining extracts.
The old pump's seal work belongs in its history even after it leaves the position. A question about the transfer duty may need the history of both pumps. And a job on the motor needs its own equipment reference, with a relationship to the assembly where that's useful. A shared location doesn't make the motor and pump the same item.
In my draft reliability-culture handbook, I described an equipment-centric model connecting the equipment record, maintenance work and the consequences of failure. My 2017 MSc dissertation proposed a related model after finding inconsistent links between systems in the historical case studied. That finding belongs to that case. I'd check the links in front of me before drawing conclusions about another site.
Here, I'll use a small model to show what I'd check.
Open full-size image (opens in a new tab)
An original miniature model for this fictional example. Each source keeps its own records. Accepted mappings connect them to equipment; specific work-order links need separate evidence.
Keep the names, then explain what they refer to
Suppose the extracts contain these records:
| Source | Record | Original reference | Detail |
|---|---|---|---|
| Work orders | WO-501 | P-101A | 12 June: seal repair |
| Work orders | WO-502 | P-101A | 20 June: follow-up check |
| Work orders | WO-503 | P-101A | 9 July: coupling check |
| Downtime | DT-11 | Transfer pump A | 12 June, 08:00–10:00 |
| Downtime | DT-12 | Transfer pump A | 18 June, 14:00–15:00 |
| Costs | C-1 | WO-501 | £600, seal materials |
| Costs | C-2 | WO-501 | £400, labour |
| Costs | C-3 | WO-502 | £200, labour |
| Costs | C-4 | Transfer package | £900, shared service invoice |
For this small example, these are the complete extracts being reconciled. The times use the same site clock. The costs are posted charges on the same VAT-exclusive basis, with no credits or duplicate invoices.
The name “Transfer pump A” looks like a good match for P-101A. I'd still want evidence. In this example, the downtime register's equipment list explicitly identifies the site and duty position. The installation record then tells me which physical pump was there on each event date.
That gives a dated interpretation: both June downtime events refer to EQ-017. So do WO-501 and WO-502. The July job belongs to EQ-042.
Keep the source reference alongside the accepted equipment reference. I'd also keep the source system, site, record ID, applicable dates, supporting evidence and who accepted the mapping. If another site uses P-101A, its records need their own context.
Simply removing spaces and hyphens won't answer those questions. It might help find candidates. It could also make 2 different references look wonderfully alike.
Watch what happens when the rows multiply
There are 3 work orders, 2 downtime events and 4 cost lines in the extract. They describe different things. That's the grain of each table: what one row actually means.
WO-501 has 2 cost lines. That still makes it 1 work order.
Both June work orders and both downtime events now point to EQ-017. If I join each work order to every downtime event for that equipment, I get 4 combinations. Counting those rows would give me 4 work orders and 4 events. The source contained 2 of each for that pump.
At that order-to-event stage, the 3 hours of downtime appear against each work order, producing 6 hours. If I then add the 3 individual cost lines, the table grows to 6 rows. The £1,200 in charges appears twice, producing £2,400. WO-501's extra cost line repeats its event durations again, taking the apparent downtime to 9 hours.
Lovely tidy table. Wrong answer.
I'd retain the separate tables and calculate each measure from its own records. A report can filter them by the same accepted equipment identity without pretending that every job caused every event. If I need to connect a particular outage to a particular work order, that needs an event-to-order link supported by the source context.
In this example, the operations log and repair record explicitly link DT-11 to WO-501. DT-12 has no confirmed work-order link. Knowing which pump stopped doesn't tell me which job explains the stoppage.
Say what the totals include
For EQ-017, the accepted records support 2 work orders, 2 downtime events and £1,200 in recorded charges linked through those work orders.
The 2 downtime intervals don't overlap. Under this example's rule of counting elapsed unavailable time for this physical pump, they total 3 hours. That says nothing about lost production. A standby pump might have kept the process running.
The shared £900 invoice remains at package level. There's no evidence here for splitting it between the pumps. I wouldn't divide it equally just to fill the equipment report.
Across the whole cost extract, £1,200 plus £900 reconciles to £2,100. The equipment-attributed amount and the package amount need to remain visible. Calling £1,200 the pump's “total maintenance cost” would hide what the extract doesn't establish.
Open full-size image (opens in a new tab)
Reconcile back to the source records. The unresolved package charge stays visible, and the replacement pump keeps its own work history.
For a larger example, I'd need explicit rules for overlapping downtime, shared events and shared costs. I'd also ask whether the records describe the failed item, an affected item or the system that lost its function. An outage attributed to a pump failure can affect several other items. Copying its full duration onto each item and adding them up would answer a different question.
Give AI a useful job, and someone the decision
This is where I'd consider AI assistance: preparing candidate mappings for review.
I'd supply the source-system names, site, original references, descriptions, event dates, the downtime register’s equipment list, the equipment register, serial numbers where available and dated installation history. I'd ask it to return candidate equipment IDs, the evidence for each, conflicts and records it couldn't resolve. I'd ask it to preserve the original values.
A hypothetical suggestion for DT-11 might read:
Candidate: EQ-017. The source equipment list identifies “Transfer pump A” as North Quay duty position P-101A. The event date falls within EQ-017's recorded installation period. No direct serial number appears in the downtime record. Check the equipment-list reference and installation history before accepting.
That's an illustrative output written for this article. No matching service has been run or tested here.
In the fictional team, Maya, the equipment-data steward, owns the mapping decision. She checks that evidence with the operations supervisor who maintains the downtime register, then accepts or rejects the candidate and records her reason. For DT-12, she can accept the equipment identity while leaving its work-order link unresolved.
If the register and installation history disagree, the output should show both. Maya needs to resolve the conflict with the people responsible for those records. An AI confidence score wouldn't settle which pump was physically installed.
The package invoice needs a different decision. Maya can identify the package reference; the cost owner must decide whether any allocation is justified. Matching a name doesn't supply a financial allocation rule.
I'd keep pending suggestions out of accepted equipment totals and show the pending records separately. If Maya later corrects a mapping, keep the earlier decision, the new evidence, who changed it and when. That lets someone explain why a historical report changed.
Try one equipment history together
I'd start with one pump, its replacement history and a handful of records from each system. Sit down with the people who maintain those records and ask them to follow the links.
Can they explain every accepted match? Can they see the unmatched records? Do the work-order counts, downtime intervals and cost lines still reconcile after the links are applied?
A small mapping register would help that conversation. One row could hold a source reference, candidate equipment, applicable dates, evidence, decision and responsible person. Keep the exceptions beside the accepted matches. The aim is to leave the next person enough information to understand the decision.
If you're still deciding which question the report should answer, From maintenance data to decisions starts there. Can your maintenance data answer the question? looks more closely at testing whether the records are fit for a particular use.
For this check, I'd finish by asking someone to open the original records behind the pump's figures. If they can follow the history, explain the gaps and tell you who accepted the links, you've got something useful to build on.