AnsweredAssumed Answered

How to get previous value for AF attributes?

Question asked by wilsona on Apr 1, 2016
Latest reply on Aug 22, 2019 by Danny Dijke

Hi,

I need help to find a way to efficiently get the last value of a two AF attributes (PI tags) before a given point in time, i.e. I want to get the PrevVal for a set of AF Attributes

 

The constraints are

  • I am not able to use Event Frames
  • I am not able to use PI Web API
  • I am not allowed to use interpolated values i.e. I need the last actual value prior to the point in time

The reason I cannot use Event Frames or PI Web API is not technical, it is a business reason.

 

If it is possible the preferred technology would be PI OLEDB Enterprise.

 

Background:

All the assets in the AF hierarchy are templatised.

On each asset (approx 1500) I am interested in two particular attributes.

The attribute values can be fast changing and even with compression we can get about 1000 values per hour per attribute (this cannot be compressed further)  but at different times we may not see a value change for hours.

 

The question:

Given a point in time how do I get the last value for each of the attributes. Does PI OLEDB Enterprise have a PrevVal equivalent?

I have something working but it takes 90 seconds on our system and this is far too slow.

The following statement is the heart of the code and also the bottleneck. The actual bottleneck is the join on to the Data.Archive table. (Please note this is not all of the code, it is not the whole solution but this code takes 90 seconds and the rest takes 1 second)

 

select

     eh.Name as ElementName, ea.Name as Attribute, r.time, r.Value

from

     AFDatabase.Asset.ElementHierarchy eh

inner join

(

     select

         ea.ElementID, ea.ID, ea.Name, ea.Path

     from

         AFDatabase.Asset.elementAttribute ea

     where

         ea.Name = 'Attribute 01' or ea.Name  = 'Attribute 02') ea on eh.ElementID = ea.ElementID   

inner join

     AFDatabase.Data.Archive  r ON r.ElementAttributeID = ea.ID

where

     r.time > '*-2h' AND r.time <=  '*-1h'

option (force order, ignore errors)

 

Any ideas?

 

Please feel free to ignore the method I have been using and suggest something completely different.

 

Thanks in advance.

 

Arthur

 

 

Outcomes