**TL;DR**
- Joining ERP (enterprise resource planning) data tables is the step most "we don't have the data" stories skip, because the numbers are usually there in separate tables.
- In our made-up example, jobs that follow a big changeover run 20% long, and no single report shows it.
- A zero and an unmeasured number look identical on most dashboards, and they mean opposite things.
- Any vendor who can't show you a case where the tool said "can't tell" is selling a sales deck, not a method.
- Tonight, pull two tables for one part family and sort every number into measured, inferred, or unmeasured.
Dale is a composite plant manager, not a real person or a real plant. Every number about his shop below is illustrative, made up to show the method, and we label it that way.
At 4:50 on a Thursday, Dale had a buyer on hold and a price to give for 500 brackets. The buyer wanted it before they left for the day. Dale had 30 years of ERP data behind him and no idea what to say.
The ERP is the software that runs his orders, schedule, and labor hours. It has been running his plant since before his newest press operator was born.
The whole price hinged on one question. Does a press changeover from a big part to a small part make the next job run long? Dale had been guessing at that answer for years, and he said "let me call you back in ten."
He called the part-time IT contractor instead. "Don't we have this somewhere?" he asked. The contractor said, "You have it in two places. Nobody ever put them next to each other."
Why every report is right and the answer is still missing
An ERP is built in modules. Each module wrote its own tables, often built at a different time by a different vendor.
So Dale has a report for each module, and each one is correct inside its own lane. The scheduling report shows what ran when. The labor report shows hours per person.
His question crosses lanes, so no single report can answer it. That's why "we don't have the data" is usually the wrong sentence. The truer one is "nobody joined these tables."
The join, and the first thing that broke
A join is a standard database operation. It matches rows in one table to rows in another using a shared field. In a plant, that field is usually the job number.
The contractor didn't touch the live system. They copied the tables, joined the copies, and left production alone. Then they matched the scheduler's rows for job 41872 to the labor rows for job 41872, and the planned start landed next to the actual hours on one line.
Then it broke. Job numbers carried a suffix in one table, like 41872-02, and none in the other. About 1 in 5 rows (illustrative) wouldn't match cleanly.
Here's the first refusal case. When a join is that leaky, the honest result is "this join is unreliable," not a report on the rows that happened to match. They fixed the suffix first, and only then trusted anything.
The surprise: setup time was never in the books
Next, Dale asked for the setup hours on the big-to-small changeover. The contractor looked and said, "There aren't any."
Many shops log labor as one block of hours per operation, and Dale's shop did. Nobody ever created a separate setup code, so the changeover sat inside the run time. In our made-up case, the press ran 9 hours, the ledger said 9 hours, and a 2.5-hour changeover was buried in that number.
That skews the run rate his estimator had used for years. It also means the setup estimate may never have existed on its own, because nobody ever measured it.
Lean manufacturing has a name for attacking this problem. SMED, short for single-minute exchange of die, is a method tied to engineer Shigeo Shingo that aims to bring changeovers under ten minutes. You can't aim a method like that at a number you've never separated out.
It warps the scorecard too. OEE, or overall equipment effectiveness, normally counts setup and adjustment as lost availability. If setup hides in run time, OEE puts that loss in the wrong bucket.
The contractor found a workaround. The scheduler stamps when the machine actually started, and the first-piece inspection record carries a time. That gave them an **inferred** setup time, and they labeled it that way.
The sequencing answer nobody had measured
The scheduler knows the order each machine ran its jobs. Join consecutive jobs and you get pairs: big part then small part, or small then big. Now you can ask whether the second job in a pair runs long.
In our made-up example, every job that follows a big changeover ran 20% long. No report had ever shown that, because it needs the scheduler and the labor ledger side by side. Dale had suspected it for years and could never prove it.
Then Dale asked the tempting question: "So if we flipped the order, we'd save that time?" The contractor said, "I can't tell you that."
Here's the counterfactual rule. To claim "a different order would have saved time," you need real cases where the other order happened. Dale always runs big parts first, so he has no cases of the alternative.
That lane is **UNMEASURED**. It is not zero, and it is not "no effect." It means the data can't support the claim, and the report says so.
The estimate that had been wrong for three years
While the tables were open, Dale asked about the bracket family's old quotes. The work order holds the estimate, and the labor ledger holds the actual hours. Join them by part family over time and you get a drift line.
In our illustrative numbers, this part family had run 12% over its estimate for three years. On a 100-hour job, that's 12 hours the quote never charged for.
New quotes often start from old estimates. So the drift quietly re-prices every job quoted from that family, in the wrong direction.
One caution before anyone reacts. If the actual hours include setup and the estimate left it out, the "drift" is partly a labeling gap. The contractor checked that first and said which part they had verified.
Two traps that make a good chart lie
**A zero is not an unmeasured number.** Say a dashboard shows 0 scrap for a line. Does that mean the line made no scrap, or that nobody typed scrap in?
If the scrap field was added in 2019, every 0 before that date is unmeasured. Most dashboards paint both the same color, and that's how a missing field becomes a good-looking trend.
A real method keeps three labels. **Measured** means a record exists and was checked. **Inferred** means we built it from a proxy. **Unmeasured** means no source supports it.
**Trimming outliers is a decision, not a cleanup step.** Take 40 setup tickets from our made-up press. Thirty-seven are between 35 and 55 minutes, averaging about 45, and three took 4 hours because a die cracked.
Average all 40 and you get about 60 minutes. Trim the three and you get about 45. That's a 25% swing from one choice.
Neither number is wrong, because they answer different questions. One says what a normal setup takes, and the other says what setups really cost you, cracked dies included. So write the trim rule down before you look at the result, and show both numbers.
How to check a number two independent ways
Before anyone acts on an operations number, get it from two sources that don't share a parent. Labor-ledger hours against scheduler start and finish stamps is one pair. Total ledger hours against payroll hours for the same week is another.
If the two agree within a margin you set in advance, call it measured. If one source plus a proxy gets you there, call it inferred. If neither can speak, call it unmeasured and don't build a decision on it.
This is slower than a chart that just says 12%. It's also the only way to find out the 12% was a mismatched job number.
How the call ended
At 5:40, Dale called the buyer back. He quoted the 500 brackets with the real run rate, the inferred setup time, and a plain note: "sequence effect measured at 20%, alternative order unmeasured."
The buyer, in this made-up story, didn't care about the labels. They cared that the number came with a reason. That's the payoff of a method that is willing to say "can't tell."
At 6:40pm, Dale walked past the front-office phone. The message light was blinking. A request for quote from a different buyer had come in at 4:52, while he was on the other line, and it had gone to voicemail.
A lead sitting in a voicemail box is a lead sitting in nobody's table. It never got joined to anything.
Questions to ask before you buy another dashboard
A dashboard shows only what someone joined for it. These questions show you what they joined and what they skipped.
- Show me a case where your tool says "can't tell." If there isn't one, the tool never refuses, and that is a warning sign.
- How does it tell a zero from a field nobody filled in?
- Which tables feed each chart, and what is the join key?
- What happens to outliers by default, and can I see both versions?
- Can I check one number against my own ledger by hand?
What to do tonight
You don't need to buy anything to start. You need one part family, one machine, and a few hours of someone who can read the database.
1. Pick **one part family** and one machine that you quote often. 2. Ask your ERP vendor or IT contact for a **read-only export** of the scheduler and labor tables for the last 12 months. 3. Check whether the **job numbers match** across the two. If many don't, stop and fix that first. 4. Sort every number you can pull into **measured, inferred, or unmeasured**. 5. Write your **outlier rule** down before you see the averages.
Where we fit, and where we don't
We build websites, not ERP reports, so we're not the tool for this join. The voicemail light is our lane.
Our AI receptionist answers calls and chats 24/7, captures the lead, and can book the next step, so a 4:52 request for quote doesn't wait until morning. Call the hotline yourself and hear what it does with an after-hours call.
Our sites are built with the same AI systems we sell, and we ship them in days. We also rebuild dead pages, and one of our demo builds is a free rebuild we did for a local roofer to show the craft, not a paying client. That's the proof we offer: a line you can call and a site you can click.
If you want to hear what an AI receptionist does with an after-hours call, dial 605-315-3900 or see how we build.
Questions people actually ask
What does joining ERP data tables mean?
It means matching rows from two tables on a shared field, usually the job number, so the scheduler's rows and the labor ledger's rows for the same job sit on one line. This is a standard database operation called a join, and the PostgreSQL documentation's tutorial on joins between tables describes it. It can run on a read-only copy, so the live ERP never changes.
Why does setup time hide inside run time?
Many shops log labor as one block of hours per operation. If nobody ever created a separate setup code, the changeover hours sit inside the run hours, and no report can split them. Check whether your ledger has a setup code and whether it was used before you trust any run-rate number.
Should I buy a dashboard before I join my tables?
Not yet. A dashboard can only show what has been joined, and if job numbers don't match across tables it will show a confident blank. Test the join on one part family first, and sort each number into measured, inferred, or unmeasured before anyone acts on it.