You have several options to backfill historical data into a PI Server.
This article discusses how to use piconfig to backfill data for new points in an existing PI Server that is collecting data. To backfill data into a new PI Server, see Chapter 1 in the PI Server System Management Guide
Before you start, review this KB thoroughly. Also note these points:
- If you intend to backfill many points and several days of data, contact Technical Support for recommendations.
- OSIsoft strongly recommends PI Server 2012 (3.4.390) or later, which includes the following features that make backfilling faster and easier:
- Archive reprocessing is no longer necessary.
- "Pt Created" events for newly created points no longer need to be explicitly deleted from snapshot to backfill with compression.
- Writing data to PI Server is significantly faster.
- OSIsoft recommends backfilling with compression to avoid archiving unnecessary data and to ensure that the backfilling is done as efficiently as possible. To compress data during backfilling, you must first clear the snapshot for the point and then send the data in chronological order. (To clear the snapshot, you must have PI Server version 3.3 or later.)
Backfilling with piconfig includes these procedures:
- Prepare a currently running PI Server for backfilling with compression. Keep in mind that you cannot backfill with compression into points that already contain data.
- Prepare data in text files for backfilling.
- Use piconfig to backfill data.
Prepare the PI Server for backfilling
- Evaluate the data to be backfilled. Determine the number and configuration of new points, the time period covered by all points, and the approximate amount of data you need to import.
- Create the points to backfill. If the points correspond to active interfaces, make sure current data is not being sent to any of the points from the interface. One way to do this is create the points with the Scan attribute set to "0" (off), or set the Point Source attribute for the points to "L" for Lab Point. You can change these settings later. It is possible to import data into existing points that already contain values, but you will not be able to compress the data.
- Check existing archive files. Use the Archives plug-in in PI System Management Tools (PI SMT), or the piartool -al command. Note the start time, end time, and filename (including the path) of all archives within the time range of the backfill data.
- Make a backup of your PI Server including all archives you plan to reprocess (if necessary) and backfill. See Chapter 1 of the PI Server System Management Guide for additional details on backups.
Reprocess old archives to create primary records for the new points if backfilling on versions PRIOR to PI Server 2012
Note: For PI Server 2012 or later, reprocessing to add the primary record is unnecessary. It is also unnecessary to reprocess fixed-size archives to dynamic for PI Server 2012, as well as for most installations of previous versions beginning with PI Server 3.4.375. For more details, refer to
Reprocessing of FIxed Archives to Dynamic Archives below.
Reprocess any existing non-primary archives with dates within the range of the backfill data. This creates primary records for the new points in those archives.
- Run PI SMT and open the Operation > Archives tree.
- Unregister the archive. Right-click the archive file and select Unregister Archive.
- Open a command prompt window and change directories to \pi\bin.
- Issue the following command. It creates a new dynamic archive file as it reprocesses. It preserves the same start time and end time of the original archive.
piarchss -if [full path and name of archive] -of [full path and name for reprocessed archive] -dup
- Register the reprocessed archive using the Register an Archive icon.
- Repeat steps 2-5 for additional archives that need to accommodate new data.
Create additional archives as needed
If the data to be backfilled include values prior to the oldest archive, create a new dynamic archive with a Start Time at or earlier than the oldest time stamp, and an End Time equal to the start time of the oldest current archive.
: The Archives plug-in included with PI SMT 2012 made it possible to create multiple archives automatically over a specified time range for backfilling. If many archives must be created, use this feature.
To create additional archives:
- In PI SMT, open the Archives plug-in.
- Select the toolbar icon for Create a new archive (or right-click and select Create New).
- Select Create Archive with Fixed Start and End Time.
- For Start Time, enter a time equal to or earlier than the oldest timestamp for data to be backfilled.
- For End Time, enter the start time of the oldest current archive.
- Select Dynamic as the archive type.
- Click OK to create and register the archive.
Clear the snapshot value for the new points if backfilling on versions PRIOR to PI Server 2012
You can clear the snapshot value for new points by deleting the snapshot value for each point at the time the point was created.
Note: Users of PI Server 2012 (or later) do not have to clear the snapshot value for new points, assuming that the only event for each point is "Pt Created" and the tuning parameter Snapshot_DoNotReplacePTCreatedOnOOO has not been set to 1 (default is 0).
You can use the Archive Editor plug-in in PI SMT to clear the snapshot value one point at a time.
If you prefer to delete data in large batches, use piconfig. For example:
After clearing the snapshot, verify that the oldest value is now in the snapshot for new points.
Prepare data in text files for backfilling with piconfig
Use a spreadsheet program such as Microsoft Excel to create a comma-separated (*.csv) text file and add a piconfig script to it.
- Create a comma-separated text file containing the data. Enter one point value (event) per row; each includes the point name, timestamp, and value, such as this example entry:
- Values for multiple points can be included in a single file.
- Values must be in time order (oldest to newest) for each point to backfill with compression.
If you have lots of data, separate the data into smaller files so you can easily manage and track the backfilling (for example, one day at a time). Note that you should backfill in chronological order to ensure compression to be applied properly.
2. Add the following piconfig script to the beginning of the file, followed by the rest of your data.
@istr tag, time, value
3. Save the script and data file as a *.csv file, such as “data.csv.” If you are using Excel, choose File>Save As and under the Save As type option, choose CSV (comma-separated). Give the file a name and note its location.
Your data file is now ready for backfilling, when using local time zone timestamps. If you have timestamps in UTC seconds, you can use the "timenum" field instead of "time". Do not use both fields as unexpected results may arise.
Backfill with piconfig
Test your backfill script using a small sample of data first (see the Tips on Backfilling section below).
- Force an archive shift to avoid backfilling into a primary archive if you are backfilling on a production system. The easiest way to do this is with the PI SMT Archives plug-in.
- Open a command prompt window and go to the PI\adm directory.
- Run piconfig and redirect your previously prepared data file. Substitute the actual path and file name of your prepared text file for "c:\tags\data.csv" in the example below.
piconfig < c:\tags\data.csv
- To verify your data use the Archive Editor plug-in in PI SMT, or use a PI Client such as PI DataLink or PI ProcessBook.
Tips on Backfilling
Always test first!
Always run a backfill test with a small amount of data first, and then proceed with the rest of the data. This way you can verify your piconfig script and make sure that the data is importing properly.
Check the archive and snapshot statistics during the test to see how the backfilling affects the PI Server performance.
Backfilling large amounts of data
To backfill large amounts of data, you may want to consider writing a custom application to do the backfilling, rather than creating comma-separated text files and backfilling with piconfig.
Backfilling large amounts of data can stress a PI Server severely. Data in files is sent to the PI Server at a very high rate, much higher than the rate of the control system. To minimize the load on the PI system:
- Always contact Technical Support for recommendations before preparing a large backfill.
- Always backfill data in chronological order, from oldest to newest, within each point.
- When backfilling from data files, use several smaller files rather than a single larger file (try one day at a time). You can throttle the rate at which the PI Server processes the data using the "@wait 1" line within the data file at intervals. The "1" is one second. You can increase this if you like.
Backfilling on an off-line PI Server
Whenever possible, perform backfilling jobs on an off-line PI Server to avoid excessive burden on your main production server. Doing this also enables you to verify the backfill is successful without posing risk to your real data on the PI Server.
Reprocessing of fixed archives to dynamic archives
As of PI Server 2012 (3.4.390) and in most installations of previous versions beginning with PI Server 3.4.375, it is no longer necessary to reprocess fixed archives into dynamic archives to ensure that all backfilled data will be accommodated in the target archives. Fixed archives will automatically be converted to dynamic archives when they become full to incorporate backfilled data when necessary.
The auto-dynamic behavior was first introduced in PI Server 3.4.375.38 with the tuning parameter, Archive_EnableAutoDynamic (enabled by default). In PI Server 2012, the behavior was permanently enabled, and the tuning parameter was removed.
Prior to PI Server 2012, if the PI Server version is 3.4.370 or earlier or the Archive_EnableAutoDynamic tuning parameter was disabled, backfilling could result in the target archive filling before all data is backfilled. For these cases, it is generally considered a good idea to use dynamic archives for backfilling. If desired, you can easily reprocess them into fixed archives once the backfilling is complete.
If you do not want to use dynamic archives, it is possible to experiment and find out how much space is required per time period, then use that period when creating fixed archives. One way to find out this info is to have the backfilling program work hour by hour for all points and then use piartool -al to observe filling up rates. PI Server 3.4 exposes performance counters that allow you to monitor the available space in each archive over time, which could also help.
This can normally be skipped on PI Server 2012 (3.4.390) or later, as long as the Snapshot_DoNotReplacePTCreatedOnOOO tuning parameter has not been set to 1 (default is 0).
Clearing the "Pt Created" snapshot for new points
If it has been set to 1 (or you are using an earlier version of PI Server) follow the steps below to clear the "Pt Created" snapshot value.
You can use the Archive Editor plug-in in PI SMT to delete the "Pt Created" snapshot value one point at a time.
If you prefer to delete data in large batches, use piconfig. First export the points whose snapshot values are "Pt Created" and then delete those snapshot values from the snapshot table.
- Use the following piconfig script to export all points whose most current value is "Pt Created."
# Create a script file called 'gettags.txt' by using a text editor (like Notepad). The file should contain the following commands:
@select status = Pt Created, tag = *
@ostr tag, time
# Save and close the gettags.txt file to your C: drive.
# Open a command prompt (Start> Run, type cmd and press Enter)
# Change into the PI\adm directory.
# Run piconfig by redirecting the previously created script file. (Substitute the actual path and file name of your script file for "c:\gettags.txt" in the example below):
This will generate a list of points whose snapshot you want to delete.
# Open the newly created ‘snap_to_delete.txt’ file from the PI\adm directory to verify which points have "Pt Created" value in the snapshot, and remove any points from the list that you are not planning to backfill.
# Save this file.
- Run piconfig with the following commands, and use the 'snap_to_delete.txt' file as an input for which snapshot values to remove:
@mode edit, t
@istr tag, time
@modi mode = remove