IMD SolutionComputing LibraryShop main store

How-to

Backfilling trend history from a plant historian

Edge computing and IIoT Published 8 min read

Short answer

Yes, you can backfill trend history from a plant historian into a SCADA server. Extract the stored records through the historian's native export or ODBC interface, map each source point to its destination object with original timestamps and quality, insert the batches through the destination's documented history methods, and prove the import by reading the interval back from the destination. A saved CSV is a bridge, not proof of import.

On this page
  1. Read the trend before you blame the tag
  2. Map the source points before you extract
  3. Extract with timestamps and quality intact
  4. Stage and validate the batch
  5. Insert through the documented history methods
  6. Prove it by reading the interval back
  7. Choose the arrangement that fits the job
  8. Questions asked

Yes. Backfill trend history from a plant historian by extracting the stored records through the historian's native export or its ODBC interface, mapping every source point to its destination object, inserting the validated batches through the destination SCADA's documented history methods, and then reading the interval back to prove it landed. A CSV file is welcome as transport and as something you can inspect, but a file on a disk is not imported history; only rows present in the destination history store count. The route below works for any enterprise historian on the source side and any SCADA destination that documents how history is inserted. The PI-specific variant of this job is covered in Backfilling PI from an existing plant historian. Run the gate checks in order — each one exists because skipping it produces an error that is expensive to undo.

Read the trend before you blame the tag #

A blank interval on a trend does not mean the tag, driver or controller is faulty. The live value and the historical trace travel different paths: if the current value updates while an earlier stretch stays empty, the live path is fine and the records are missing, or the trace is bound to the wrong history object. If neither live nor historical values appear, resolve the point path before any backfill work. Then run one gate check: display a period you know is already stored in the destination, on the same trend configuration you will use for verification later. Proceed only once that period renders correctly, because an unreadable trend makes every later verification step worthless.

Map the source points before you extract #

Build the mapping sheet before any query runs. A historian point name is not necessarily the destination object name, and matching descriptions are not a dependable key, so match only on identifiers you have verified by hand.

Put one row per point on the sheet:

  • Source point identifier, exactly as the export returns it.
  • Destination history object the rows must land in.
  • Value type: analog, discrete or text.
  • Engineering units and any scaling applied on either side.
  • Expected sample interval, so a 1 s tag is never mistaken for a 1 min tag.
  • Requested start and end timestamps, and whether each boundary is inclusive or exclusive.
  • The time basis of both systems and the quality model of both systems.

Decide the boundary rule now, before any slice exists: adjacent batches need one ownership rule for the shared timestamp, or the sample at the seam gets submitted twice. Gate check: hand-verify several mapped pairs, including one analog and one discrete point whenever both types are in scope.

Extract with timestamps and quality intact #

Prefer the historian's own historical export when it preserves timestamp, value and quality on every row. Where no direct export fits, use the historian's ODBC interface: add it as a query source, pull the rows, and save a bounded batch as CSV for inspection. Query raw or recorded samples, never aggregated or interpolated results — aggregates fabricate rows that were never stored as source events, and evenly spaced output does not tell you which mode you received, so check the query mode in the historian documentation rather than inferring it from the rows.

The timestamp travels with the sample at collection time. AVEVA Application Server, for example, sends the timestamp to its historian inside each VTQ update that is saved as a historical record, so exported rows already carry the original sample time. Never let an export step or a spreadsheet re-stamp rows with the extraction time.

Choose the arrangement deliberately: a direct ODBC-to-import pipeline for repeatable or large transfers, or a staged CSV route when a bounded, reviewable batch matters more than throughput. Gate check: extract a small interval and confirm that every row carries source point, timestamp, value and quality.

Stage and validate the batch #

Treat the CSV as an interchange file. Nothing about a saved file proves the destination will accept it; staging exists so a person can validate the rows before insertion.

  • Keep one unambiguous column per mapped attribute, with a stable timestamp representation: fixed-format text with an explicit UTC offset, not anything locale-dependent.
  • Stop spreadsheet mangling: disable automatic date parsing, watch decimal separators, and do not let long identifiers or discrete state codes get truncated.
  • Validate every value against the destination data type and reject malformed rows instead of repairing them by eye.
  • Translate quality deliberately: unknown or uncertain source quality must arrive as unknown or uncertain at the destination, never silently as good.
  • Sort rows by destination object and timestamp, then detect duplicate object-and-timestamp pairs before submission.
  • Keep the original export untouched as a read-only comparison copy.
  • Do not interpolate gaps to make the series look continuous, and never confuse a genuine zero with a missing value; interpolation is a separate engineering decision.

Gate check: reopen the staged file as plain text, not through the spreadsheet, and compare the first, middle and last records against the source query.

Worked example: the window is 3 days, which is 3 × 86,400 s = 259,200 s, and 40 analog points at a 1 s raw rate produce 40 rows per second, so 259,200 × 40 = 10,368,000 rows to move.

Assume the pilot run shows the interface accepting 250,000 rows per transaction — an example planning input, because the real limit comes from your own pilot and depends on the interface, the row width and the server load. Split on time rather than row counts: 1-hour slices give 3,600 s × 40 rows/s = 144,000 rows per batch, inside that limit, and 259,200 s / 3,600 s = 72 batches exactly, each owning a start-exclusive, end-inclusive stretch of the window. At roughly 60 bytes per staged row the whole set is about 10,368,000 × 60 B ≈ 622 MB, so staging disk is trivial — the real risks are timestamps, quality and duplicates, not capacity.

Insert through the documented history methods #

Build the inserter against the destination's documented interface, not against a generic CSV layout. Destinations differ: Geo SCADA Expert documents how to import historic data into point and accumulator histories through its Historic aggregate methods on the CHistoryBase object, while AVEVA Historian documents an import where you choose how the file data integrates into storage, such as appending new values as a streamed import. Read what your import mode does when a row's timestamp already exists in the store, because append and replace behave very differently at a duplicated instant.

Then run this sequence:

  1. Resolve every source point to its destination history object before a single row is written.
  2. Pilot on a non-production destination, or on a tightly bounded production interval approved through your change process.
  3. Submit the smallest chronological batch and log every accepted and rejected row with destination, timestamp and reason.
  4. Read the pilot interval back through a trend or query, not from the file, before scaling up.
  5. Run the remaining slices in order with checkpoints, so a restart resumes at a known boundary instead of resending an unknown range.

Give the import account the minimum: read on the source, write on the selected destination history objects, nothing else. One caution belongs here: an import changes what operators see on live trends, so run it against a production server only under change control, like any other write to the system.

Prove it by reading the interval back #

A successful insert call is not proof; the only evidence that counts is reading the interval back from the destination's own store. Render the imported window on the same time axis as the existing history and check four things. Values: compare the first, middle and last samples per point against the source query — the same rows you checked at staging, now from the destination side. Timestamps: pick one known event and compare wall-clock times; if the source shows a compressor start at 05:30 and the destination trend shows it at 07:30, a +2 h double conversion happened somewhere in the chain, the classic symptom of a local-time export reconverted as if it were UTC. Quality: uncertain and bad samples must render with their own styling, not blended into good data. Boundaries: adjacent slices must meet with no gap and no repeated sample at the seam.

When the imported interval renders alongside the old history with correct values, timestamps and quality, the backfill is done. Until then, it is not.

Choose the arrangement that fits the job #

For a one-time catch-up after an outage, the staged CSV route ends here: validated, inserted, read back, and archived together with the mapping sheet. For a recurring job — nightly catch-up after store-and-forward gaps, or a planned migration in nightly slices — script the whole chain so the extract, the validation and the insertion run as one checkpointed job whose results log somewhere a failure will actually be seen.

The export interface is a property of the historian you own, and it should be a selection criterion for the next one; the trade-offs are set out in Plant historian selection by scale and support needs. And if a recurring job earns a permanent home, put it on a dedicated plant-floor host instead of an engineering laptop that gets borrowed: a fanless mini PC mounted in the cabinet is the usual fit for a service that talks to both the historian and the SCADA server.

Questions asked

Why can't I point the SCADA trend at my CSV file directly?

A trend reads the history store, not a file. The CSV becomes history only after something calls the destination's documented import function or history insertion method. Some products ship a built-in history import utility that reads staged files; others expect a short script that calls the history methods. Until one of those runs and a read-back confirms the rows, the file is just staged data.

Should the staged timestamps be in UTC or plant local time?

Use whatever the destination's documented import expects, and convert exactly once, in the importer. If both sides accept UTC, move UTC and let the client render local time. If one side stores local time, record the offset and its daylight-saving behaviour on the mapping sheet, because a zone that shifts during the backfill window is the classic cause of a one-hour band of misplaced rows.

How do I stop the boundary sample between two batches being written twice?

Give every slice one ownership rule before anything runs: make each slice include its start timestamp and exclude its end timestamp, or the reverse, and apply it to every slice and every retry. Then deduplicate on destination object plus timestamp before submission as a second net. With both checks in place, an identical boundary sample cannot arrive twice, even when a slice is rerun after a failure.

What if the source historian will only give me aggregated or interpolated data?

Push back to raw or recorded samples first, because aggregates create rows that were never stored as events. If the only available source is aggregated — for example hourly averages from a retired system — import them as deliberately derived data: record the aggregate type and period on the mapping sheet and treat the backfilled interval as reconstructed, not measured.

Can I delete a bad backfill from the history store afterwards?

Plan as if you cannot. Most SCADA history stores are built to append, and few give a documented way to delete a range of imported rows; the practical recovery is restoring the store from backup, which costs far more than a pilot run. That is why the procedure inserts the smallest batch into a test destination and reads it back before production sees anything.

Keep reading

  • How-to

    Backfilling PI from an existing plant historian

    Split the historian-to-PI backfill into four independent stages — extract, intermediate store, external compression, destination load — and qualify throughput with a measured pilot. Rows divided by elapsed seconds, not tag count, sets the schedule: 12,000 tags from 2020 at 500 rows/s is about 203 days. Check the native Historian 2023 replication route before writing pipeline code.

    Edge computing and IIoT

  • Comparison

    Evaluating Ignition historian with TimescaleDB for your plant

    The Gold Technology Provider announcement is a reason to test TimescaleDB behind Ignition, not proof it replaces your historian. No footprint, compression or performance number is documented for this pairing. Adopt it only after a replay test on your exact Ignition 8.3 build measures growth, ingestion continuity, query response and backup/restore against your current store.

    Edge computing and IIoT

  • Selection guide

    Plant historian selection by scale and support needs

    Pick the tier first: a control-room or automation-vendor historian for one site's process trends, an enterprise platform only when several sites share one operational history, and a self-built time-series stack only when your team owns connectors, dashboards, backups and upgrades. Size by stored events per second — the sum of each tag group's tags times its stored events per second — times retention, never by tag count alone.

    Edge computing and IIoT

Why Geo SCADA history needs points to backfill Citect trends