8 Replies Latest reply on Dec 12, 2018 2:17 PM by Tomas Stark

    Event-triggered table lookup analyses having performance problems

    Tomas Stark



      we have about 900 table lookup attributes in our PI AF system fetching data from a remote SQL database and via event-triggered analyses stores the data in corresponding PI Points. The values are in the SQL database updates about one time every second or third week (they are manually entered data in a rounding system). The reason to store the data in PI Points is that we want to have it historised in the PI Data Archive and we have manually back-filled 15 to 20 years back in time.


      This works pretty good, but intermittently some data is not fetched when the data changes in the SQL database. I would like to understand better how the functionality for this is working and if there is some tuning we can do to have it 100% reliable.


      Or perhaps, is there a smarter way to do this? The RDBMS interface could be one solution, but at the other hand we are only talking 900 readings here being changed every fortnight or so.


      Below is an example with nine table lookup attributes and the corresponding nine PI Point attributes.


      180424 Skärmklipp 1.jpg

      The event-triggered analysis just outputs the value of the data lookup attribute to the PI Point attribute whenever the value changes.

      180424 Skärmklipp 2.jpg




        • Re: Event-triggered table lookup analyses having performance problems

          Hi Tomas,


          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.




            • Re: Event-triggered table lookup analyses having performance problems
              Tomas Stark

              Hi Sebastien,


              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.