Backfilling with PIconfig

PI provides many ways to import historical data, such as using piconfig scripts or your own PI API and PI SDK applications. In addition, OSIsoft offers several file-based interfaces (Batch File, RDBMS, Universal File, and PI File) which are far more efficient for backfilling data than piconfig. If you are planning on backfilling many tags and more than few days worth of data, we encourage you to contact technical support for recommendations before you begin preparations.

• Preparing a currently running PI Server for backfilling with compression*
• Preparing data in text files for backfilling
• Backfilling with piconfig
• Tips on backfilling

These instructions assume you already are collecting data in your PI Server and need to backfill data for new tags. Please see Chapter 1 in the PI Server System Management Guide for additional information and procedures on backfilling data into a new PI system, with and without compression.

*You cannot backfill with compression into tags that already contain data.

Preparing the PI System for backfilling with compression

We recommend 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 tag, and then send the data in chronological order. In order to clear the snapshot, you must have PI 3.3 or later. Follow these steps to prepare the PI system for backfilling:

STEP 1: Evaluate the data that is to be backfilled. Determine the number and configuration of new tags, the time period covered by all tags, and the approximate amount of data you need to import.

STEP 2: Create the tags to backfill. If the tags correspond to active interfaces, make sure current data is not being sent to the tag from the interface. One way to do this is create the tags with the "Scan" attribute set to "0" (zero, which is off), or set the "Point Source" attribute for the tags to "L" for Lab tag. You can change these later. (You can import data into existing tags that already contain values, but you will not be able to compress the data.)

STEP 3: Check existing archive files. Use the PI Archive Manager plug-in in System Management Tools (SMT 3), 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.

STEP 4: Make a backup of your PI Server including all archives you plan to reprocess. See Chapter 1 of the PI Server System Management Guide for additional details on backups.

STEP 5: Reprocess old archives to create primary records for the new tags. You need to reprocess any existing non-primary archives with dates within the range of the backfill data. This creates primary records for the new tags in those archives. In addition, you should reprocess them as dynamic archives (using the "-d" switch) to allow the archives to accommodate new data. To do this:

1. Run SMT 3 and open the Operation > PI Archive Manager tree.
2. Unregister the archive by right-clicking the archive file and choosing Unregister Archive. 
3. Open a Command Prompt window and change directories to \pi\bin. 
4. Issue the command:

piarchss -if [full path and name of archive] -of [full path and name for reprocessed archive] -dup

This command creates a new dynamic archive file as it reprocesses. It preserves the same start time and end time of the original archive.

5. Register the reprocessed archive using the Register an Archive icon.
6. Repeat steps 2-5 for all archives that need to accommodate new data.

1. In SMT 3, open PI Archive Manager.
2. Right-click any existing archive and choose Create New.
3. Select the option "Create Archive with Fixed Start and End Time."
4. Enter a Start Time equal to or earlier than the oldest time stamp in the backfill data. 
5. Enter the End Time equal to the start time of the current oldest archive.
6. Select "Dynamic" as the archive Type.
7. Click OK to create and register the archive.

STEP 7.  Clear the snapshot value for the new tags by deleting the snapshot value for each tag at the time the point was created. You can use piconfig (see below) or the Archive Editor plug-in (in PI SMT) to delete the snapshot.

@table piarc
@mode edit,t
@istr tag,time,mode
mytag1,14-Jul-04 15:27:23.000,remove
@ends

If you have many new tags whose snapshots you need to clear for backfilling, then see the Tips on Backfilling section (below) for scripts to facilitate this.

For PI 3.3 SR1 Users Only: Before you clear the snapshot, you must first add the oldest value to be backfilled to the archive for each tag. This can be done with a piconfig script (see below) or the Archive Editor plug-in in PI SMT.

@mode edit,t
@table pisnap
@istr tag,time,value
mytag1,01-Jun-00 11:00:00,99.2
...
@ends

STEP 8. Verify that the oldest value is now in the snapshot for new tags. At this point your PI System is ready to accept the backfill data.  Now you need to prepare the data itself and the piconfig script for input.  If you have already prepared your data files, go to "Backfilling with piconfig" below.

Preparing data in text files for backfilling

This section describes how to set up your data in a comma-separated (*.csv) text file and add a piconfig script to the file so you can easily backfill the data into the PI Data Archive. A spreadsheet program such as Microsoft Excel makes it easy to set up comma-separated text files for import. To setup the file, follow these steps.

1. Create a comma-separated text file containing the data. Format your text file as follows:

• One tag value per line.
• Each line must include the tag name, timestamp, and value:
  
  A1HV074B,08-Aug-01 11:00:00,3659
  
• Values for multiple tags can be included in a single file.
• Values must be in time order (oldest to newest) for each tag 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 (i.e., one day at a time). 

2. Add the following piconfig script to the beginning of the file:

@mode edit,t
@table pisnap
@istr tag, time, value
A1HV074B,08-Aug-01 11:00:00,3659
... [followed by the rest of your data]

piconfig < c:\tags\data.csv

Always test first!

Always run a backfill test with a small amount of data first, and then do 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

If you have large amounts of data, you may want to consider writing an API or SDK 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 PI 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 tag.
• When backfilling from data files, use several smaller files rather than a single larger file (try 1 day at a time). You can throttle the rate at which PI 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

We highly recommend, whenever possible, to do backfilling jobs on an off-line PI Server to avoid excessive burden on your main production server. This also offers an opportunity to verify the backfill is successful without posing risk to your real data on the PI Server.

Tags – Reusing and creating extras

If you have old tags that you no longer use, you can recycle these tags for backfilling. The advantage is, these tags may already have primary records in older archives. Be careful when doing this and make backups beforehand. Also note that you may still need to reprocess old archives in order to make them dynamic. 

Likewise, when creating new tags for backfilling, consider creating a few extra tags so that if your project happens to require a few more tags than expected, you don’t have to go through the trouble to reprocess all your old archive once again.

Clearing the "Pt Created" snapshot for new tags

The Archive Editor in PI SMT makes it easy to delete the "Pt Created" snapshot value one tag at a time.  

If you prefer to delete data in large batches, use piconfig. The first step is to export the tags whose snapshot values are "Pt Created" and the second is to delete those snapshot values from the snapshot table.

STEP 1: Use the following piconfig script to export all tags whose most current value is "Pt Created."

1. Create a script file called 'gettags.txt' by using a text editor (like Notepad). The file should contain the following commands:
   
           @table pisnap
           @select status = Pt Created, tag = *
           @ostr tag, time
           @output snap_to_delete.txt
           @ends
   
   
2. Save and close the gettags.txt file to your C: drive.
3. Open a command prompt (Start> Run, type "cmd" and press Enter)
4. Change into the pi\adm directory.
5. 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):

                Piconfig<c:\gettags.txt
   
   This will generate a list of tags whose snapshot you want to delete.
   
   

6. Open the newly created ‘snap_to_delete.txt’ file from the pi\adm directory to verify which tags have Pt Created value in the snapshot, and remove any tags from the list that you are not planning to backfill.
7. Save this file.

@table piarc
@mode edit, t
@istr tag, time
@modi mode = remove
@input snap_to_delete.txt
@ends