Yes, the AF table cannot be refreshed automatically actually. My suggestion is to use AF SDK make a programming to create either "Import" or "Link" AF table. You could delete this table every day, and after deletion, you could create the table again. Make sure the "Check In" method has been done after every action of the table, creating or deleting.
I think it will not make a difference if the table is a linked table or an external table, as in both cases the client / AFSDK will fetch the entire dataset and do the filtering client-side (not 100% sure on this though!) For a similar scenario i also use an external table referenced in AF. Although for my scenario another reason to use AFtables is that the table holds future data ruling out any use of PI Server.
- The client will connect to the external database, and NOT AF server, thus you need connections from ANY client to the data source!
- The recommended limits for an AF table size of about 10K records. Not set in stone, but anything beyond that you would need to investigate the performance implications of the table size. There is caching however on the AFtable, thus depending on the latency you can suffer, you could use that to prevent frequent trips to fetch the external data.
Another viable option though is to use a custom AF DataReference to get the data from the external server. You can make that do and perform any way you want, and that would be my first choice if an AFtable is not an option due to size or the lack of Server-side access, filtering or buffering. Creating a custom DR that calls a WCF webservice that runs a query on the database should not be a difficult task for any programmer (i would estimate this to be about 15 days work, for design, develop, unittest, perfomance test and documentation).
Some threads on a similar topic:
Thanks to Roger and Xi for the information provided.