3 Replies Latest reply on Mar 4, 2011 6:33 PM by skwan

    AF Table Lookup Summary Attributes in ProcessBook / WebParts not passing time context correctly


      It appears that there is a pretty serious flaw with the way ProcesBook / WebParts work with AF and especially with summaries on table data references.


      Everything is hunky dory with averages, totals etc on PI Point references, but summaries on Table references do not work because only the End Time of the context of the tag in ProcessBook or End Time of a WebPart is passed to the GetValue method.


      I have a relational database with data logged each day. I want to be able to do a summary of all values within a time range and I want the timerange to be used to be dynamic based upon the AFTimeContext of the calling application. Typically the range will be Month to date, Year to Date, Start of Production To Date.


      The options I see are:

      • Copy the data from the source system into PI using RDBMS - would require thousands of tags!
      • Hard coding the ranges, I could have an attribute that is based on a PI PE Tag which keeps track of the current Year Start, Current Month Start etc and use that as the reference.
      • Write an AF Add-In to ProcessBook which, given an element context and a time range, would call AF GetValue(AFTimeRange) - but that requires additional software to be installed on peoples PC's.
      • Create additional linked tables, which do the aggregations directly.
      • In WebParts, I can use DataSets to do the SUM query directly. This is not so straightforward for ProcessBook because I don't want the overhead of installing OLEDB Enterprise on everybodies PC.

      It would be better if this functionality was supported in the client ptoducts, but it does not appear to be listed in the tech support knowledge base so I am not sure if and when it will be fixed.


      I would appreciate any advise on how to get round this issue.





        • Re: AF Table Lookup Summary Attributes in ProcessBook / WebParts not passing time context correctly



          In some way, what you're asking is similar to this KB article.




          In a nutshell, the current implementation of the Table Data Reference in AF was never meant for the users to trend these table values.  As such, AF currently has no concept of time in the Table Data Reference, meaning even though it supports data/time format in a column in a table, it really doesn't know that 00:12 is 12 minutes past midnight.  Since there is no concept of time, you really can't do summaries.  This is related to what you're seeing in that only the last value is pass to AF from ProcessBook because, once again, the Table Data Reference was meant to give a value at a snap shot in time, and not trends.


          Let me know if this makes sense to you as it is difficult to convey this information without some hand waving and a white board :-).


          Also, you are not the first to want to do this, we already have a feature enhancement request in our queue.  So that is the good news, the bad news is that this is most likely not going to be in scope for AF 2011 release.

            • Re: AF Table Lookup Summary Attributes in ProcessBook / WebParts not passing time context correctly



              I already used the knowledge based article describing how to trend AF Table data, using another tag (PI Point) as a reference point for the lookup into the underlying table, e.g.


              SELECT GasTotal FROM ProductionData WHERE WellID = '%Element%' AND ReportDate <= #%Time%# AND GasTotal <> @ComparisonTag ORDER BY ReportDate DESC 


              This will give you the value on or before the timerange associated with an object within ProcessBook if using a PI Value object. The timestamp it shows is the timestamp associated with the found row.


              I can also use this attribute in a Trend object and I get all rows returned. The unfortunate thing is that you have to prepare your PIPoint reference tag to have a timestamp at the point you expect it to have in the source RDB table, and a value which the RDB field is not expected to have., So this would not work where the data is stored at ad-hoc times.


              I realise that AF is looking at a value at a given time. But I do not think that it is unreasonable for the AF reference to SUM all rows between two given times, otherwise why would there be the summary function for an AF Table reference if nothign can use it?


              I have kind of got something that works by referencing other AF Attributes which are of DR PI Point. For "Month To Date" attribute I first of all created 2 PI Points, one for "Beginning of Month" and one for "End of Month". These were PE tags which are


              BOM(‘*’) for the begin month, and BONM(‘*’) – (60*60*24), for end of month.


              My AF Lookup Query is then


              SELECT Sum(GasTotal) FROM ProductionData WHERE ReportDate >= @[Beginning of Month] AND ReportDate <= #%EndTime%# AND WellID = '%Element%'

              This looks to give me back what I want, i.e sum of all values for the beginning of the selected month up until the end time that the user selects. PI Value objects allow the EndTime value to be set on individual objects on a ProcessBook display. The unfortunate thing is that you now need VBA code to be able to set each object up with the correct EndTime for it to work, which is not nice!



              But I still believe that ProcessBook and WebParts should be passing through the start/end  time through to the underlying AF attribute to fix this bug.



                • Re: AF Table Lookup Summary Attributes in ProcessBook / WebParts not passing time context correctly

                  Hi Simon:


                  Thank you for your follow up, now I have more context on what you have already try/done; it wasn't so apparent from your original post and that was why I provided the link to the KB article .  Subsequent to your reply, I had some conversation with others, notably tech support and I was alerted that there is a PLI associated with ProcessBook that is related to the problem that you're seeing so I would suggest we work to solve that via tech support as it's a bug and not so much a development issue.


                  Getting back to your most recent reply, I acknowledge that there are some features and functions that many people are now asking for in the AF Table data reference that are beyond the original intention of this DR, one of which is the ability to understand time context.  So rest assured that we're listening so keep the suggestions and requests coming.