Overview

We often come across scenarios in which we would like to extract large data sets into files (.txt, .csv etc) from the PI Data Archive but run into parameters which are put in place to protect the PI Server from trying to satisfy a huge query. The server can handle larger and larger queries with more resources but these safeguards prevent queries from straining the archive leading to performance issues. There are valid cases where these safeguards become limitations making it difficult in achieving our intended objectives.

 

There are three PI tuning parameters that can help (For a more detailed discussion refer to KB 3224OSI8)

(1) ArcMaxCollect to limit the quantity of data retrieved by a single client

(2) MaxThreadsPerClientQuery to restrict the number of repeated queries accepted from a single client

(3) Archive_MaxQueryExecutionSec to cancel long-running archive queries.

 

Working around the ArcMaxCollect contraint

The most common scenario of running into a limitation is with ArcMaxCollect which causes PI Clients to return "Failed to retrieve events from server. [-11091] Event collection exceeded the maximum allowed" after large or filtered queries. The problem is that you are asking the PI Data Archive for too large an amount of information at once.

 

In order to get around we need to break down the underlying query into smaller queries. To preserve data integrity we need to make sure no values are excluded and more values are not included from the boundaries while collating the chunks. Rick Davin has addressed this issue in his blog post GetLargeRecordedValues - working around ArcMaxCollect and the users interested in the code implementation are highly encouraged to check it out. This is incorporated into the PIEventsNovo utility's downloadcsv option to get the values from PI Points into csv files.

 

Usage of the Utility

pieventsnovo.exe -downloadcsv <tagmasks> <starttime,endtime>[,pageSize] [-server Archive]

 

Example:

 

The pageSize here refers to the "chunking" of the data retrieval from the server. The Defaults, Min and Max can be set in App.config. When it is not specified in the command line the Default is used.

The output folder is also specified in the App.config. If it is left as an empty string the the applications current directory is used.

 

For technical documentation, other available features & to download use the following PI DataPipe Events Subscription and Data Access Utility using AF SDK - PIEventsNovo

 

A note of caution

Retrieving larger number of values may potentially result in a higher load/resource usage on the server. Make sure adequate resources are available in order to protect the server and the network (and indirectly, client applications) from enormous amounts of data being retrieved.

Opening .csv files using MS Excel may run into specifications and limits set by Microsoft. For 2016 version this is 1,048,576 rows. Alternatively use editors like NotePad, Emacs, gedit, Notepad++ etc.