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

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

Reply
Frequent Contributor
Posts: 84

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

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?

 

 

 

 

Super User
Posts: 6,936

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 84

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

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.

Super User
Posts: 5,256

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

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
Frequent Contributor
Posts: 84

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

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

Frequent Contributor
Posts: 84

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

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

Frequent Contributor
Posts: 84

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

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
Ask a Question
Discussion stats
  • 6 replies
  • 516 views
  • 6 likes
  • 3 in conversation