I have walked onto a lot of plants over 22 years, and one ritual shows up on nearly all of them. An operator opens the SCADA trend screen at the end of a shift, exports it to Excel, and emails it up the chain. Nobody planned it. It grew because the reports were needed and the tools to make them properly were never wired up. This is a how-to for pulling that ritual out by the root, without ripping out your SCADA or historian.
The pattern on almost every plant
The shape is always the same. At handover, the operator selects a set of trend pens on the SCADA client, exports the window to CSV or straight into Excel, and saves it to a shared drive with a filename like Shift_B_flows_final_v2.xlsx. A supervisor opens that file, copies the numbers into a daily report template, and adds a few notes. At month end, someone (often the same supervisor, sometimes a young engineer) stitches thirty of those daily files together to reconstruct monthly production tonnage and energy consumption, aligning timestamps by hand and filling blanks with whatever looks reasonable.
Everyone treats this as normal. It is not. It is a manual extract-transform-load process, run by people, under time pressure, at shift change, with no tests and no version control. The output feeds production accounting, energy billing, and management KPIs. The inputs are correct because the SCADA and historian are usually solid. The damage happens entirely in the copy-paste layer that sits between the tag and the report.
What breaks, and why it keeps breaking
Before you fix a process, list its failure modes plainly. Here is what actually goes wrong in the export-to-Excel chain, in the order it tends to bite.
| What breaks | Why it happens | What it costs you |
|---|---|---|
| Manual timestamp alignment | Two tags logged at different intervals get pasted side by side and eyeballed into the same rows. | Flows and totals bound to the wrong minute. Small drift, wrong monthly tonnage. |
| Missing data during comms outages | When the link to a PLC or RTU drops, the trend has a gap. In Excel the gap looks like a blank cell. | Someone types in a "sensible" number, or the average silently spans the hole. |
| Unit-conversion errors | A tag is in kg/s, the report wants tonnes/shift. The factor lives in a formula nobody checks. | One wrong constant multiplies through every row and every downstream KPI. |
| No audit trail | Numbers are typed, overwritten, and re-saved. There is no record of what changed or who changed it. | When a figure is questioned, nobody can reconstruct how it was produced. |
| Reports that die with a person | The macros, filters, and tribal knowledge live in one spreadsheet on one laptop. | When that person leaves, the report stops. Reporting reverts to guesswork. |
Notice that four of the five failures are not calculation errors. They are integrity errors. The pipeline cannot tell you when it is uncertain, so it always sounds confident. That is the real problem to solve.
The target state: one path from tag to report
The fix is not a bigger spreadsheet. It is a short, boring pipeline where every stage does one job and leaves a record. Data flows from the historian or SCADA database, through a scheduled extract, into a reporting store, then into a semantic model that the Power BI reports sit on top of. A shift and production calendar feeds the model so totals land in the right shift, the right day, and the right month automatically.
A few points that matter in practice. The extract is scheduled, not manual, so it runs whether or not the operator remembers. It reads from the historian rather than from a live SCADA client, because the historian already solves buffering and store-and-forward. If you are still deciding what to read from, I have written separately on SCADA historian integration and on how the platform choice shapes this in choosing a SCADA platform.
The semantic model is where units live once, in one place, tested once. The Power BI layer only presents. It never converts, never guesses, never fills a gap. And every measure it shows should trace back to a KPI you deliberately chose, which is the argument I make in building a KPI tree. For the reporting layer itself the Power BI documentation covers scheduled refresh and dataset design well enough that I will not repeat it here.
Handling gaps and bad quality without lying
This is the stage the spreadsheet never had, and it is the whole point. Industrial data arrives with a quality signal attached, and a report that ignores it is a report that fabricates numbers whenever the plant hiccups.
In OPC, every value carries a quality alongside it, and quality falls into three buckets: Good, Uncertain, and Bad. In Classic OPC DA the quality is a 16-bit field whose top two bits carry that general classification, with lower bits giving a more specific sub-status and limit information. In OPC UA the same idea is carried by a 32-bit StatusCode, where the severity sits in the top bits: Good means the server completed the read normally and the value is valid, Bad means it did not and the value is not valid, and Uncertain means the server produced a value but could not stand fully behind it, for example a last-known value held during a comms loss or a reading past a sensor range. The details and the canonical definitions live with the OPC Foundation , and it is worth reading them rather than trusting any vendor's paraphrase, including mine.
The rule that matters most
A value with Uncertain quality must never enter a KPI without being flagged. Uncertain is not a softer kind of Good. It is the server telling you it could not fully vouch for the number. Carry the quality all the way into the reporting store, and let the report decide whether to include, exclude, or mark it. What you must not do is drop the quality at the extract and let an Uncertain reading average in as if it were solid.
So the data-quality rules in the pipeline are concrete. Only Good values roll into a total by default. Uncertain and Bad values are kept, tagged, and either excluded or shown with a visible marker. Gaps are represented as gaps, with an explicit coverage figure ("this shift total is built from 96% of expected samples"), not silently interpolated. Unit conversion happens once, in the model, from a documented factor. And every extract writes a run log, so the audit trail exists whether or not anyone ever asks for it.
The trap to avoid
The most common mistake I see when teams first automate is a time-weighted average that spans a comms outage as if the plant kept running steadily through it. The average looks smooth and reasonable, which is exactly why it is dangerous. If the link was down for forty minutes, the honest answer is not a smooth curve. It is a gap and a coverage percentage. Averaging across a hole does not remove the missing data, it just hides that it was missing.
A shift report rebuilt, and the hours it gives back
Here is a real shape of engagement, with the numbers rounded. A processing plant ran three shifts a day. Each shift the operator spent about 25 minutes on the export and cleanup, and the supervisor spent another 20 minutes assembling the daily report. Month end added a full day of manual reconciliation to rebuild production and energy totals from the daily files.
We pointed a scheduled extract at the existing historian, landed the tags in a reporting store with quality carried through, built one semantic model that held the unit conversions and a shift and production calendar, and published two Power BI reports: a shift report that regenerates automatically at handover, and a monthly rollup that no longer needs a human to assemble. The operator now reviews and annotates rather than builds. The supervisor reviews rather than re-types.
| Task | Before (manual) | After (pipeline) |
|---|---|---|
| Shift export & cleanup (per shift) | ~25 min | ~5 min review |
| Daily report assembly | ~20 min | ~5 min review |
| Month-end reconciliation | ~1 full day | ~1 hour check |
| Recovered per week (approx.) | ~8 to 10 hours of operator and supervisor time | |
Eight to ten hours a week is the visible saving. The bigger one is invisible: the monthly figures now reconcile because they are built the same way every time, the quality of every total is knowable, and the report keeps running when the person who used to build it moves on. That last point is the one that sells the project to a plant manager who has been burned before.
Where to start
You do not need a platform migration to begin. Pick one report, usually the shift report, because it repeats most often and hurts most. Point a scheduled extract at the historian, carry quality through, model units once, and publish. Prove it on that one report, measure the hours it returns, then widen. The Excel export does not die by decree. It dies when the automated report is simply better and nobody reaches for the spreadsheet anymore.
Independence note: I am not affiliated with, sponsored by, or reselling for any SCADA, historian, or reporting vendor named here, including Microsoft and the OPC Foundation. The approach above is platform-agnostic and reflects my own field experience, not a vendor relationship. Verify quality-code behaviour against your own stack and the official OPC specifications before relying on it.
Written by Muhammad Abbas
CMMS / CAFM Manager & Enterprise Integration Specialist · 22+ years across ERP, EAM, CAFM and enterprise integration.
Work with me