Accessing Relational Data through AF

Discussion created by arm77 on Oct 24, 2010
Latest reply on Oct 25, 2010 by pcombellick

Hi All,

Appreciate if you all could provide valuable information on the below issue.

Osisoft ,Could you please guide on what will be your  strategy in  future releases so that we can align our apps accordingly


Abdul Rahman



Accessing Relational Data through AF




Introduction:- PI AF does not provide out of the box feature to pull historical data from Realtional Databases like  Oralce or Sql Server. In order to tackle this issue we had to develop a Custom Data Reference.This Custom Data Reference connects to any relational database and pulls historical values.


Issues With Custom Data Reference:-


AF SDK ( Interface to Connect to AF)  is structured  to only  allows  to pull data of one attribute  at a time, In order to get historical values of 5 attributes, it makes 5 different queries(calls) to the database.








Even if all the 5 attributes reside in the same table ,it still makes 5 calls to the database to pull data.There is no straight forward way to consolidate these query calls to one single call(So that we can speed up) .


Possible Solutions:-


1.     Solution 1:- ByPass DataReference






In this solution we need to read the configuration information from the AF Attributes and make a consolidated/optimized Query calls to the Relational database.




1.     By Bypassing the Data Reference, we loose the “Reach” of AF Clients for eg PI Process Book /RT Webparts will not be able to fetch the historical values for that attributes






2.     Solution 2:- Caching




In this solution a caching layer is added to the architechure to speed up the data access.Custom Data reference needs to be tweaked to read from the memory cache instead of the database.


Microsoft’s APPFabric which is powerful and scalable could be used




1.     If the update mechanism fails , we could end up with stale data




1.     Solution 3:-  PI Server to replicate data




In this solution, The required Realtional data is pulled into a PI server with the PI-OLEDB Interface.Duplication of the data is resulted. But the performance will be super fast




1.     Duplication of data,need to maintain the required pitags and monitor the interface.