14 Replies Latest reply on Jan 13, 2016 6:29 PM by vwitzel

    Compatibility of Table Lookup AF Attributes and AF Analyses


      Hi all,


      I am trying to better understand the compatibility of Table Lookups AF Attributes with AF Analyses. In my testing, I configured the following:


      1. A linked AF Table that is pointed to an SQL relational database (RDB) and set to a 10s cache interval. Security is set to impersonate client.
      2. A Table Lookup AF Attribute ("TL") pointed to the AF Table and reading a numeric field.
      3. A dummy int32 PI tag with point source L.
      4. A PI Point AF Attribute ("Output") pointed to the dummy PI tag.
      5. A periodic (10s) AF Analysis that adds "1" to the value of the  "TL" AF Attribute and writes the results to the "Output" AF Attribute.


      When I tested the AF Analysis by hitting "Evaluate," it displays the correct calculation results in the "Value" column. However, the actual Analysis only writes "Calc Failed" to the dummy PI tag. Is this because AF Analyses do not support the use of Table Lookup Attributes, or is there something I should be doing differently in my set up?


      Assuming AF Analyses do support the use of Table Lookup Attributes, I would also like to know:


      1. Can they be used to event trigger Analyses?
      2. How does the cache interval of linked AF Tables impact the data seen by AF Analyses that have Table Lookups as inputs? I understand that the cache interval of AF Tables is a client-side property. Taking my above set up as an example, if a new record was inserted into the SQL RDB, would the 10s periodic execution of the AF Analysis trigger the expiration of the cache interval and a refresh of the AF Table?


      Appreciate any insights you could share


        • Re: Compatibility of Table Lookup AF Attributes and AF Analyses

          PI Analysis Service does support Table Lookup DR's. Regarding Calc Failed during runtime, there could be many possible reasons. I'd first check the permissions on the linked table for the analysis service account. Table Lookup DR's can be used for event triggered analyses. If you want to decrease the data latency, then the cache interval should be small so new data is fetched from the source when requested. Note that Table Lookups don't scale very well in PI Analysis Service and even a few bad apples can break other analyses. Also, although the AFTable data is cached client-side, the cache interval (time span) is actually a server-side property, but each client will have its own start/end time (time range)

            • Re: Compatibility of Table Lookup AF Attributes and AF Analyses

              Hi Barry,


              Thanks for your input. A couple of thoughts:


              1. In my test set up, I've got PI, AF, and the AF Analysis Service running on the same machine. The latter is running under "Network Service" and has read/write to the AF Table in question. Any other thoughts on why this calculation would error at runtime?
              2. Just to confirm, does the execution/triggering of an Analysis (be it periodic or event triggered) that has a Table Lookup DR as an input cause the underlying linked AF Table to update / refresh with a new copy of data from the RDB?
              3. KB00539 states, "The AFTable cache interval is strictly a client side property." However, I think what you are implying is that the actual interval duration is stored in the server, whereas the time when the Table was last accessed/updated is maintained on the client. That's how I understand that to work as well



                • Re: Compatibility of Table Lookup AF Attributes and AF Analyses

                  For 1, I'd try running the Analysis service as a privileged user with access to the underlying database and also PI and AF. Perhaps the underlying SQL db doesn't allow the Analysis computer account to authenticate. What is the Table Connection connection string and security configuration? This doc talks more about linked table security (i.e. AF Server performs the query on behalf of client). https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v3/GUID-D2F0AFF2-343A-4E62-9CD4-B3B234931488 


                  2. For table-provided time-series data, I believe TLDR supports AFDataPipe which populates the AFDataCache used by Analysis. So there are two caches: the table and analysis data cache. Analysis will look in AFDataCache first. If data is outside of cached range, it will look in table cache and refresh if needed. The AFDataPipe also asynchronously updates the AFDataCache in the background and should be going to the datasoure when the table cache is expired. I can find out some more details on the exact mechanisms and let you know.


                  3. Yes, that is all correct.

                  1 of 1 people found this helpful
                    • Re: Compatibility of Table Lookup AF Attributes and AF Analyses

                      So as I mentioned, the PI Analysis Service was running under the Network Service account. This account did not have login privileges to the SQL DB I had linked to my AF Table. I created a SQL login for this account and restarted the Analysis. No change - I still got "Calc Failed". I then granted that SQL login the db_datareader role on the SQL DB and restarted the Analysis. Success . The Analysis now produces results. That being said, I am not sure I understand why that is. As you pointed out, the AF Server performs the table query on behalf of the client. From the get-go, when I viewed the AF Table within PI System Explorer, it was populated with data, so why could the PI Analysis Service not use this data?


                      FYI - I confirmed that a change made to the SQL DB is automatically captured by an AF Analysis using this data via a linked AF Table. That is, I did not have to "force" an update to the AF Table cache by accessing the AF Element holding the Table Lookup DR (or some other kind of AFSDK query to that Table Lookup DR).