I am trying to better understand the compatibility of Table Lookups AF Attributes with AF Analyses. In my testing, I configured the following:
- 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.
- A Table Lookup AF Attribute ("TL") pointed to the AF Table and reading a numeric field.
- A dummy int32 PI tag with point source L.
- A PI Point AF Attribute ("Output") pointed to the dummy PI tag.
- 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:
- Can they be used to event trigger Analyses?
- 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
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)
Thanks for your input. A couple of thoughts:
- 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?
- 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?
- 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
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.
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).
When you click Evaluate, the data is retrieved under the impersonated client account running PSE, which probably already has permissions to SQL. This data is retrieved on demand and not available to PI Analysis Service. It exists in the PSE memory space and that data isn't published to other processes. When the analysis actually runs, a separate data call is made by PI Analysis Service under its account and that data exists in the analysis service's memory space.
You're correct in that no forcing of an update is needed via PSE to update the table data. This is all done under the hood by the AFDataPipe inside analysis service periodically polling the SQL db.
Interesting. I was not aware that the Analysis Service's credentials would in fact be passed through to the RDB that is linked to the AF Table.
I did find that after I changed the Analysis from being periodic to event triggered, changes made to the SQL DB (namely, newly inserted records) did not trigger the Analysis. Is this the expected behavior?
Event-triggered should work. Can you share the TLDR configuration? Is Table-provided Time Series data used? What is the version of PI Analysis Service and AF SDK on the analysis machine?
Sorry for the delayed response, Barry, I did not see that you had responded to the thread. To answer your questions:
1) The Table Lookup DR (TLDR) is configured as shown in the first screenshot below.
2) Yes, the TLDR is configured to return time series data, as you can see in the first screenshot below. If I change the DR's rule to "Select first row matching criteria" and change the Analysis to be event-triggered, the Analysis goes into an error, indicating that the Attribute cannot be used an input (see second screenshot below).
3) The PI/AF/Analysis Server has PI Analysis Service (x64) 2014 R2 and AF SDK v220.127.116.1158 installed.
Your Table Lookup DR configuration seems a little weird. Your time column is "LastUpdateDate" so I will assume the values in that column of the table are in DateTime format. Meanwhile your WHERE clause says: LastUpdateDate = @CDT158. Is this really what you meant to configure - the LastUpdateDate equals the value of CDT158? (I will assume CDT158 is the name of an attribute?)
Stephen, what you are alluding to are just the defaults for the combo boxes . I am not actually using a WHERE clause. My config string reads:
SELECT PimlVersion FROM testTable17;TC=LastUpdateDate
Would you please use a meaningful WHERE clause in the configstring and see if the analyses trigger?
Interesting! With a WHERE condition in the mix, event-triggering the AF Analyses works using the Table Lookup DR . Any idea why a WHERE clause is required?
Strictly speaking, without a WHERE clause, the configuration is incomplete. Let's look at your screen shot of the configuration:
You're using testTable17, you want the value (result) from the column PImlVersion, you have designated the column LastUpdateDate as the source of the timestamps. That's all you have configured. The TableLookup doesn't know which table row you want. After you configured the WHERE clause, now it knows which table row you want.
Understood. Thanks for your help, Stephen