Documentation
OpenRDA for Intellution Fix/Dynamics

 
Overview
The historical and real-time data in the FIX DMACS database is exposed through NODE, TAG, TAGVALUE and ARCHIVE tables.  The NODE table exposes a list of nodes accessible from the FIX node where the OpenRDA Server is running.  It is used to get a list of all the nodes that are on the FIX network.  The TAG table exposes the tags, the block types, description and other information about each of the tags.  It is used to look up tags by tag name, block type, description or node.  The TAGVALUE table is used to find out the current field values for all the tags.   The ARCHIVE table is used to access historical data for specified tags. Use of SQL allows these tables to be searched using wildcard or numeric expressions.

You can experiment accessing data from any of the tables by using tools like Microsoft Access and Microsoft Excel or by using the Interactive SQL tool supplied with the OpenRDA ODBC Driver.  Refer to step 6 of chapter 3 for details on accessing the FIX data from Microsoft Excel.  The OpenRDA Server for FIX does not authenticate based on user name or password so you can enter any values you want for the user name and password in the login box.

select * from NODE

Accessing Tag Directory
The tag directory table TAG contains a list of all tags on all nodes accessible from the selected server.  It contains information about each of the NODE:TAG pairs.  The type of information exposed includes the description of the tag, engineering units and the block type.

To get a list of all tags on all nodes that are contained in the database: select * from TAG

To get a list of tags on a specific node: select * from TAG where NODE=’DEMO’

To get a list of tags where the description contains the word 'temperature':

select * from TAG where DESCRIPTION like '%temperature%'

Accessing Tag Values
The real-time table TAGVALUE allows access to current field values for the specified NODE and TAG.  This can be used to read any number of tags and their associated field values.  For each tag, the associated field values are returned as columns.

To access the field values of tag DEMO:RAMP, use the query:

select * from TAGVALUE where node=’DEMO’ and tag=’RAMP’;

Examples:

update tagvalue set ACV='2.0' where tag='HUMIDITY';

You can also use a special feature to update multiple tags with a single query.  This is done using a special INSERT syntax provided by the OpenRDA Server for FIX.  The syntax is as follows:

insert into tagvalue (NODE, TAG, ACV) values ( ('Node1','Tag1','20.0'), …, ('Node1','Tag20','50.0'));

The above statement will update the values of all the specified NODE:TAG pairs as part of the VALUES clause to the specified values.  You must use the exact syntax specified above.  Each SQL statement can at most update 2048 tags and the size of the SQL statement must not exceed 8192.  If you exceed this limit then you will have to break your update up into multiple statements.

Accessing Historical Data
The historical data table ARCHIVE allows access to data collected from the FIX Historical Collect program or from a Lab data file converted to FIX format. Data is accessed by specifying the NODE, TAG and time range. This table is used to get time history for one or more tags and to find out what tags exist in the history files.  If you want to access data from history files that are located in a different location than the path configured by FIX, you can specify value for the DATASET column to either select a pre-configured path or any other path containing the historical data files.  This allows each user to access any history files that are accessible by the OpenRDA Server for FIX. Details on how to set up and access different sets of historical datasets are given in section Specifying the location of the History Files.

Finding out what tags are in the history files
You can find out what all tags are currently being archived by querying this table without any time range.  For example,

select * from ARCHIVE

will print out all points that are currently being archived.  The server assumes default TIME value of one minute before current time, a DURATION of one minute, and an SAMPLE_INTERVAL of one minute.

To find out what points are in the history files at a specified time:

select * from ARCHIVE where NODE=’DEMO’ and TIME = ’1992-09-25 08:00:00'

or

select * from ARCHIVE where NODE=’DEMO’ and TIME = {ts ’1992-09-25 08:00:00'}

select * from ARCHIVE where (NODE=’DEMO’ and TAG=’OILRM1.F_CV’) and TIME between '1992-09-25 08:00:00' and '1992-09-25 12:00:00’;

or

select * from ARCHIVE where (NODE=’DEMO’ and TAG=’OILRM1.F_CV’) and TIME = '1992-09-25 08:00:00' and duration ='4:00:00’;

To access multiple tags from the same node over the same time range:

select * from ARCHIVE where NODE=’DEMO’ and (TAG=’OILPIPE1’ or TAG=’OILPIPE2’) and TIME = '1992-09-25 08:00:00' and duration ='4:00:00’;

select * from ARCHIVE where TAG=’OILLEVEL1’ and duration=’1:00:00:00’ and sample_interval=’1:00:00’ and mode=’HIGH’

This feature allows you to embed these queries in reports and have the reports generated with past specified amount of data each time they are run.

To access 1-hr averages for tag DEMO:OILLEVEL1:

select * from ARCHIVE where TAG=’OILLEVEL1’ and time='1992-09-25 08:00:00' and duration=’1:00:00:00’ and sample_interval=’1:00:00’ and mode=’AVERAGE’

To access the highest valid data point for tag DEMO:OILLEVEL1 during a 24-hr sample_interval and for a duration of 4 days:

select * from ARCHIVE where TAG=’OILLEVEL1’ and time='1992-09-25 08:00:00' and duration=’4:00:00:00’ and sample_interval=’23:59:59’ and mode=’HIGH’

select min(value) from archive where node = 'DEMO' and tag='XYZ' and duration='1:00:00:00';

The following query will print out the minimum, maximum and average over the last 24 hours:

select min(value), max(value), avg(value) from archive where node = 'DEMO' and tag='XYZ' and duration='1:00:00:00';

The following query will report the maximum value and the time at which the value was at its maximum using a nested query feature of SQL.  Multiple times will be returned depending on how long that maximum existed and what you have specified as your sample_interval.

select time, value from archive where node='DEMO' and tag='XYZ' and duration = '1:00:00:00' and sample_interval='1:00:00' and value = (select min(value) from archive where node='DEMO' and tag='XYZ' and duration = '1:00:00:00' and sample_interval='1:00:00' )

SELECT ARCHIVE_1.TIME, ARCHIVE_1.VALUE AS OILLEVEL1, ARCHIVE_2.VALUE AS OILTEMP

From ARCHIVE ARCHIVE_1, ARCHIVE ARCHIVE_2

Where (ARCHIVE_1.NODE=’DEMO’ and ARCHIVE_1.TAG=’OILLEVEL1’ and ARCHIVE_1.TIME = {ts '1992-09-25 08:00:00'} and ARCHIVE_1.duration ='8:00:00’ and sample_interval=’1:00:00’) and (ARCHIVE_2.NODE=’DEMO’ and ARCHIVE_2.TAG=’OILTEMP’) and ARCHIVE_2.TIME= ARCHIVE_1.TIME;

The above query was formulated using the MS Query tool that is bundled with Microsoft Excel for external data access and generated the following result table: 

Interval Literal
Interval literal strings can be used in SQL query to manipulate Date/Time/Timestamp values. Interval literal strings can be used in date-time expression to:

• add an interval to a datetime (or add a datetime to an interval)
  
  
• subtract an interval from a datetime

The interval literal string can be in one of these formats:

{INTERVAL [+|-] interval-string interval-qualifier}

INTERVAL [+|-] interval-string interval-qualifier

The two types of intervals cannot be mixed in expressions. You can only use one of them in any expression.

Interval-string: The interval-string value is specified in quotes in one of the two formats:

• year-month interval: interval in terms of years and an integral number of months
  
  
• day-time interval: interval in terms of days, hours, minutes, seconds.

Each of the fields in the interval-string needs to be separated by specific separator characters. Between year and month is a hyphen separator. Between day and hour is a space separator, between hour and minute is a colon separator, between minute and second is colon separator,  between second and fractional second is period separator. Date-time interval operations are supported only on dates older than Jan 1, 1970. Year-month interval operations are supported on any date.

Interval-qualifier: The interval qualifier specifies the class of the interval (type of interval) and precision of the fields.

Table 4‑5: Example Interval Literals

Example queries of date-time expressions with Interval literals:

To access data for tag DEMO:OILRM1.F_CV for last 24 hours and at a sample interval of 1 hr each.

select * from archive where time between  now() - {INTERVAL '1' DAY} and now() and SAMPLE_INTERVAL = '01:00:00';

Note: The current version does not support Interval Data Type.

Specifying the Location of the History Files
By default, all queries to the ARCHIVE table access the history files pointed to by the FIX path settings.  You can query different history files by either specifying a path or using a pre-configured path on the server. All paths should point to the root HTRDATA path, not an individual node directory. On the server side you can configure both the primary and secondary path for the history files by setting the PRIMARY and SECONDARY settings in the [FIX] section of the OPENRDA.INI file.  This allows any client to request access to these history files by simply setting the value of DATASET to ‘P or 'S'. If paths for PRIMARY and SECONDARY are empty, they will default to the Historical Data path setting in the FIX SCU configuration. For example, the query:

select * from archive where dataset='S'

will list all points being archived in the history files at the location defined by the SECONDARY. 

Alternatively, the client can specify a path in the query.  This allows the greatest flexibility but requires each query to include the path. For example, the query:

select * from archive where dataset='//mynode/history'

will list all points being archived in the history files at the location //mynode/history.

To set up the server for primary and secondary historical data paths:

1.      Make sure the OpenRDA Sever for FIX is not running.  If it is, stop it by closing the Window.

2.      Open the OPENRDA.INI file by running the OpenRDA Config File from the OpenRDA ODBC for FIX program group.  This will open OPENRDA.INI for editing.

3.      Go to the section [FIX]

4.      Under this section, set the settings for:

PRIMARY = UNC path

SECONDARY = UNC path

where path is location of the history files ( i.e. PRIMARY = C:\FIX\HDT). Note that these paths can refer to location on your file server or any other NODE in the FIX environment.

5.      Save the file

6.      Start OpenRDA Server from the OpenRDA ODBC for FIX program group

7.      From the client you can now execute queries where the DATASET contains the value "S".

Accessing lab data
FIX stores lab data in files with L24 extension.  These may be in the same directories as the HXX files or in separate directories.  If they are in separate directories then the node name that is returned is the sub-directory they are in.  For example, if you have a node DEMO, then you will have a DEMO directory under the HTRDATA directory.  This directory will contain historical data files with H04, H08 or H24 extensions.  If lab data is placed in this directory, then accessing the ARCHIVE table with NODE='DEMO' will return data from both lab data files and the history data files.  In some installations the lab data files are in separate directories under the HTRDATA directory.  If one of these directories is called LABDATA, then the ARCHIVE table will report the node name as LABDATA.  You can retrieve just the lab data by specifying NODE = 'LABDATA' in the query.

Accessing Alarms
The FIX historian saves the alarm status along with the tag value.  You can access the ALARM status by retrieving the ALARM column from the ARCHIVE table.  This allows you to easily monitor the alarm by archiving that value.  For example, you can obtain all alarms over the last 24 hours by using the following query:

select * from archive where duration='1:00:00:00' and ALARM is not like 'AS_OK%' and alarm is not like 'AS_DSAB%' and sample_interval='30:00';

Table xx below lists all the different values that are returned for the ALARM value.  You can use the LIKE feature of SQL to not have to type in the exact string.

Table 4‑6 : Alarm Values