Access to the PI System - How to retrieve PI counts?

[Resa]

In a project we are working on, up until recently we have been retrieving information from the PI System by using an OLEDB connection. This was all done using PROC SQL, in the form of:

PROC SQL;

    CONNECT TO OLEDB <PI Connect information>;

    SELECT * FROM (

        SELECT <info> FROM <pi table>
    )

    DISCONNECT FROM OLEDB;

QUIT;

The information we retrieve are tag metadata, tag measures and counts.

The latter provides information on how many measures for a tag are available in a certain timeframe.

The tag metadata is retrieved from the PI table: pipoint, the tag measures are retrieved from the PI table: picomp and the counts are retrieved from the PI table: picount.

Recently we have the product "Access to the PI System" also available for retrieving information from the PI system.

However, the documentation only describes how to retrieve tag metadata (using pipoint) and tag measures (using picomp).

I have been unable to find information on whether also counts can be retrieved.

We need these counts to determine in advance per tag how many measures are available in a certain timeframe so we can check if we have retrieved all available measures.

My question:

Is it in any way possible by retrieving the counts by using the libname as we now do for getting metadata and measures?

Otherwise we are faced with the fact that we have to use the access through OLEDB to get the counts and can only use the libname for measures and metadata which seems a bit unlogical.

[LinusH]

I have no experience with PI, just trying to interpret the doc.

Under ACCESS for PI, I'm reading this under "Read Tag Data":

• COUNT= specifies to interpolate the data and evenly distribute the data between the start and end times using a specific number of observations.

  Is this perhaps what you are looking for?

[Resa]

Thank you for your reply, but this is not what I am looking for.

Basically the PI System collects signals (measures) from sensors (tags) which can be retrieved by using the Access product. When fetching measures you can do this by indicating a period by using the START= en END= option which will give all measures withing this time period. On other way, at least that is what I understand from the documentation, is not to indicate a period from which to retrieve measures but to indicate that you would like to fetch X measures for a time period. That is when you use the COUNT= option.

The counts that I am looking for is basically a summary providing for an indicated time period how many measures are available for a tag within the PI system. Which can help in validating whether all measures are available/retrieved from the PI System

[LinusH]

Hi, I'm probably totally out bounds here, bear with me - just trying to take the opportunity to learn something.

Are those counts available within PI, and you need a way to access them from SAS?

Or, why can't you extract your time period and then do the counting in SAS?

[Resa]

Are those counts available within PI, and you need a way to access them from SAS?

Yes, in the current situation (when accessing through OLEDB) we retrieve counts as follows:

PROC SQL;

    CONNECT TO OLEDB (<PI Connection Parameters);

    SELECT * FROM CONNECTION TO OLEDB

       (SELECT <PI Columns> FROM piarchive.picount WHERE <Filter parameters>);

    DISCONNECT FROM OLEDB;

QUIT;

What I am looking for is a data step (similar to how measures are retrieved from the picomp dataset) using the PI libname engine to retrieve the PI counts

Or, why can't you extract your time period and then do the counting in SAS?

In our current ETL we first get the counts so we know in advance how many measures are coming in.

Next we retrieve the measures and use the counts to check if we indeed have received all measures.

When I would follow your suggestion the counts would always match the number retrieved. 

[Resa]

UPDATE: I have also raised this question with Technical Support.

It turns out that getting the PI counts is currently not possible using the Access to the PI System.

It has now been entered as a request for a new feature.

[Resa]

As of release SAS 9.4 M4 it will be possible to retrieve, amongst others, the PI counts with the availability of Special Virtual Tables for the Data Archive.

From the documentation:

Summary Statistics That Are Available in Picomp_Summary

Column	Data Type	Description
Tag	String	Tag name
StartTime	Timestamp	Start time of a summary interval
EndTime	Timestamp	End time of a summary interval
Count	Numeric	Sample count for event-weighted statistics 1/(number of seconds) for time-weighted statistics
Total	Numeric	Total of sample values in an interval for event-weighted statistics Average of sample values in an interval for time-weighted statistics
Minimum	Numeric	Smallest sample value in an interval
MinimumTime	Timestamp	Timestamp of the smallest sample value
Maximum	Numeric	Largest sample value in an interval
MaximumTime	Timestamp	Timestamp of the largest sample value
Range	Numeric	Difference of the Maximum and Minimum values for an interval
Average	Numeric	Mean of the sample values over an interval
Median1	Numeric	Median of sample values over an interval
MedianCount1	Numeric	Number of sample values used to determine the Median
StdDev	Numeric	Standard deviation of sample values over an interval
PopulationStdDev	Numeric	Population standard deviation of sample values over an interval
PercentGood	Numeric	Percentage of sample values with a status of “OK”
Status	String	Status of the summary

1The median statistics are optional, but if requested (via the MEDIAN= data set option), they are calculated by reading all relevant data into SAS. This can significantly increase processing time.