AnsweredAssumed Answered

How far are you pushing table lookups?

Question asked by jasongogal on Jan 12, 2019

Documentation suggests that table lookup data references should be kept relatively limited to context data. However, in practice, I find that the table lookup database select functionality offers some advantages over trying to do analyses and comparisons in the PI-side data! One example would be a sum of something for the past week vs. a sum of something planned. Let's say a planned number of shipments in a PI future data PI Point versus an actual number of shipments from a database. The actual shipments table lookup "sum(number of shipments) where date > 1 week ago and date < now" is a blazing fast transaction from a linked SQL database. However, to look at a rolling sum of planned data that is stored in a future type PI Point, doing an AF analysis of the old "tagavg*eventcount(tag, '*-7d','*') or tagtot from a future tag is actually a far, far slower and clumsier transaction to perform. As an aside, I wonder if this is due to the fact that PI handles future data tags in a separate archive as per documentation? As another aside, what am I missing about why rolling sums and totals - even of very sparse data like 1 point per day - always seem so slow and cumbersome to create and execute in AF analyses, and in general in the PI system? Or is that perception influenced by the availablity heuristic of my past couple of days of experimenting with summarizing future data tags?


Either way, I'm mainly wondering how far other users are pushing table lookups? At the same time, I wonder where the boundaries of "right tool for the job" are being drawn for users in other settings. Recognizing that there's no real one size fits all solution, it would be nice to hear how others are approaching data correlation and analytics from PI side vs. non PI side data, especially in a rolling sum or other higher order time context. PI absolutely nails showing what I call "zeroth order" data display where you're just showing a trend, but sometimes, as described above, struggles with "first order" and higher order types of analyses where you are trying to do rolling totals, rolling sums, counts, rolling averages, etc. for display in PI Vision.


Message was edited by: Jason Gogal