AnsweredAssumed Answered

How does a Formula DR support time-weighted average in PI SQL?

Question asked by JPBarnard on Jun 19, 2020
Latest reply on Jun 24, 2020 by JPBarnard

Stephen Kwan Can anyone explain to me why and how a Formula DR manages to support Time-weighted Average via PI SQL transpose summarise functions in PI System 2018 SP3 Patch 1? As I understand the call stack, the PI Totaliser Subsystem ultimately does the summary when called from PI SQL. But, in my case here, the input to the formula is a Boolean (logical) PI Point, which does not support time-weighted average in the PI Totaliser Subsystem. Here is the Formula DR definition:


A = MyBooleanPIPointDR; [A]; step=1


Strangely, the above DR functions well when targeted via a transpose summarise function in PI SQL with the time-weighted average method.


Alternatively, when targeting a reference attribute that casts the Boolean to Double or Single, the PI SQL transpose summarise function fails to return time-weighted average (which would be otherwise totally understandable given that the PI Totaliser does not support time-weighted average on type logical and a reference attribute will be resolved to the target attribute, which happens to be Boolean). But, superficially, the above formula does the same thing as casting the Boolean input to Single or Double via a reference attribute.


So, has something changed in where transpose summarise is implemented along the call stack?


BTW, since PI System 2018 SP3 Patch 1 (perhaps earlier, but we have skipped a few versions along our upgrade road) I have noticed a marked improvement in AF Formula DRs under PI SQL queries. Previously, we have learned to avoid targeting Formula DRs in transpose functions in PI SQL, exactly because of bad query performance over even moderate time ranges. Not anymore. Well done. And RTQP does 100x better.