Browse Search Login
Home : Configuration - Source Configuration Q12233 - Advanced Settings
Q12638 - HOWTO: Configuration - Source Configuration Q12233 - Advanced Settings

The OSISoft PI Historian offers various ways to retrieve data from its archives. In order to facility these methodolgies, the Hach WIMS Direct Server-Side Interface to OSISoft PI Historian has been modified to handle this. The definitions for the interpolated tables and the non-interpolated table (picomp) are shown below:

As of v1.1.2, we now use the OSI recommended piinterp2 table.  From the PI OLEDB Manual:

"In addition, PI OLEDB allows you to perform calculations on archive data. The "piinterp" table, its newer implementation "piinterp2" (recommended), and aggregate tables in the "piarchive" catalog represent gateways to the calculation results."

There is an advanced setting added to Configuration - Source Configuration, as shown below:

As explained in Configuration - Source Configuration, this allows two types of data retrieval methodology. Interpolated means values will be evenly spaced over time, or time weighted. Non-interpolated will retrieve values as they are. Interpolated will fill in data holes while non-interpolated will not. These methods determine which queries to use in gathering data from the PI historian. In some cases, there is no affect because of the statistic selected in the Hach WIMS client (which is covered in Supported variable configurations for interface Q12233 - Advanced Settings).  

As of version 8.0.4 interpolated support was added to the TIME statistics.  TIMEGT(x).I. The query will use the piinterp2 table and will add a timestep.

The following are queries used for the different methods:

STATISTIC

Non-Interpolated SQL Query

Interpolated SQL Query

AVERAGE

 SELECT avg(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') SELECT avg(value) FROM piarchive..piavg  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')

TOTAL

 SELECT sum(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')

SELECT sum(value) FROM piarchive..pitotal  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') 

MINIMUM

 SELECT min(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') SELECT min(value) FROM piarchive..pimin  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')

MAXIMUM

 SELECT max(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') SELECT max(value) FROM piarchive..pimax  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')

FIRST

 SELECT top 1 time,value FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') order by time asc SELECT TOP 1 time,value FROM piarchive..piinterp2  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')ORDER BY Time ASC

LAST

 SELECT top 1 time,value FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') order by time desc

SELECT TOP 1 time,value FROM piarchive..piinterp2  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')   ORDER BY Time DESC

DIFF
  1. Get FIRST value
  2. Get LAST value
  3. RESULT = LAST - FIRST
  4. IF RESULT < 0 THEN (10 ^ (Ceil(LOG(first_value) / LOG(10)))) - first_value + last_value

No Change, except the query it uses for each part (FIRST and LAST)

RANGE
  1. Get MIN value
  2. Get MAX value
  3. Get ABS(MIN - MAX)

No Change, except the query it uses for each part (MIN and MAX)

INVENTORY

1. SELECT time,value FROM piarchive..piinterp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')  AND Value <> 'Bad' AND timestep = '2m'  ORDER BY Time ASC


2. Loop throught the records returned and sum the downward trends greater than the Deadband

No Change

TIMEGT(x)

 SELECT count(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') and value > 13

No Change unless .I is used on statistic    TIMEGT(x).I

piinterp2 table will be used.  Timestep will be added to the query

TIMEGE(x)

 SELECT count(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') and value >= 13

No Change unless .I is used on statistic    TIMEGE(x).I

piinterp2 table will be used.  Timestep will be added to the query

TIMELT(x)

 SELECT count(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') and value < 13

No Change unless .I is used on statistic    TIMELT(x).I

piinterp2 table will be used.  Timestep will be added to the query

TIMELE(x)

 SELECT count(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') and value <= 13

No Change unless .I is used on statistic    TIMELE(x).I

piinterp2 table will be used.  Timestep will be added to the query

TIMEEQ(x)

 SELECT count(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') and value = 13

No Change unless .I is used on statistic    TIMEEQ(x).I

piinterp2 table will be used.  Timestep will be added to the query

TIMENE(x)

 SELECT count(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') and value <> 13

No Change unless .I is used on statistic    TIMENE(x).I

piinterp2 table will be used.  Timestep will be added to the query

CYCLE(x)

 SELECT count(value) as value FROM piarchive..picomp p WHERE   p.tag =  'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') and value = 0

No Change

COUNT

 SELECT count(value) FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')

No Change

MOST

 SELECT COUNT(*) as thecount, digstring(status) as status FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00')  AND Value <> 'Bad'  GROUP BY status ORDER BY thecount DESC

No Change

MINTIME

 SELECT top 1 time, min(value) as mvalue FROM piarchive..picomp  WHERE Tag = 'SOURCETAG'  AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') group by time order by mvalue asc

No Change

MAXTIME

 SELECT top 1 time, max(value) as mvalue FROM piarchive..picomp  WHERE Tag = 'SOURCETAG' AND time >= date('25-May-2009 00:00:00') AND time < date('26-May-2009 00:00:00') group by time order by mvalue asc

No Change
Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 		 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 11/2/2009 12:02 PM.
Last Modified on 8/19/2022 3:01 PM.
Last Modified by Steve Fifer.
Article has been viewed 7185 times.
Rated 8 out of 10 based on 10 votes.
Print Article
Email Article