What are the inputs to the attribute with a table lookup data reference that triggers your analysis? For the analysis to be triggered, the input needs to support AFDataPipe and therefore the input to the table lookup data reference need to support AFDataPipe. If there are PI Points as inputs to the table lookup, are the input PI Points updating at the same frequency as the table is in SQL ? You can find more information on what data references and what configuration support AFDataPipe here: PI Server
What is the cache refresh interval? If it is set to manual, the table will only be reloaded into memory by the analysis service after a service restart. You can find more information on how table caching works in AF here: https://techsupport.osisoft.com/Troubleshooting/KB/KB00539/
Which version of Asset Analtics and AF are you running? We have a few known issues in previous versions that you may be encountering.
we use the latest PI AF 2017 R2.
The table connection uses Microsoft OLE DB Provider for SQL Server. I'm not familiar with AFDataPipe and can't find much documentation about in Live Library. Seems to have more to do with application development which is not the case here .
I have now minimised the table query so that we only fetch the two table columns (time and value) that we are interested in, and have also changed the Cache Interval from 1 minute to 15 seconds. Still don't really understand how this is used. Will PI AF send the SQL Query every 15 seconds and store the whole result in memory and the event-triggered analysis will check (how often?) if any value has changed and then trigger the analysis to execute and output the value to the corresponding PI tag?
In a couple of weeks I will be able to conclude if these changes have helped.
What's the configuration of the table lookup attribute? Is it a static configuration or is there a PI Point? If it's a static configuration you will need to configure the analysis to be Periodic instead of Event Triggered.
Event triggered analyses get triggered when an input updates and this update is fed to the analysis via an AFDataPipe. In the case of a static table lookup, the analysis service will never get triggered as there is no update being fed up to the analysis.
The table caching is a client side property. The table will be refreshed every 15s in the memory space used by the PI Analysis Service.
Hope this helps clear things up. Please let us know if you have any additional questions.