topic Re: Access to the PI System - How to include the "index" column? in SAS Data Management

Access to the PI System - How to include the "index" column?

Resa — Wed, 27 Jan 2016 12:37:23 GMT

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?

Re: Access to the PI System - How to include the "index" column?

Kurt_Bremser — Wed, 27 Jan 2016 12:59:42 GMT

Does the OLEDB access method that you used previously still work?

Re: Access to the PI System - How to include the "index" column?

LinusH — Wed, 27 Jan 2016 13:17:28 GMT

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.

Re: Access to the PI System - How to include the "index" column?

Resa — Wed, 27 Jan 2016 14:50:09 GMT

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.

Re: Access to the PI System - How to include the "index" column?

Resa — Wed, 27 Jan 2016 14:51:52 GMT

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

Re: Access to the PI System - How to include the "index" column?

Resa — Tue, 29 Mar 2016 08:54:02 GMT

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

Re: Access to the PI System - How to include the "index" column?

Resa — Thu, 24 Nov 2016 09:17:59 GMT

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