How-to
Backfilling PI from an existing plant historian
Short answer
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.
On this page
- Decide what the backfill must preserve
- Why swapping the query interface will not fix it
- Run the backfill as four restartable stages
- Pilot throughput before you schedule anything
- Working the numbers for 12,000 tags backfilled from 2020
- Check the Historian 2023 replication route before writing code
- The decision, case by case
- Questions asked
Backfilling 10–15k tags from a Wonderware Historian 2017 into PI is a pipeline problem, not a query-tuning problem. Split the job into four independent stages — source extraction, an intermediate event store, external compression, destination load — so each can be measured, tuned and restarted on its own. Qualify throughput with a pilot and compute it as returned rows divided by elapsed seconds; the schedule comes from measured rows, never from tag count. On this migration's own numbers — 12,000 tags backfilled from 2020, one pilot window of 1,000 tags over 5 days returning two million rows in about 67 minutes — the whole job runs about 203 days at 500 rows/s. The decision: measure your rows and your sustained rate, demand the rows-per-second your deadline implies, and check the native Historian-to-PI replication route before writing any pipeline code.
Decide what the backfill must preserve #
A query that returns rows has proven retrieval, not fidelity. Recorded events, interpolated samples and aggregates are three different products: interpolation can manufacture values nobody measured, an aggregate pass can drop short-lived excursions, and only the recorded-event stream is a faithful raw history. Fix the retrieval mode per tag group in the manifest before tuning batch sizes, because it changes row counts and everything downstream.
| Manifest entry | Set it from | What it decides |
|---|---|---|
| Retrieval mode | The retrieval options of the source query or SDK request | Whether the export carries recorded events, calculated samples or summaries |
| Tag mapping | Source tag inventory against the destination point list | Which histories move; similar names are not identity, so map explicitly |
| Timestamp rule | Export and import configuration | One clock end to end, so five years of data cannot shift by an hour |
| Quality and data type | Source metadata and export fields | Keeps bad values and discrete states distinct from good analog readings |
| Backfill start, per tag | The point where live collection takes over | Defines the handoff instant; no gap and no overlap |
Before scaling, run the chosen retrieval mode once on a deliberately awkward set: a fast-changing analog, a steady analog, a discrete, and a tag whose value never changes but whose quality does. Check that event times, values and quality all arrive in the destination. A mode that loses any of the four will lose them across the whole backfill.
Why swapping the query interface will not fix it #
Engineers running this migration report that the historian SDK and direct queries perform about the same, which fits: both go through the historian's own retrieval engine rather than reading the archives directly. The live connector is the same story — it uses the SDK too, and it is built for current ranges, not multi-year backfill, so it is not a demonstrated faster path either.
The tagname like '%' experiment does not turn the query path into an archive dump. It only widens the selection each request carries, and per-request work can go up with it. All-tags-short-windows and tag-batched-longer-windows are both legitimate shapes; pick between them on measured throughput and on what the live historian tolerates, not on intuition.
Offline extraction deserves a committed answer. Historian archive files are the product's internal storage; their meaning depends on the historian software and its metadata, so copying them is not a migration interface. For a Historian 2017 source, plan on there being no supported offline archive extractor. If the vendor documentation does describe an offline procedure for your versions, prove it on a restored test copy first: export a known window online, run the same window through the offline method, and compare event for event. Without that proof, the realistic choices are the query path you already have and the replication route at the end of this article.
Run the backfill as four restartable stages #
Take a documented PI Server backup before the first bulk load, and if live collection shows gaps or late data while extraction runs, stop and cut concurrency — the historian's first job is today's data.
- Freeze the manifest. Retrieval mode per tag group, explicit tag mapping, one timestamp rule, quality handling, and the per-tag backfill start where live collection resumes. Nothing extracts until these are written down.
- Extract into an intermediate event store, partition by partition. One partition is one tag batch, one time window, and the retrieval configuration that produced it. Store the raw recorded events, not the processed output. Record each partition's row count, earliest and latest timestamp, file checksum and completion status; mark a partition complete only after its file is closed and validated, and retry a failed partition as a whole unit. Keep the tag mapping and export settings beside the files — a filename is not provenance.
- Compress outside the destination. The backfill write path may not apply the same swinging-door filtering as live collection — engineers running this exact migration report that it does not — so verify the behavior of your PI version first. If filtering is bypassed, run each tag through the same two settings its live point uses: the compression deviation and the minimum interval between archived values. Write the compressed output to separate files and keep the raw extract untouched, so compression can be re-run at different settings without querying the historian again.
- Prepare the destination, then load. Create and register archives covering the backfill period before loading; AVEVA documents this with
piartool -acdor the Archives tool in PI SMT. Load with a documented bulk interface — apiconfigscript run from the PI\adm directory against a CSV, or the Archive Editor plug-in in PI SMT, again as documented for your PI version — and log rows loaded per partition. - Prove boundaries and retries. Check a pair of adjacent partitions for the event that sits on their seam. Repeat one partition end to end and confirm the reload produces the same dataset. Then verify the handoff instant against live collection: history ends exactly where the live feed begins.
Run these stages on a plant-side machine rather than on the historian or the PI server. The intermediate store is a small historian in its own right, and the storage arithmetic in sizing a self-hosted Node-RED, InfluxDB and Grafana historian carries over directly. If you would rather drive the extract, compress and load steps as flows than as scripts, installing Node-RED natively on a Linux edge computer is the host setup to start from.
Pilot throughput before you schedule anything #
Run two or three representative windows under the manifest's retrieval mode and record three numbers each time: elapsed seconds on a stopwatch, rows returned, and output bytes. Watch the historian during the same interval — CPU, storage latency, and live collection health. Change one dimension at a time: tag batch size, window length, or concurrent request count. Expect more concurrency to saturate the same storage path and sometimes reduce total throughput.
Compute two values per pilot: throughput as returned rows divided by elapsed seconds, and row density as rows divided by tags times days. Density, not tag count, is what predicts totals — a thousand fast tags outweigh ten thousand quiet ones. Sample several consecutive windows, including a high-event-density period, before extrapolating anything. Then the schedule is one division: total projected rows divided by sustained rows per second. Re-measure after every change to batch shape, because a change that raises rows per request can lower rows per second.
Working the numbers for 12,000 tags backfilled from 2020 #
Inputs:
- 12,000 tags — the midpoint of the 10–15k reported for this migration.
- Backfill span 2020-01-01 to 2025-01-01: 1,827 days, including the leap years 2020 and 2024. Substitute your actual handoff date.
- Batch shape under test: 1,000 tags per query, 5-day windows — the several-day slicing already in use here.
- Pilot measurement, illustrative stopwatch values to replace with your own: one window returns 2,000,000 rows in 4,000 seconds, about 67 minutes.
The rule in words: total rows = windows per tag batch × tag batches × rows per window, and duration = total rows ÷ sustained rows per second.
The arithmetic:
- Windows per tag batch: 1,827 ÷ 5 = 365.4, rounded up to 366.
- Tag batches: 12,000 ÷ 1,000 = 12.
- Total queries: 12 × 366 = 4,392.
- Total rows: 4,392 × 2,000,000 = 8,784,000,000.
- Throughput: 2,000,000 ÷ 4,000 = 500 rows/s.
- Duration: 8,784,000,000 ÷ 500 = 17,568,000 s; 17,568,000 ÷ 86,400 s/day = 203.3 days.
Sanity check on density: 2,000,000 ÷ (1,000 tags × 5 days) = 400 events per tag per day, one event every 3.6 minutes on average — plausible for mixed analog tags. If your pilot measures 100 events per tag-day, every total here drops by four.
Decision: at 500 rows/s this backfill runs about 203 days, six to seven months — the months-long estimate, now proven with arithmetic. To land it in 60 days you need 8,784,000,000 ÷ (60 × 86,400 s) = 1,694 rows/s, about 3.4 times the pilot. The threshold to apply: required rate = total rows ÷ seconds in your deadline. If tuned batch shape cannot reach it, cut scope instead of hoping — per-tag start dates, fewer tags, or coarser retrieval only where the manifest allows it.
Check the Historian 2023 replication route before writing code #
Historian 2023 adds native Historian-to-PI replication with a Backfill Utility. If the source historian can be upgraded, this replaces the entire custom pipeline with a supported path, and it is the first alternative to weigh. Confirm against the vendor documentation before scheduling: supported source and destination versions, licensing, how far back the utility reads, how it carries quality, and whether it applies destination compression on the backfill path. An upgrade project that takes two weeks beats a 203-day pipeline; it loses to a pipeline your pilot measures at two weeks. Run the same awkward-tag verification from the manifest section against whatever it loads.
The decision, case by case #
- Source can move to Historian 2023 and licensing permits: use the native replication and the Backfill Utility, and keep a short pilot to verify quality and compression behavior on real tags before the full run.
- Source stays at 2017: build the four-stage pipeline. Manifest first, pilot second, and hold the line that duration equals measured rows divided by sustained rows per second. If the required rate for your deadline exceeds what tuning reaches, cut scope.
- Someone offers an offline archive rip for the 2017 box: accept only a vendor-documented procedure, proven event for event on a restored copy. Without that proof, treat it as unavailable.
- Whatever the path: the raw extract lives in the intermediate store, so compression and load can be re-run at will. The expensive full extraction happens exactly once, and the historian is left alone after it.
Questions asked
How do I stop time zones from shifting the backfilled history?
Export and import under one clock: write UTC timestamps in the intermediate files and confirm the destination import reads UTC too. Then verify a known real event that falls inside a daylight-saving change. If its reloaded timestamp lands one hour off, fix the interpretation before the bulk run — an hour of shift across five years of history is a painful thing to unwind.
How much disk does the intermediate event store need?
Measure it from the pilot instead of guessing: divide the pilot's output file size by its returned rows to get bytes per row, multiply by your projected total rows, and add headroom for the separate processed copy. Re-run the arithmetic whenever batch shape changes, because rows per tag-day differ by plant area and a denser area changes the bytes-per-row you measured.
Which window owns the event sitting on a boundary between two partitions?
Declare it in the manifest before any extraction: one partition owns the boundary instant and the next begins after it. After the run, take a known real event that falls on a seam and confirm it appears exactly once in the destination. Two partitions that both include the boundary duplicate the event; two that both exclude it lose it silently.
Does every tag need the same backfill start date?
No, and trimming starts per tag is often the cheapest throughput win available. A tag commissioned in 2022 has nothing before 2022 to export. A slowly changing tag may serve its destination purpose as daily summaries rather than raw events, if the consumers accept it. Set the start and the retrieval mode per tag group in the manifest and let the row totals show it.