3 Replies Latest reply on Aug 13, 2013 10:08 PM by fbatista

    PI AF Table periodic refresh

    fbatista

      Hi folks,

       

      I have an AF table that should contain 30 days of data pulled from an Oracle database. My idea is to have it refreshed once a day, at midnight (old data will be purged, and new data will be added to the table). So when the user accesses the data in the morning, the table will have already been refreshed. 

       

      I thought the option "Import" with a Cache Interval = 24 Hours would solve my problem, but I noticed that the table is populated only once, just after being configured. After I configured the "Import" option,  the data remained static, even after new data were added to the external database. 

       

      Using a linked table is not an option, because it will bring the entire dataset to the client side (after the cache interval is expired of course), affecting the user experience. I also considered the idea of abandoning AF tables in favour of using RDBMS tags, but it would add more tag management complexity and unnecessary data replication.

       

      Should I create a service to automatically update the AF table? Any ideas?

       

      Your suggestions will be appreciated.

       

      Best regards,

       

      Fabiano Batista

       

      Syscontel Technologies

        • Re: PI AF Table periodic refresh
          xwang

          Hi,

           

          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.

           

          Xi Wang

           

          v-Campus team

            • Re: PI AF Table periodic refresh
              Roger Palmen

              Fabiano,

               

              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.

               

              Note:

              • 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:

               

              http://vcampus.osisoft.com/discussion_hall/development_with_osisoft_sdks/f/28/t/4161.aspx

               

              http://vcampus.osisoft.com/discussion_hall/development_with_osisoft_sdks/f/28/p/3903/21010.aspx