How to create a Custom Data reference in PI AF for access to historical data

• Download My_Table_Lookup-noexe.zip - 8.6 KB
• Download My_Table_Lookup.zip - 3.4 MB

Introduction

The reason behind to write this article was scarcity of information about custom data reference on web and limited access to PI resources, so that others who are trying to implement something similar don’t have to go through the head scratching sessions. Though there is a lot of stuff available on VCampus but my mere intention was to ease out things by integrating all the information at a single place.

Why on earth do we need to connect to a relational database when we have a real time management system?  

Let’s consider the following scenario of an Oil and Gas industry. Suppose there are ten wells and we need to check the outlet flow. The company can’t place a Flow meter with each well as it would cost them a lot, so they decide to place a Flow meter between 10 wells and the wells are connected to it via pipelines, with a valve attached in each. Now say we need to check the outlet flow of the Well 1, we close the valves of all the other wells, and as Well 1 is already connected to the Flow meter, we can easily get the outlet Flow value.

Now we can infer two things from the above scenario, that it would add to the cost, if we place a Flow meter with each Well and it is pointless to archive this data in PI when we don’t even want to monitor it on a daily basis. Say there are 100 Wells and we only check the outlet flow of the Well 1 once in two weeks only, so there is no point maintaining this data in PI, since it would completely defy its main purpose of real time data management. In such cases it’s wise to maintain data in some relational database.

The question here arises is, if the data is fragmented i.e. some in PI System and some of it in relational DB’s, so how is PI Managing the data? PI provides the facility to show the data from the PI Server as well as the relational database. It displays the data from relational database in the form of Tables which could be customized according to the user’s needs. Tables could be found under the Library section of PI System.

(Fig 1.1) 

Now the next big question is, why do we need a custom data reference when PI already provides ways to refer to the relational databases.

Usually PI AF fetches historical data from PI tags referenced as PI Point in AF, data can also be retrieved from other data sources like relational data from Oracle, SQL, etc., which is brought to AF using library tables. AF doesn’t provides an option for the retrieval of historical data within a specific Time Range from a relational database, it only provides the current value of the attribute when referenced through AF TableLookup Data Reference. But what If, we need historical data of a specific date time range for analysis, trending purpose in the application. Therefore we are going to make this My_Table_Lookup custom data reference in AF to get the Historical Data from relational database (e.g. SQL) within a specific time range.

Basics  

I assume that the user reading this article is familiar with the PI System Explorer, still let’s have a quick overview of the AF terminology before jumping to the implementation part. 

PI System: An OSIsoft product for management of real time data and events.

PI AF: PI Asset Framework allows the representation of data in reference to the physical assets like equipment, devices that exists within the plant. It displays data in hierarchical and structured form as Elements and attributes.

Elements: Elements generally represents the physical objects like Equipment, Wells etc. within the plant.

Attributes: Attributes are used to store information about the elements such as the Flow rate, temperature etc. 

Data Reference:  Data Reference is one which allows getting attribute value from a data source that could be a PI Point (from PI Server), a relational database .PI AF also provides

PI SDK: OSIsoft has provided OSIsoft has provided PI Software Development Kit, a Microsoft .NET assembly to access the OSIsoft data or simply a programming library to access the PI Servers. 

Using the code 

As we need to fetch data from alternate sources, the custom data reference (My_Table_Lookup) will act as a lookup, similar to the Table lookup, to retrieve data from relational database for a specific time range. My_Table_Lookup will be configured using the AF Data Reference. It inherits from the AF Data Reference object and uses the standard functions available. This custom data reference can be registered using the command line which we will talk about later. Once registered it could be viewed in the data reference list in the AF Client (Fig 1.3). My_Table_Lookup will then be associated with an AF Table that will provide the required data from the database. After the association we will be using the same dialog box as that of the Table Lookup Data Reference to allow the user to configure the attribute value. It make dynamic query to the relational database and pulls the data. Here the GetValues function gets overridden to return the historical values from the AF Table. 

You need PI SDK (v.1.3.8 or later) which is based on Microsoft’s Component Object Model and AF Client 2010 R2, for building the custom data reference. Once you install the PI System, PI SDK is installed with all the necessary libraries and services required to access the data stored within the PI historian. 

This could be better understood by the following figure. 

(Fig 1.2) 

The basic idea is to override the existing GetValue and GetValues functions of the data reference and add required implementation to fetch the current/historical value(s) as required. 

GetValue 

By default, this method returns the current value of the attribute and since we need value at a particular timestamp, we will override this function to get the required result.

If the value is not available at requested timestamp, we might need to return the value at the closest timestamp by interpolating the results. 

GetValue(object context, object timeContext, AFAttributeList inputAttributes, AFValues inputValues) 

First of all we are going to override the GetValue method

public override AFValue GetValue(object context, object timeContext, 
           AFAttributeList inputAttributes, AFValues inputValues)
{
   .
   .
   // Code
   .
   .   
}  

Now the next thing is to get the Filter, here Filter in base.Filter, is the query which we get after configuring a particular attribute

Filter could look like "SELECT ProducedOil FROM [WellDailyData] WHERE ProducedOil = @[Production Rate]".

string filter = base.Filter.Replace("[", ""); 
filter = filter.Replace("]", "");

We need to retrieve the attribute name from the filter by parsing the filter 

index = filter.IndexOf("@");
if (index > 0)
 {
   for (int i = index + 1; (i < filter.Length); i++)
    {
      if ((filter[i].ToString() != " "))
       {
         attributeName += filter[i].ToString();
       }
       else
        break;
    }
........
///code
........
} 

Create an object of AFAttribute, as we have already retrieved the attribute name. We get the attribute object by the GetAttribute method

AFAttribute attribute; = base.GetAttribute(attributeName);
string val = attribute.GetValue();
string temp = "@" + attributeName;         
string value = "'" + val.ToString() + "'"; 
filter = filter.Replace(temp, value);
filterInterpolation= filter.Replace(temp, value); // for later reference  

Now the crux of the article comes here. Since we only get the Current/Latest value, we need to modify and retrieve value at a particular TimeStamp

So we are going to tweak the Filter (Query), in order to get the value at a requested TimeStamp. 

if (filter != null && timeContext != null)
{
  filter = filter.Trim();
  filter = filter + " AND START_DATETIME ='" + timeContext.ToString() + "'";
}
else if (filter == null && timeContext != null) // This is in case when there is no filter
{
  filter = "START_DATETIME ='" + timeContext.ToString() + "'";
} 

As we have got the required filter with the Timestamp now we need to get the database and the Tables which the Lookup is referring to and query the table

AFDatabase afDB = base.Database;
AFTable t = afDB.Tables[base.Table];
DataRow[] foundRows = t.Table.Select(filter); 

Returning the last value i.e. is the last value of foundRows (as it is in ascending order)

Field: The Field which holds the value to be returned 

AFValue item = item = new AFValue(foundRows[foundRows.Length - 1][base.Field], 
       new AFTime(Convert.ToDateTime(foundRows[foundRows.Length - 1]["START_DATETIME"]))); 

Now the question arises what if there is no matching value in the AF table at the requested timestamp. In such cases we need to interpolate the result.

Interpolation is creating new data points within the range of known data points, i.e. we need to the get the value at the just before and next timestamp to the requested Timestamp.

And we'll use the most basic way of interpolating by finding the slope between the two values, where Timestamp will represent the Y-Axis and the values the X-Axis

Interpolation 

First of all retrieve all the rows fulfilling the filter condition in ascending order of DateTime

   retrievedRows = t.Table.Select(filterInterpolation, "START_DATETIME ASC");
// Or in case there is no filter
   retrievedRows = t.Table.Select();  

Now finding the values at the respective Timestamps

for (int i = 0; i < retrievedRows.Length; i++)
{
  if ((DateTime)retrievedRows[i]["START_DATETIME"] > ((AFTime)timeContext).UtcTime)
  {
    t2 = (DateTime)retrievedRows[i]["START_DATETIME"];
    valueAttribute2 = retrievedRows[i][base.Field].ToString();
    break;
  }
  else if ((DateTime)retrievedRows[i]["START_DATETIME"] < ((AFTime)timeContext).UtcTime)
  {
    t1 = (DateTime)retrievedRows[i]["START_DATETIME"];
    valueAttribute1 = retrievedRows[i][base.Field].ToString();
  }
 }

Finding the slope between the two points i.e. between valueAttribute2 and valueAttribute1

if (valueAttribute1 != "" && valueAttribute2 != "")
{
 slope = ((Convert.ToDouble(valueAttribute2)) - 
        (Convert.ToDouble(valueAttribute1))) / ((t2 - t1).TotalHours);
 difference = ((AFTime)timeContext - t2).TotalHours;
 interpolatedValue = (Convert.ToDouble(valueAttribute2)) + (difference * slope);
 item = new AFValue(interpolatedValue, new AFTime ((AFTime)timeContext).UtcTime);
}  

We should also handle the boundary conditions when record associated to the requested TimeStamp is the first record in the table or the last record in the table. In this case we do not get the two points to find the slope as it is the first or the last record. 

// for the first record which is not available
else if (valueAttribute1 == "" && valueAttribute2 != "")
{
 interpolatedValue = 0;
 interpolatedValue = (Convert.ToDouble(valueAttribute2));
 item = new AFValue(interpolatedValue, new AFTime((AFTime)timeContext).UtcTime);
 }
//For latest record, if there is no value              
else if (valueAttribute2 == "" && valueAttribute1 != "")
{
 interpolatedValue = 0;
 interpolatedValue = (Convert.ToDouble(valueAttribute1)); 
 item = new AFValue(interpolatedValue, new AFTime((AFTime)timeContext).UtcTime);
} 

Finally we return the desired AFValue

return item; 

GetValues 

This method retrieves the historical values of attributes and returns all the values within a time range i.e. start time and end time. It also interpolate the results according to the number of values requested

GetValues(object context, AFTimeRange timeContext, int numberOfValues, AFAttributeList inputAttributes, AFValues[] inputValues)

As done in GetValue method, similarly we are going to override the GetValues method, the get the Filter and find the attribute name.

If the numberOfValues is less than 0 then we need to interpolate the result and return values according to the numberOfValues otherwise we are going to return all the values within the TimeRange 

public override AFValues GetValues(object context, 
       AFTimeRange timeContext, int numberOfValues, 
       AFAttributeList inputAttributes, AFValues[] inputValues)
{
	.
	.
	// Code
	.
	.   
}

Once we get the attribute name we'll find the startTime and endTime, i.e., the TimeRange within which we need to retrieve values 

startTime = timeContext.StartTime.ToString();
endTime = timeContext.EndTime.ToString();
attribute = base.GetAttribute(attributeName);
val = attribute.GetValue();
temp = "@" + attributeName;
value = "'" + val.ToString() + "'";
filter = filter.Replace(temp, value);
tempfilter = filter.Replace(temp, value);
// as filter would modified therefore we need to maintain it in another variable 

Now we'll customize the filter

if (filter != null)
{
  filter = filter.Trim();
  filter = filter + " AND START_DATETIME >='" + startTime + 
      "' AND START_DATETIME <='" + endTime + "'";
}
else
{
  filter = "START_DATETIME >='" + startTime + "' AND START_DATETIME <='" + endTime + "'";
} 

We are going to check if numberOfValues is negative or not. If numberOfValues is less than 0 then we will interpolate the result. 

DataRow[] retrievedRows;
retrievedRows = t.Table.Select(filter);
if (numberOfValues < 0)
{
  for (int i = 0; i < retrievedRows.Length; i++)
  {
      item = new AFValue(retrievedRows[i][base.Field], 
             new AFTime(Convert.ToDateTime(retrievedRows[i]["START_DATETIME"])));
afValues.Insert(i, item);     // item is the AFValue
  }
}
else
{
//Interpolate
} 

Interpolation 

First we'll interpolate the Timestamps and then the values.

Initialize a Timestamp array against the numberOfValues and find the time difference between the startTime and endTime so that we can create Timestamps within the TimeRange. The interpolated time would be the time difference divided by the number of values requested. 

numberOfValues = numberOfValues * (-1);
DateTime[] Timestamps = new DateTime[numberOfValues];
Timestamps[0] = Convert.ToDateTime(startTime);
Timestamps[numberOfValues - 1] = Convert.ToDateTime(startTime);
timeDifference = (Convert.ToDateTime(startTime) - Convert.ToDateTime(endTime)).TotalSeconds;
if (timeDifference < 0)         //if negative 
timeDifference = timeDifference * (-1);
interpolatedTime = (timeDifference / (numberOfValues - 1));  //numberOfValues-1 as starttime and endtime are included
for (int i = 1; i < (numberOfValues - 1); i++)
{
  Timestamps[i] = Timestamps[i - 1].AddSeconds(interpolatedTime);
} 

Now we'll interpolate the values by first checking if the values are available for the interpolated Timestamps in the retrieved records and if not then interpolating the values

We find the results with the Timecontext(with start time and end time) 

DataRow[] retrievedRows;
retrievedRows = t.Table.Select(filter, "START_DATETIME ASC");

tempFilter is the filter i.e. without the timecontext(all the records in the ascending order od DateTime)

We can get exact values from retrievedRows but we'll find interpolated values using foundRows 

DataRow[] foundRows;
foundRows = t.Table.Select(tempfilter, "START_DATETIME ASC");

First we'll check if the interpolated Timestamp matches the Timestamp of the retrieved row, if it matches then we get the first exact value otherwise we need to interpolate the value

if (Convert.ToDateTime(retrievedRows[_index]["START_DATETIME"]) == Timestamps[j])
 {
  // Code to get the record from the retrieved rows and insert it to the AFValues list 
 } 

In other case when the Timestamp doesn't match the interpolated Timestamp, we first compare if the Timestamp of the retrieved record is less than the Interpolated Timestamp, if so then we again check whether it is less than timestamp of the foundRow record, we get the first time and its value, otherwise we get the second time and value for interpolation. 

if ((DateTime)foundRows[i]["START_DATETIME"] > Convert.ToDateTime(Convert.ToDateTime(Timestamps[j])))
{
  t2 = (DateTime)foundRows[i]["START_DATETIME"];
  valueAttribute2 = foundRows[i][base.Field].ToString();
  break;
}
else if ((DateTime)foundRows[i]["START_DATETIME"] < Convert.ToDateTime(Convert.ToDateTime(Timestamps[j])))
{
  t1 = (DateTime)foundRows[i]["START_DATETIME"];
  valueAttribute1 = foundRows[i][base.Field].ToString();
} 

Find the slope and get the interpolated value 

slope = ((Convert.ToDouble(valueAttribute2)) - (Convert.ToDouble(valueAttribute1))) / ((t2 - t1).TotalHours);
difference = ((AFTime)Timestamps[j] - t2).TotalHours;
interpolatedValue = (Convert.ToDouble(valueAttribute2)) + ((difference * slope));
item = new AFValue(interpolatedValue, new AFTime((AFTime)Timestamps[j]));
afValues.Insert(j, item); 

Repeat the above steps for all the retrieved records and interpolated timestamps and return the all the AFValues

return afValues;

How to register and unregister the Custom Data Reference 

We need to register our data reference in order to use it.

Prerequisite

Place the OSIsoft.AF.Asset.DataReference.TableLookup.dll and My_Table_Lookup.dll at the following location “C:\Program Files (x86)\PIPC\AF” 

For Registering

Go to Command prompt and type cd C:\Program Files (x86)\PIPC\AF\

Type the following command

regplugin "C:\Program Files (x86)\PIPC\AF\My_Table_Lookup.dll" /owner:"C:\Program Files (x86)\PIPC\AF\OSIsoft.AF.Asset.DataReference.TableLookup.dll"

If you are not able to register the data reference then give the following command to register it forcefully.

regplugin "C:\Program Files (x86)\PIPC\AF\My_Table_Lookup.dll" /owner:"C:\Program Files (x86)\PIPC\AF\OSIsoft.AF.Asset.DataReference.TableLookup.dll" /F

RegPlugIn.exe and is located in the following folder: \PIPC\AF 

For Unregistering

Go to Command Prompt and type cd C:\Program Files (x86)\PIPC\AF\

Type the following command 

regplugin /u /Files:C:\ProgramData\OSIsoft\AF\Custom_Table_Lookup.dll

NOTE: Do close the PI AF Client and PI Server if installed, before registering and unregistering.

After registering we would be able to see it in the AF Client as shown below.

(Fig 1.3) 

Conclusion

The custom data reference could be used in various applications like Processbook and Datalink, for a graphical representation, for trending purpose etc., to retrieve the historical values from the relational databases. This was one type of data reference which was specifically built to cater a particular requirement. Anyone can create a data reference by inheriting the Table Lookup and customize it according to their needs.