arm77

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

Thanks

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.

 

Image1.jpg

 

Image2.jpg

 

 

 

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

 

Image3.jpg

 

 

 

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.

 

Disadvantage:-

 

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

 

Image4.jpg

 

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

 

Disadvantage:-

 

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

 

 

 

1.     Solution 3:-  PI Server to replicate data

 

Image5.jpg

 

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

 

Disadvantage:-

 

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

 

 

 

 

 

 

Outcomes