THE NEW COLLECTION · Power BI and maintenance systems

Three checks before you trust a CMMS export

You've exported 5 rows. How many work orders completed in October?

I'd want to know a bit more before putting a number in the report. There might be several history entries for one order. The equipment tag might mean different things at different sites. And that status column might describe the order today.

Here's a small fictional extract. All the records, people, definitions and dates are invented. It contains selected status-history entries, with the current order status copied onto each row at extraction on 5 November 2026. It isn't a complete history.

The question is: how many distinct North Quay work orders in this extract entered Complete during October 2026? Lena, the fictional CMMS process owner, accepts that definition, including orders whose status later changed. “Complete” is the site's agreed status for this question; it doesn't establish a failure or prove the quality of the work.

The supplier hasn't confirmed that every qualifying October event is in the file. I'd need the export filters checked and coverage reconciled against the source population before reporting a site total.

WO Site / equipment Event ID Entered status Event date Current status
701 NQ / P-07 E11 Complete 18 Oct Closed
701 NQ / P-07 E11 Complete 18 Oct Closed
701 NQ / P-07 E12 Closed 2 Nov Closed
702 NQ / P-07 E13 Complete 20 Oct Open
703 SQ / P-07 E14 Complete 21 Oct Complete

Fictional annotated extract: NQ = North Quay; SQ = South Quay. All event dates are in 2026. The second E11 row is an identical exported copy. “Entered status” belongs to the dated event; “Current status” belongs to 5 November.

1. What does one row represent?

Count the October Complete rows and you get 4. That includes E11 twice.

I'd ask Ravi, the fictional export supplier, to explain how he built it, then trace E11 back to the source history. For this example he confirms one event was exported twice. Counting the copy would make 701 look like 2 completed orders.

The intended grain, meaning what one row describes, is a status event for a work order. Keep E12: it's a different event for 701. Deleting every repeated work-order number would throw away real history.

After setting aside the confirmed E11 copy, there are 3 distinct represented orders with an October Complete event across both sites. Keep the original extract and record the duplicate rule so someone can repeat the check.

2. Which equipment identifier is this?

Every row says P-07. It's tempting to treat that as one pump.

In the invented equipment register, NQ/P-07 and SQ/P-07 identify different duty positions. Lena checks that register and accepts site plus tag as the position key for this extract. Order 703 belongs to South Quay. Within this extract, that leaves 2 North Quay orders: 701 and 702.

That answer changed because the site scope was applied. Checking the identifier exposed why P-07 alone couldn't safely define that scope.

A question about a particular physical pump would also need its equipment identity and installation dates. One equipment record, several systems works through that distinction and the links between separate records.

3. What do the status and date mean?

Filter these North Quay rows to current status Complete and you'll get 0 orders. On 5 November, 701 is Closed and 702 is Open.

For the agreed October question, use the entered status and event date, then count distinct orders within the site. Both 701 and 702 have a Complete event in October, giving 2. Their later status doesn't remove those events from this definition.

I'd ask Lena to check those events in the source orders and accept how the report treats later status changes. The extract alone doesn't explain why 702 is Open now. It also can't establish the backlog at an earlier date, because the history is incomplete.

Record which date you're using, its time zone and the period boundaries. In this example the accepted dates are site-local dates from 1 October up to, and excluding, 1 November. An export date or a last-updated date would answer a different question.

An original diagram traces 4 October Complete rows in the extract to 3 distinct represented orders after the confirmed E11 copy is excluded, then 2 North Quay orders. Among these supplied North Quay rows, current Complete status gives 0 while the October event definition gives 2. Site-wide coverage is unverified.Open full-size image (opens in a new tab)

Original MDP illustration using the fictional extract above. Each result applies to the supplied rows. Coverage of every qualifying October event at the site remains unverified.

My 2017 MSc dissertation checked what the case company's collected data could support, including the equipment references used in its analysis. These 3 checks extend that approach to a small export. They're new practical checks, rather than findings about every CMMS. Building a maintenance reporting system people can use follows the wider route into a model, report and review.

AI could help draft a profile of the supplied rows: repeated E11, repeated P-07 across sites, and different event/current statuses. That's a proposed use, not an AI trial performed here. I'd ask for the row references beside every flag. Ravi checks and accepts the row-grain interpretation; Lena accepts the equipment and status meanings against the source records. A plausible profile still needs that conversation.

I'd keep a three-check export card beside the file: question and extraction time, row grain and duplicate rule, equipment key and scope, status/date definition, coverage checked, and the person who accepted each interpretation. Fill it in with the export supplier and process owner before trusting the count.

Part of the Maintenance Data Pro article collection.