4 Replies Latest reply on Jun 1, 2016 4:21 AM by eradwan

    Integrate LIMS data in AF


      Hi There,


      I have a .Net utility running on top of LIMS/Oracle based system that exposes a tree like GUI for Plant Units / Sample Points where the user expands the tree , selects required components from one or many sample points, hits a button and get the lab results in Excel. Now, I want to migrate this asset structure and code to be based on AF, and after I did my homework searching PI Square I got to know that the best approach is to send LIMS data to PI in tags or using the table lookup feature. Unfortunately, the PI tags based solution isn't an option in my site and I want to consider the table lookup scenario. My worry here as I'm thinking before coding is, in my legacy code I send one SQL query having all selected sample points / components where I hit Oracle only once, but having each LIMS component mapped to an attribute in AF, does this mean if I expose a similar Tree view in a web app on top of AF and with the same use case of a user selecting many components from different sample points, then getting values will hit one SQL query per each attribute / component, which will be slow?  Or there is a way to have a 'bulk' read in one query as we have one the attribute is liked to a PI tag?





        • Re: Integrate LIMS data in AF
          John Messinger

          I did a similar project a few years back, where the customer wanted to integrate their LIMS via AF. We used a linked table (via a view) that brought in approx 10K rows of data, that were then queried using the Table Lookup data reference. This dataset represented values for every sample point This did not appear to hit the server with a significant increase in requests. We specifically limited the number of rows in the AF Table, due to the potential for performance impacts with a large row count (see this thread for some more information). You will find a number of threads in these forums that talk about performance of AF tables with large row counts. The attribute lookup via the Table Lookup data reference hit the locally cached data in the AF Table, which was periodically refreshed at a pre-defined interval.


          My experience with this was that at the end of the day, you can configure the table lookup to the LIMS database a number of different ways to access the data, and each will have their relative strengths and weaknesses. In our case, it was that a limited amount of sample history would be available in the linked table due to our requirement to maintain an upper limit of 10K data rows (which was an arbitrary limit). With some creativity, you can probably write a stored procedure that takes in a list of components / sample points, and return the required results from a single query, or even re-use the query that your .Net utility executes.

          4 of 4 people found this helpful
            • Re: Integrate LIMS data in AF

              Thanks for the info, John.

              I just had a few follow up items.

              1. For those interested in more information on AF Table caching, here is an article:

              KB00539 - How AFTable Caching Works

              2. To avoid the problem you mentioned of querying large row sets with the Table Lookup Data Reference, query parameters were added to AF (2.6, I think).  Here is the documentation:

              PI Server

              With this method, we can affectively request a small number of rows (often just one row) per attribute from the foreign table rather than having to return thousands of rows from the foreign system with our AF table query and then query this large result set with the table lookup DR.  So I guess some testing would be needed to see if using a view on the foreign system that gives a larger cached AF table is better or worse than using query parameters to cache individual results for many attributes.

              Emad, please let us know what you find to work for you.

              2 of 2 people found this helpful
                • Re: Integrate LIMS data in AF
                  John Messinger

                  Yes, I remember when the ability to create parameterised queries for AF linked tables was released, and in some instances this goes a long way to solving some cases where otherwise a large dataset would have needed to be returned. I think in Emad's situation, this could still result in many queries being made to the Oracle backend, as each component mapped to an Attribute would pass the component name or ID as a parameter to the query. If there is concern with the number of queries to the database that could be executed, I'm not sure if query parameters in the table link would address that concern. But again, creative stored procedures or queries might just do it! Agree that testing to see what is the better option is the way to go.

                  1 of 1 people found this helpful
                    • Re: Integrate LIMS data in AF

                      Thanks John,this is exactly what I wanted to say. How do you think a stored procedure would fix this multi query issue? The problem still exists as the parameters values will change for every component_name. On the other hand, I believe I'll go with linked tables as I don't see value of duplication a large data set between Oracle and AF SQL tables specially that its a huge one.