TN HistClient108 Using Integral Retrieval with Historian Client Query
Description
How to bring back totals based on Integral Retrieval and a variable flow rate.
- Author: Glenn Yancey
- Published: 08/31/2015
- Applies to: Historian Client 2012 and higher
Details
Integral retrieval calculates the values at retrieval cycle boundaries by integrating the graph described by the points stored for the tag. Therefore, it works much like average retrieval, but it additionally applies a scaling factor. This retrieval mode is useful for calculating volume for a particular tag. If you were measuring how many units of juice were produced based on a flow rate of gallons per minute (gal/min or GPM), then using Integral Retrieval one can actually see the total gallons produced during a specific period of time.
Figure 1: Flow Meter representing Liters per Minute
Integral retrieval is a true cyclic mode. It returns one row for each tag in the query for each cycle.
The number of cycles is based on the specified resolution or cycle count. Integral retrieval only works with analog tags. For all other tags, normal cyclic results are returned.
Calculating values for a cycle in integral retrieval is a two-step process:
- The historian calculates the area under the graph created by the data points.
- After this area has been found, it is scaled using the value of the IntegralDivisor column in the EngineeringUnit table. This divisor expresses the conversion factor from the actual rate to one of units per second.
For example, if the time-weighted average for a tag during a 1-minute cycle is 3.5 liters per second, integral retrieval returns a value of 210 for that cycle (3.5 liters per second multiplied by 60 seconds).
Gaps introduced by NULL values are not included in the integral calculations. The average only considers the time ranges with good values.
Flow Rates setpoints may be set for a desired rate of flow, but at times that Flow Rate measured by the FlowMeter could vary due to a plethora of circumstances. It is due to these variances that Integral retrieval is important to use because it keeps quality in mind of the values that were are storing, and can give us an accurate indication of what we had produced.
Example
Tag Creation
In this example below, I had created a tag below called VariableFlowRate. I created a Memory Real tag so that I can use this to adjust my flowrate to simulate actual flow by hand using a slider as I don’t have a PLC (IO Tag) in this example. In the real world, I would have an IO Tag (Real) that I would use to measure my flow. It would be that tag that I would store in the Wonderware Historian. Note the Eng Units (Engineering Units) that I have defined for this tag. This tag has been given an engineering unit of “Gal/Min” for Gallons per Minute.
Verifying the Engineering Unit
Before I import my tag into the Historian, it is VERY important to ensure that the Unit of Measurement for your tag exists in the Historian Runtime Database. If not, it will be imported and added into the Runtime DB during the InTouch Tag Database import. To verify the Engineering Unit in the DB (database), you will need to open up the SQL Server Management Studio. Then, expand through the database called Runtime, then Tables, then dbo.EngineeringUnit. Right click on the table to “Edit Top 200 Rows”. There is a possibility that we might have to modify an entry after import if we chose an Engineering Unit that doesn’t exist or doesn’t have a properly defined IntegralDivisor (measured in Seconds).
Why is an Integral Divisor important? Well, if we are measuring a flow in Gallons per Minute, we need to make sure that our IntegralDivisor represents the amount of seconds in a minute, which is 60. The same would be true for Gallons per Hour (3600 seconds), Gallons per Day (86400 seconds), etc.. If the Engineering Unit in question (Ex. GPM) only shows 1, it could be that the EngineeringUnit was imported in, but the Historian wasn’t sure how to calculate it for Integral Retrieval. A modification may be required to perhaps change the IntegralDivisor from 1 to 60.
Importing the Tag(s) into the Historian
As I imported my InTouch tag(s) into the Historian, I chose to store this value using Cyclic Storage (1 second interval). This way, I know that I should expect a good value every second. With Delta storage, there could be a long period of time where I had no value change, which means that a values hasn’t been stored for many minutes. This in turn could affect the values that I use for Integral retrieval.
There is always the chance for bad quality due to network issues, change in pressure, or faulty equipment. Regardless, the Historian still expects a value to be stored every second which from a quality perspective, that is good.
Retrieval
In order to use Integral Retrieval, I am using the Historian Client Query rather than Trend.
My Query type is set to History Values, and I chose the tag in question that I would like to perform Integral Retrieval against. In my example below, I am selecting 1 “Value over equal time interval”. This is also known as the wwCycleCount. I’m doing this because I only want 1 value returned over a given time frame to see how many gallons were produced based on my flow rate. The Time that I have chosen will reflect 5 minutes of time.
Under Retrieval in the Other Tab, I’m choosing Optimistic quality under “Values to Include in Calculations”. The Optimistic setting for the quality rule lets you retrieve information that is possibly incomplete but may nevertheless provide better results in the counter and integral retrieval modes where the calculation cycle contains data gaps. This setting calculates using the last known good value prior to the gap (if possible). The logic for determining the quality of the points returned remains unchanged in both retrieval modes.
The integral retrieval mode is an exception to this where the integral is scaled up to cover gaps.
Query
SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 21), [VariableFlowRate]
FROM WideHistory
WHERE wwRetrievalMode = 'Integral'
AND wwCycleCount = 1
AND wwQualityRule = 'Optimistic'
AND wwVersion = 'Latest'
AND DateTime >= '20150831 15:33:00.000'
AND DateTime <= '20150831 15:38:00.000'")
ResultSet
During the past 5 minutes, I produced roughly 139 gallons based on a variable flow rate.