8 Replies Latest reply on Oct 8, 2010 2:19 PM by spilon

    Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data

    mikeloria
      I started to dive into the linked tables in AF and found some very powerful uses. We have a lot of OLEDB compliant data in RDBMS and linking these tables provides the ability to use this data for notification criteria and a more powerful approach for analysis. I also viewed the data in processbook because my linked table field attributes were using a PI tag for filtering criteria. This also has numerous benefits for providing a much more detailed means of visualization.
       
      I do have questions though concerning the cache updating and how this will effect visualization when needing to go back months in time and needing several thousands of records.
       
      It appears that the way linked tables work is that the data is cached in main memory and when it is refreshed, it will refresh all records. This limits the amount of data in the cached set to the desired amount of memory usage but more importantly, the efficiency of retrieving the data is significantly impacted.
       
      In many of my cases, I would like to have a significant amount of data cached or possibly even stored locally but I do need to refresh a small amount of the current data and retrieve new records. What I am saying is that it would be nice to have just a small amount of refreshing to be added to a cache instead of having to refresh the entire cache. Is this possible or is this something that is under consideration?
        • Re: Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data
          Ahmad Fattahi

          You are raising an interesting point Mike. I have asked the experts to weigh in. In the meantime, could you give us some feeling of how much it affects your application? In other words, how big the efficiency degradation caused by refreshing the whole cache is? How much data are you retrieving from the table? And is your concern only limited to linked tables?

          • Re: Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data
            pcombellick

            AF is just a pass through for your arbitrary query that is targeted to an external datasource.  Each time your AFTable object is refreshed, your query is re-run.  AF does not have any mechanism to modify your query (which might be a stored procedure call) to just request changes "since the last time the query ran".

              • Re: Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data
                Ahmad Fattahi

                Currently the entire table is cached in the client. However, this has been recorded as a future feature:

                 

                +++++

                 

                Add support for parameterized table queries

                 

                 

                 

                Need support for parameterized table queries.  Essentially, AFTables should provide a mechanism for definining parameters which can be filled out using Element context.  This mechanism needs to be similar  to the mechanisms defined by WebParts and BaseLine Services for the relational data sources.

                 

                +++++

                 

                 

                 

                There’s no plans to add this feature to the next release of AF, but perhaps in the release after.

              • Re: Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data
                mikeloria

                We have product set up records in OLEDB compliant databases and production records as well. There are new records that are added about every 5 minutes. Having these in linked lookup tables is extremely powerful and very feasible. We only need to refresh about the last 500 records(2 days worth) which would also include the new records. I would like to do this every 30 seconds to a minute. It would even be nicer to do it on a trigger which could be a PI point.

                 

                The problem is that we many times need to go back 2 or 3 months which could be 50,000 + records most of which are static. If I wish to use the elements in processbook and/or reports created programically, I am not sure how it can be done.

                 

                It would be nice to have an append feature for the linked table.

                  • Re: Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data
                    skwan

                    Thanks for the input guys.  As noted earlier, there is currently a Work Item on this topic.  We're inclined to work towards a more generic solution, as opposed to any specifc data reference handling features because we want the most bang for the bucks - i.e. concentrate on the features that help the most people.  I'm thinking that a scheduler, plus some sort of an improved analytics capability in AF to handle formulas and strings, along with an AF data reference that can support parametized table query would do the trick.

                     

                    OK, that was a hint on some of the things we're thinking of implementing, without being too specific so I don't get into trouble with the developers .  Keep the discussions and suggestion coming.

                     

                    Regards, Steve

                    • Re: Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data
                      pcombellick

                      Mike,

                       

                      What does your query look like?

                       

                      In the meantime, you could write a custom DataReference that encapsulates your desired "refresh" behavior. 

                       

                      Regards,

                       

                      Paul

                       

                      AF Dev Team

                        • Re: Using AF Linked Tables for integration with OLEDB Compliant RDBMS Data
                          mikeloria

                          Hi Paul

                           

                          Please explain custom data references and where I may find literature on this.

                           

                          Right now I am doing a simple query such as SELECT TOP 100 * from TABLE ORDER BY date DESC; This gives me the last 100 records because it is critical that I receive the latest records. I am running this once per minute.

                           

                          I have a PI tag that is an element attribute that is used in the where clause for other attributes in the element that are values coming from the lookup table (SELECT <field1> from <lookuptable> where <field2> = <PI tag attribute>).This works out great because I am using data from the lookup table for notifications.

                           

                          I want to also use the element for visuaiztion in processbook and datalink (when it becomes available). The problem is that I may need to visualize something fom several 1000s of records (> 50000). I think right now it is all or nothing. Either I refesh all 50000 or I do not refresh and do not get the most current record.