BookmarkSubscribeRSS Feed
Resa
Pyrite | Level 9

In addition to my earlier question "Access to the PI System - How to retrieve PI counts", I have a new question regarding the Access to the PI System.

 

When we retrieve data from picomp (which produces a seven-column data set that contains the time series data that is stored in each tag) using the Access to the PI System we get the following columns:

  • Tag: is the name of the PI Point.
  • Timestamp: is the date and the time, including milliseconds, when the tag value was first recorded.
  • Value: is the value of the sample, usually numeric. It could also be a character string or a timestamp.
  • Status: is the status of the sample.
  • Questionable: is a flag that indicates whether the event has been marked as questionable.
  • Annotated: is a flag that indicates whether the event has an annotation.
  • Substituted: is a flag that indicates whether the value has been modified after its original recording.

However, before we had the Access engine and we retrieved the data using OLEDB there was an additional column named "index".

This column indicates a sequence number when for the same tag, timestamp combination multiple values are recorded. 

 

Since this column is not available within the dataset retrieved using the Access to the PI system we are now faced with duplicates records and no way to distinguish which of the records is the one with the highest sequence number.

 

AFAIK there is no option available to also retrieve the "index" column within the results dataset but maybe I am missing something.
Anyone any suggestion?

 

 

 

 

6 REPLIES 6
Resa
Pyrite | Level 9

Yes, the OLEDB access also does still work however there are several advantages in using the Access to PI product when retrieving measures, like the time in which the data is retrieved from PI and the way that you can indicate for which tags you would like to retrieve data.

 

On the other hand if the questions I have now posted on Access to PI can not be resolved we just may have to fall back on the OLEDB method and consider the Access to PI product as obsolete.

LinusH
Tourmaline | Level 20

Since there are no mentioning of index column anywhere in the SAS doc, I think you need to direct this question towards a SAS representative, like a product manager or similar.

If you learn anything about the issue, please keep us posted.

Data never sleeps
Resa
Pyrite | Level 9

I was hoping that there was more documentation than I was aware of or that there was an "undocumented feature" that others may have found and/or know about.

 

We will redirect our questions also to a SAS representative and I will update my posts here if more information comes available

Resa
Pyrite | Level 9

UPDATE: This issue, particularly the duplicate entries, has been raised with Technical Support.

For me it is not completely sure whether it is currently seen as a defect or a request for a new feature.

Basic outcome is that the index column is currently not available and therefor duplicates may occur when records are retrieved using the Access to the PI System

Resa
Pyrite | Level 9

As of SAS Release 9.4 M4 it will be possible to include the index column in the output by means of the SHOWINDEX data set option.

This is one of the new data set options that has become available. 

 

From the documentation:

SHOWINDEX=<YES> | <NO>

specifies whether to include column _index in the Picomp table that indicates the index corresponding to a timestamp. The index value is 1 when there is only one observation for a given timestamp. The index value increments by one for each additional observation that is recorded with the same timestamp. The _index column enables you to create a unique key for each observation.

Two observations are considered to occur at the same time when their timestamps differ by less than 0.001 seconds (1 ms).
Default NO

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1820 views
  • 6 likes
  • 3 in conversation