Equipment records that could not be tied to what was spent on them
A water utility trying to decide which pumps to replace finds that its asset register, its work orders, and its ledger each identify the same equipment differently.
Illustrative example. The organization and its data are invented to show a typical pattern. This is not a customer story.
What they hold
- An asset register listing each pump, valve, and meter, with a location and an install date.
- Work orders from field crews, recording what was done, where, and the parts used.
- Operating expenses from the finance system, coded to cost centers.
- Readings from monitoring equipment, exported by the vendor's software.
The mess
One pump, several identifiers
The register calls it PS-0412. Crews write PS412 or Pump Station 4 unit 12 on work orders. The finance system dropped the leading zero when the asset list was loaded from a spreadsheet, and knows it as PS-412. Nothing links the three.
Site names typed from memory
The same site appears as Riverside PS, Riverside Pump Stn, and River Side. Costs grouped by site are split across the spellings.
Readings in mixed units
Flow is recorded in liters per second by newer meters and gallons per minute by older ones, in the same column. The unit lived in the meter's configuration and was lost in the export.
Install dates that are placeholders
Equipment older than the register was given the first of January of the year the register was created. Those assets all look the same age, and younger than they are.
What it costs
- Maintenance cost per asset cannot be calculated, so replacement is decided by opinion instead of by which equipment is actually costing the most.
- Expenses that cannot be tied to an asset sit in a general account, and the true cost of running a site is understated.
- A flow alarm threshold set for one unit is applied to readings in another, producing false alarms that crews learn to ignore.
- Age-based replacement planning treats the oldest equipment as mid-life.
- Regulatory reporting on asset condition takes a team weeks to assemble by hand.
What clean looks like
- Each asset has one identifier, stored as text with its zeros intact, and a saved crosswalk records every alternative it has been known by.
- Work orders and expenses carry that identifier, so cost per asset is a query instead of a project.
- Site names resolve to one agreed list.
- Every reading states its unit, and values are converted to one unit before comparison.
- Unknown install dates are marked unknown, with an estimate held separately where one exists.
Problems in this example
- Identifiers that lost their leading zerosA code that starts with zero, such as a postal code or account number, was read as a number and the zeros were dropped.
- The same entity in two systemsOne person or organization exists in two systems under different identifiers, with nothing linking the records.
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Mixed unitsValues in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
- Defaults mistaken for dataA value that looks real was filled in automatically by a form or a system, and nobody actually chose it.