16 Replies Latest reply on Oct 10, 2013 5:29 PM by j.rao

    Calculating yesterday's averages for a PI Tag using PI AF?

    j.rao

      In PI AF, I love the capability to calculate min/max, averages for current day. We can set the right 'Time Range' in PI Point Data Reference to calculate current day (12:00 AM through NOW()). 

       

      I am wondering if there is a possibility to do the same for yesterday's data - say 1200AM yesterday through 1200AM today? Time Range is always 'relative time', so we cannot specify a time interval. 

       

      3872.OSI_5F00_TimeRange.PNG

        • Re: Calculating yesterday's averages for a PI Tag using PI AF?
          j.rao

          I am trying to avoid creating new tags for yesterday's averages and instead leverage the power of PI AF aliasing to do the same.

            • Re: Calculating yesterday's averages for a PI Tag using PI AF?

              If you use a Relative Time of *-24h and in your query ensure that you pass "t"  you will get yesterday's avg.  

                • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                  j.rao

                  Where do I pass 't'? May I know which query you are referring to? I am doing it all in PI System Explorer.

                    • Re: Calculating yesterday's averages for a PI Tag using PI AF?

                      In the AFOLEDB query, and also use a timestep of "24h"  -  you can get daily averages for the past 30 days etc...using the timerange query - see examples in PI OLEDB Commander.

                        • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                          hanyong

                          Hi Jagdish,

                           

                          When you get value from an attribute, there is a time context involved. If you look at the PI System Explorer interface, you should find a Query Date button at the top which allows you to specify the time that you are querying for data. What Gopal mean is if the query time that you specify is equivalent to 't' or today at 00:00:00, and your time range settings is relative time of *-24h, then the data returned will basically be the average of the day before.

                           

                          In other ways to access the data like via the PI OLEDB Enterprise, you can also specify the time parameter to ensure that the aggregated value of a day's period is returned.

                           

                          I think the main point is, just with the configuration of the PI Point Data Reference it will not allow you to specify that the aggregated value is only for the day before easily. You can consider the information that Gopal mentioned to get the value that you want.

                            • Re: Calculating yesterday's averages for a PI Tag using PI AF?

                              Han Yong

                              I think the main point is, just with the configuration of the PI Point Data Reference it will not allow you to specify that the aggregated value is only for the day before easily. You can consider the information that Gopal mentioned to get the value that you want.

                               

                              You can't do it in PSE alone. Yesterday is relative to the current time, the query date or the time context. With changing each time option yesterday becomes 1 day further in the past. The PI Point DR will use the relative time as the start but the end is always the time context of the consumer which is PSE in this instance. So a relative time of "y" in the PI Point DR config will be a time range of yesterday until current time/query date; you can't specify a relative end time. I think the OP is looking to display today's current average and yesterday's average from within PSE. You'd be able to do it with Abacus though by outputting a PE of TagAvg('Attribute','y','t') to an Attribute.

                                • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                                  wallacejason

                                  Any sign of Abacus yet? And how long after release until it's reasonably bug-free?

                                    • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                                      Roger Palmen

                                      Another option before Abacus would be to implement a custom DR (yes, i like my custom DR's!), that is able to resolve a PE equation.

                                       

                                      All required is already available in the v2.5 AFSDK, thus this should not be a huge task.

                                        • Re: Calculating yesterday's averages for a PI Tag using PI AF?

                                          Now we get into a philosophical discussion...custom DRs would typically execute on the client given that is the common way to architect them. For calculations, or simple averages etc, this would mean repetition of calculations amongst each consumer of the custom DR, which means the potential impact to the PI Server (and network) could be huge depending on the calculation(s) & user base - and not forgetting the user experience. Typically the PI Point DR in PSE is used for instantaneous values, or as a place holder for where to interrogate for larger analyses. This is (IMO) one of the reasons behind Abacus and why the Formula Data Reference wasn't extended to cover PE syntax; it doesn't scale.

                                           

                                          If you were to go down the custom route it would need to implement similar functionality to Abacus (back end processing) to scale, and so I think it would be a huge task. Otherwise we would have had Abacus 6 months ago. Just look at the work that's gone into the Abacus expression editor, it is awesome.

                                            • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                                              skwan

                                              Jason:

                                               

                                              Please consult the PI System Roadmap for estimated release dates.  Currently the estimate is Q1 2014 for AF 2.6, which includes Abacus functionalities.  Come to vCampus Live! 2013 if you want to play with it :-).

                                                • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                                                  Roger Palmen

                                                  Agree that DR's execute on the client, thus any use of custom DR's must be embedded in a sound solution architecture. I mainly like DR's because the results are not persistent, and for some applications this can make life easier instead of solving the recalculation puzzle. After all, the recalculation problem is typically only solved in a few variants, but to date not in ACE nor Abacus no signs of auto-recalculation functionality. Yes, i know the recalculation functionality of PE tags has some drawbacks too (e.g. high peak loads during recalc), but none that can't be resolved imho.

                                                   

                                                  To continue the philosophy, now (meaning AF v2.6) we either have no persistence (DR's), or persistence forever (Abacus). Couldn't we have something in between? E.g caching of attribute data server-side for an x amount of time?

                                                   

                                                  Some of the complexity and components required is already in place. E.g. invalidating cache results for stacks of calculations should already be there in e.g. the AF Datapipe.

                                                   

                                                  But maybe we should create a new thread for philosophy topics!

                                                    • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                                                      j.rao

                                                      Great to see quite a discussion on this topic.

                                                       

                                                      Thank you all for contributing. I am assuming the following from what I gleaned from the replies so far :

                                                       

                                                      Calculating yesterday's averages in PI System Explorer alone is impossible as of today.

                                                       

                                                      PI AF SDK and custom DR's might be an option.

                                                       

                                                      Project Abacus can fill this gap but is due out early (hopefully) next year.

                                                       

                                                      Gopal, Han : While PI OLEDB Enterprise works great, we want to do this in PI System Explorer alone. We are using XHQ which is driven by AF. XHQ computes the day averages pretty easily using canned functions. Unfortunate that AF couldn't currently.

                                                       

                                                      Roger : I am trying to avoid any custom development with the current timelines I have for this particular issue.

                                                        • Re: Calculating yesterday's averages for a PI Tag using PI AF?

                                                          Part of the problem from AF & in particular PI System Explorer maturing is the intended use of PI System Explorer. PI System Explorer is not your typical application for interrogating data, it is was intended to be an administration tool for managing hierarchies/configuration/meta-data. However, as with all good applications they are sometimes driven in another direction by their user base. Many people want to use PI System Explorer for viewing data now, you just have to look at the last version of PSE that introduced Rich Data Access functions directly on an Attribute, and PSE 2.6 that goes a step further by introducing the Coresight trend object within PSE. Maybe our favorite PM on vCampus can comment further on the OSIsoft direction for PSE as a client analytical tool vs an administration tool (okay with you Steve? )? So I think some of the general data statistical gaps you're finding will naturally be there.

                                                           

                                                          Anyway, back to the your problem...are you always going to make the comparison to fixed time periods for display & comparison within PSE? Or do you need flexibility to provide dynamic time periods for comparison? In other words, for the "yesterday" average couldn't you calculate those within PE now then later migrate to Abacus?

                                                           

                                                          On Siemens XHQ, the adapter will be built around PI/AF SDK, right? So the custom development needed for PSE has already been anticipated in XHQ by wrapping around the SDKs, not that AF (SDK) itself can't provide such functionality. If memory serves me right one nice feature of XHQ is the server side caching of the retrieved values, something my colleague Alex Brodskiy has been preaching for AF to adopt for the last couple of years...Alex is coming to vCampus Live in December for the sole purpose to hunt down the AF team for an update.  

                                                            • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                                                              j.rao

                                                              I will look forward to more commentary from the PM on PSE. It is a very powerful tool for several use cases.

                                                               

                                                              I am always interested in a fixed time interval for yesterday's averages. No fancy time ranges or anything like that. I am swaying from creating new tags for this effort, hence not considering PE for this effort.

                                                               

                                                              We are using OLEDB Enterprise and XHQ is querying the AF Linked server, that is how we set it up. Yes, XHQ has server side caching which is set to 15 minute refresh in our case.

                                                                • Re: Calculating yesterday's averages for a PI Tag using PI AF?
                                                                  hanyong

                                                                  I would clarify again just so that we are not having any misunderstanding here. It is not that you can't get yesterday's average from a PI Point Data Reference attribute in PSE. But because the setting in the data reference is always relative time, you have to take into account of the time you are querying for data. Even if you are querying now if you pass the timestamp parameter as today at 12am, it will still give you the appropriate values. In PSE, if you change the query date parameter, that will give you the effect. Whereas in PI OLEDB Enterprise, it is simple getting interpolated data at today 12am.

                                                                   

                                                                  Considering that you are integrated XHQ and AF with PI OLEDB Enterprise, I can imagine you can create a view that queries for data at today 12am so that the data returned is summaries for the whole of yesterday. Then regardless of the time of the day you are querying the view, it will always return you the aggregated value from yesterday. Something like below, where the time parameter of the data table (ft_InterpolatedDiscrete) is set to 'T'

                                                                   
                                                                  SELECT i.*
                                                                  FROM [NuGreen].[Asset].[ElementHierarchy] eh
                                                                  INNER JOIN [NuGreen].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID,
                                                                  [NuGreen].[Data].[ft_InterpolateDiscrete] i
                                                                  WHERE eh.Path = N'\' -- root elements
                                                                     -- function tables require the WHERE clause to contain restrictions for all function arguments:
                                                                  AND i.ElementAttributeID = ea.ID -- first InterpolateDiscrete TVF argument
                                                                  AND i.Time IN (N'T') -- second InterpolateDiscrete TVF argument
                                                                  OPTION (FORCE ORDER, EMBED ERRORS)
                                                                  

                                                                  I agree with Rhys that PSE isn't really a tool that is designed for client analytics in mind. At the same time, it seems like what you really want is to move the desired data into XHQ, so I believe just querying the data with the appropriate time context would meet the requirement of getting the aggregated value from the day before into XHQ.