SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Frequent Contributor
Posts: 84

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

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.

Super User
Posts: 5,260

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

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?
Data never sleeps
Frequent Contributor
Posts: 84

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

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

Super User
Posts: 5,260

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

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?

Data never sleeps
Frequent Contributor
Posts: 84

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

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. 

Frequent Contributor
Posts: 84

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

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.

Frequent Contributor
Posts: 84

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

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.

Ask a Question
Discussion stats
  • 6 replies
  • 506 views
  • 3 likes
  • 2 in conversation