Skip to content
  • There are no suggestions because the search field is empty.

Historians: Backfill AVEVA PI Data

This article provides a starter solution related to obtaining historical value changes from AVEVA PI System historian.

What Does This Article Cover?

Some advanced analytics solutions require access to large amounts of historical time-series data stored in a process historian. For example, it may be necessary to retrieve several days’ worth of historical data for analysis. In these scenarios, it is not possible to obtain all the data in a single execution of an Intelligence Hub Connection Input. Instead, the data pipeline must be designed to collect the historical data incrementally.

This article outlines an example solution that retrieves historical data from an AVEVA PI Data Archive and publishes arrays of modeled data to a MQTT Broker.  (A MQTT broker is being used to provide a simple and self-contained solution.  Typically, the modeled payloads would be written directly to a data lake, data warehouse, or object store.)

Solution Assumptions 

The following summarizes the assumptions related to the example solution.

  • The solution scope included thousands of PI Points.
  • The frequency of value changes varied per PI Point, with thousands of value changes per minute for the in-scope PI Points.
  • The value change data was obtained from PI Data Archive.
  • AVEVA PI System was installed on an Amazon EC2 instance.  
  • Intelligence Hub was installed on a second Amazon EC2 instance with 16 GB of RAM. 
  • The Intelligence Hub Pipeline published arrays of modelled data to a MQTT broker.  

 

Solution Summary 

The queries used to obtain a large amount of historical value changes from AVEVA PI System can be time consuming and resource intensive.  To address this issue Intelligence Hub provides the option to obtain the data from AVEVA PI System in parquet format.  This solution consists of saving the data obtained in parquet format locally in a file.  A DuckDB Connection in Intelligence Hub is used to interact with the file.  Therefore, DuckDB is a prerequisite for this solution.  

A DuckDB jar file (duckdb_jdbc-1.#.#.#.jar) should be obtained and saved in the Intelligence Hub runtime\lib directory.  Intelligence Hub must be restarted after saving the .jar file to the directory.  

The following summarizes the design of the example solution.  

  • Obtain PI Point Names
    The first step in creating the solution is to obtain the in-scope PI Point names.  The PI Point names can be obtained from AVEVA PI System Asset Framework, a file, a database, or query of AVEVA PI System Data Archive.  In this case the PI Point names were obtained from Data Archive using the Intelligence Hub PI Connection Point Browse Type Input.  The query should return the list of PI Point names quickly, for example in a few seconds or less.  Cache can be enabled on the Connection Input.  The format of the PI Point names should be a JSON array where each element is a string value.
  • Configure the Connection Input 
    Next the Connection Input to obtain the values can be configured.  The Intelligence Hub PI Connection Point type input may be used.  The Connection Input that obtains the PI Point Names can be used for the Reference.  Example start and end date times should be defined as parameters for testing.  Optimally the Connection should return data for the time span in a few seconds or less.  Start with a small number of PI Point names and a short time span and increase to optimize.  Parquet should be selected for the Format field in Intelligence Hub.
  • Build the Intelligence Hub Pipeline
    The Intelligence Hub Pipeline is used to define and manage the start and end time for the total duration of the backfill time span.  The index interval is also defined in the Pipeline.  The Pipeline manages these values as state and metadata. For a given index time period the Pipeline obtains data from PI Data Archive in parquet format.  The data is saved locally in a file.  A while loop is used to incrementally index through the records in the file based on a defined batch size.  DuckDB is used to query and model the records for each batch.  The Pipeline publishes the array of modeled data to a MQTT broker.  Typically, the Pipeline would write the array of modeled data to a file or directly to a table.  The number of records should consider downstream processing and required latency.   
  • Optimize for Performance
    When optimizing the Pipeline consider the volume of data being processed.  It might not be possible to use the Debug or Replay capabilities due to the large volume of data being processed. 
  • Isolate Backfill Workloads
    Consider the other Pipelines in the Intelligence Hub instance and the use of the PI Connection.  It might be necessary to dedicate a PI Connection or Intelligence Hub deployment for the purpose of processing the backfill.
  • A project file may be downloaded [here].

 

Results and Recommendations 

The following summarizes the results of running the example solution.  The example solution processed value changes for thousands of PI Points for a 24-hour period.  

  • The Connection Input returned data for an interval of one hour in approximately 2.5 seconds.  
  • On average the Pipeline executed in about 7 seconds.
  • The Pipeline processed all value changes in less than 3 minutes.
  • The Pipeline processed over 6 million value changes.
  • The Backfill Pipeline was the only Pipeline running during the test.
  • Performance of the solution is correlated to RAM allocated to the Intelligence Hub runtime.  

 

Additional Resources