Case Studies
Three problems, described without the specifics.
Everything below is anonymised. No client or employer is named, no client data appears here, and the details that would identify an organisation — industry, size, location, systems by name, item numbers and dollar figures — have been removed rather than disguised. What is left is the part that transfers — the problem as it presented, what it actually turned out to be, and what was built. Where a number would matter, it is called out as something to measure rather than asserted.
Why there are no percentages on this page. Consulting case studies tend to open with a figure — 82% faster, 40% fewer errors. Those numbers are only worth anything if someone measured the before state, and in most operational work nobody did, because the before state was nobody's job to instrument. Where a baseline was captured, it is named. Where it was not, saying so is more useful than a number nobody can reproduce.
A KPI console for people who were reading the operation from memory
The situation. An operations team ran receiving, picking and purchasing on a SQL Server–backed ERP. Operational questions — is receiving keeping up, are we paying what the purchase order said, who picked what yesterday — were answered by whoever had been there longest. The data existed. Nothing assembled it.
What was built. A reporting console with two audiences and two very different trust levels. Supervisors get the full set in a browser. The floor gets a subset on wall-mounted displays, driven by scoped display tokens.
That split turned out to be the design constraint that mattered. Several reports carry cost and margin. A screen on an operations floor is visible to everyone who walks past it, including visitors and contractors. So report definitions carry a flag: anything with dollars in it is backend-only, the server refuses to attach it to a display token, and it is never listed in the publicly enumerable set. The restriction lives in the report definition rather than in the page that renders it, because the page is the thing most likely to be rewritten later by someone who does not know the rule.
The receiving metrics. Volume was the easy part. The ones that changed conversations:
| Metric | What it measures, and the modelling decision behind it |
|---|---|
| Purchase price variance | Receipt cost minus purchase-order line cost. Positive means the business paid above what the PO said. This is the number that gets attention, because it is the gap between what purchasing agreed and what accounts payable will actually be billed. |
| On-time percentage | Receipt date against the due date, preferring promise date, then required, then original. Lines with no due date are excluded from the ratio rather than silently counted as on time — an undated line is missing data, not a success, and rolling it into the numerator flatters the number in exactly the cases nobody is watching. |
| In-full percentage | Only open receipts carry an in-full flag; the posted-history table has no equivalent. History rows are therefore excluded from the ratio via an explicit marker instead of scoring zero, so a full month does not read worse than a single day purely because most of it has been posted. |
| Lines per PO | Receiving effort per order. A cheap proxy for whether purchasing is consolidating or drip-feeding the dock. |
Two of those four rows are decisions about what not to count. That ratio is normal. Most of the work in an operational metric is deciding which rows are evidence and which are absence of evidence, and a dashboard that gets it wrong is worse than no dashboard, because it is confidently wrong in the direction nobody checks.
Open versus posted. The same period can span unposted receipts and posted history, which live in separate tables with different columns. Today reads from open receipts only. Week and month union both. Getting this wrong is the classic ERP reporting bug: numbers that look right on a daily view and quietly drift on anything longer.
Picker productivity. A parallel set covers lines picked, quantity, active hours and first and last pick per picker. The awkward part was not the aggregation, it was identity: pickers appear as initials on the transaction, and the mapping to a human runs through a chain of fallbacks — a description field, a Windows login, the local part of an email address, and finally an explicit unmapped bucket. Unmapped is displayed rather than hidden. An unattributed pick is a data-quality finding, and hiding it turns a fixable mapping gap into a permanently wrong leaderboard.
Worth measuring if you run this
- PPV dollars surfaced in the first full month, against what was being caught manually before.
- Time from “how did receiving do last week” to an answer, before and after.
- Share of picks landing in the unmapped bucket, tracked down over time as the mapping is cleaned.
The report that worked on Today and timed out on Month
The symptom. The same KPI report returned instantly for Today and timed out for week-to-date and month-to-date. Reported, reasonably, as “the dashboard is slow.”
What it actually was. The week and month views filter invoice-history on a pick-date column. That column was not indexed. The indexes that existed on the table covered the order number, a history id and line, the lot, and a rowguid — every access path except the one the date filter needed. So the date range forced a full scan of the entire invoice-history table. Today was fast because it read a different, far smaller table; the period views were slow because they touched history, and the shape of the query meant touching history meant reading all of it.
This is the most common performance root cause in ERP reporting and the one most often misdiagnosed, because the symptom scales with the date range and therefore looks like data volume. It is not a volume problem. It is an access-path problem that volume makes visible.
Why the fix needed more care than the diagnosis. The tables shipped with the ERP; they were not locally created. Adding an index to a vendor schema is a supportability decision before it is a technical one, so the work went to the platform owner before anything was applied. Beyond that:
- Index creation takes a schema-modification lock on SQL Server Standard Edition. It blocks live users for the duration of the build, so it belongs in a maintenance window, not in the afternoon it was diagnosed.
- An idle query window holding an open transaction will block the build and create a lock chain that looks, to everyone else, like the database has frozen. Close the tabs first. This sounds trivial until it happens.
- Size and current index state get captured before anything changes, so the change can be evaluated and reversed.
The transferable part. Written up in full as why an ERP report is fast on today and times out on month-to-date. In short: when a report is fast on a short window and dies on a longer one, resist the instinct to optimise the query. Look at what the date filter is actually filtering, and whether anything indexes it. The fix is frequently one index. The risk is almost never in the index — it is in applying it to a vendor schema, on the wrong edition, at the wrong time of day.
Worth measuring if you run this
- Report runtime before and after, at each period setting. Capture it before you apply the index; you cannot go back for it.
- Logical reads for the query, which is the honest measure and does not move with server load.
- Index build duration, so the next maintenance window can be planned rather than guessed.
One label service instead of a label program per label
The situation. Labels were being produced from several unrelated places — contract bins, order lines, invoice lines, purchase-order lines. Each had grown its own path to a printer. Adding a label type meant writing another program, and the layouts drifted apart because nothing held them to a shared definition.
What was built. A single label service with the sources defined as data rather than as code. Configuration tables in the ERP database describe each label source: the query that produces the rows, the fields it exposes, and the template it maps into. A .NET API serves those sources, an admin panel maintains them, and the user-facing side becomes one interface that changes behaviour by configuration.
Output is native printer command language for industrial label printers, rather than a rendered image. For a floor printer this matters — it prints faster, positions predictably at the label edges, and does not degrade when the printer is asked to scale.
The design decision worth stealing. Adding a label type should be a configuration change, not a deployment. Once the source is a row in a table — a query, a field list, a template — the fifth label type costs a fraction of the first, and the layouts stay consistent because they share one rendering path. Most label sprawl is not a printing problem. It is the absence of a place to define a label source.
Worth measuring if you run this
- Elapsed time to add a new label type, before and after.
- Reprints caused by layout or alignment faults, per week.
- Number of distinct programs that can talk to a printer — the number you are trying to drive to one.
The pattern across all three
None of these started as the problem they turned out to be. “We need a dashboard” was really a question about which rows count as evidence. “The dashboard is slow” was one missing index on a vendor table. “We need another label program” was the absence of anywhere to define a label source.
That is the common shape of operational software work: the request describes the symptom accurately and the cause not at all, and the useful first move is always to reproduce the symptom against the actual data before agreeing to build anything.
Related
- All insights — articles and case studies in one place.
- Operations modernization — the larger engagement these sit inside: consolidate onto one system of record, then optimize on top of it.
- CRM & ERP integration — the integration and pipeline work that feeds reporting like this.
- How the analysis actually gets done — the working method behind these engagements.
- Why your ERP report times out on month-to-date — the technical write-up of Case 02.
Recognise any of these?
If a report is slow on longer date ranges, or a number nobody trusts is driving a decision, the first conversation is short and costs nothing.