Data statistics and PI OLEDB Enterprise, Part One: Introduction

Blog Post created by bperry Employee on Aug 24, 2012

This is a two-part post. 


Part One, below, is an introduction into obtaining PI summary data (statistics etc.) within PI OLEDB Enterprise.
Part Two is where things will get interesting: a look at how to exploit the nuances of PI Time Syntax when creating reports containing summary data. In other words – how to get statistics for different types of time ranges, like month-to-date.



Raw data or statistics?

As many here know, PI OLEDB Enterprise (as of 2010/2012 versions) does not directly support statistical calls. If I have an AF attribute giving me raw production flow…




…then within PI OLEDB Enterprise I’m limited to getting various renditions of those archived values.










For production flow, though, what really matters to me is total production – e.g. the total for today, or the month-to-date total, or some such thing. Total is key; the flow at given points doesn’t matter for my use case. So how do we coax a total out of PI OLEDB Enterprise?



What not to do

One forbidden fruit is the included aggregate SQL functions: SUM, AVG, MAX, MIN, COUNT.






Don’t get me wrong – there are definitely uses for these functions – but more often than not, they don’t fit the use cases of PI OLEDB Enterprise. What if we tried taking the SUM() of today’s values for Production flow? First off, we’d get an event-based result – senseless here – though we could fudge things with some interpolation. Second, this would be wildly inefficient! Why bring back a mountain of data from the PI Data Archive, only to aggregate it locally – when the PI Data Archive can do aggregations itself?



Adding statistics to the AF model

Thus the question becomes: How do we coerce the PI Data Archive into doing our aggregations for us? Thankfully, the PI Point Data Reference has this capability. So back in AF, we’ll add some attributes below Production Flow which will eventually show the numbers as desired.






If we begin by configuring Total 24h, note that we already have a perfectly good PI tag to go and totalize – whichever tag Production flow is pointing at! So I’ll set this data reference to use Production flow’s tag as its source.








But the interesting part is below, where if time mode is set to be a time range, we get a wealth of “value retrieval” modes: among them, Total, just as we need in this example.






Our totalization requires some parameters – source units and rate, and the relative time range to use. Since our attribute is specifically a “past 24 hour total,” the appropriate time range is “-24h”






This attribute, which does totalizations relative to an end time, will be directly accessible in PI OLEDB Enterprise. Here, we see it in PI System Explorer:





Querying the statistical attributes

I threw together a query to query my wells for their 24-hour Production flow total, for two different time stamps:


between * (now) and 24 hours before now


between the beginning of today (t) and 24 hours before then


Remember, the “24 hours before then” part is what we set in the Total 24h attribute configuration. What we’re specifying in the query is what “then” means.



SELECT e.Name as Well
, REPLACE(ea.Path,'\','') as Metric
, ea.Name as Aggregate
, di.[Time] as [Time]
, di.ValueDbl as Value
, UOMAbbreviation(ea.DefaultUOMID) as UOM
FROM Asset.ElementHierarchy eh
INNER JOIN Asset.Element e
ON eh.ElementID = e.ID
INNER JOIN Asset.ElementTemplate et
ON et.ID = e.ElementTemplateID
INNER JOIN Asset.ElementAttribute ea
ON ea.ElementID = e.ID
INNER JOIN Data.ft_InterpolateDiscrete di
ON di.ElementAttributeID = ea.ID
WHERE eh.Path = '\Wells\' --restrict by Path
AND et.Name = 'Well Template' --Template
AND ea.Name = 'Total 24h' --Attribute
AND ea.Path = '\Production flow\' --Parent Attribute
AND di.Time IN ('*','t') --Two different timestamps to query
ORDER BY e.Name ASC, di.Time ASC







So there you have it – the ability to request all of the native PI Data Archive statistics from within PI OLEDB Enterprise (and any other PI AF client) relative to the time of your choosing.



First caveat

Flexibility and overhead. Quite obviously, this is not a flexible way of doing things. It requires the infrastructure (PI AF model) to contain pre-configured, aggregated attributes to be queried. This adds overhead and removes flexibility, but the good news is that the AF SDK contains (as of version 2.5) the underpinnings for PI OLEDB Enterprise to offer native PI statistics in the future. I dream of a future without need of this workaround… but until then, this can work well.




Can work well?”…. Ah! Thus, we arrive at the...

Second caveat

Cost. Remember that these statistical calls are done on-demand. The magic formula for cost is:


(Attribute count) * (Data rate) * (Time range) = (Cost)


So if you’re storing 60Hz data in your PI archive, please don’t call an on-demand daily average for 1000 attributes at once! Your data rate is probably much saner, but regardless: please don’t do (e.g.) year-to-date totalizations on-demand! That type of expensive call is why we have PI Totalizer tags. Or if Totalizers make you squeamish, then PI ACE. Either of those solutions can write year-to-date totalizations to a PI tag at the interval of your choosing, which you can expose in your PI AF elements and query (raw values) from PI OLEDB Enterprise. It is additional configuration overhead, but your PI Data Archive server will thank you. On-demand statistics follow the universal on-demand mantra:


if it’s cheap, do it on demand; if it’s expensive, schedule and store.



To get PI data statistics within PI OLEDB Enterprise, create AF attributes which expose a certain "value retrieval method" (average, total, etc) for a relative time range (e.g. -1h for the past hour). Then query those on-demand statistical attributes, interpolated, within PI OLEDB Enterprise. This works well for cheap/small statistical calls, but for expensive calls, use a calculate-and-store option like PI Totalizer tags or PI ACE.



Up next

Stay tuned for Part 2, a dive into exploiting the nuances of PI Time Syntax when we write reports – to get, for example, a week-to-day total in PI OLEDB Enterprise.