Relational Database(rdbms via odbc) Interface

Configuration Diagram

In the following figures there is the basic configuration of the hardware and software components in a typical scenario used with the RDBMSPI Interface.

Configuration Diagram – PI Home Node with PI Interface Node and RDBMS Node



Configuration Diagram – All PI Software and RDBMS Installed on one Node



Note: The communication between the RDBMPI interface and a PI Server is established via PI API as well as PI SDK libraries. PI SDK is used for replication of the PI Batch Database and for reading from and writing to PI Annotations. PI API is primarily used for the actual data transfer to and from PI Data Archive.

The communication between the RDBMSPI interface and the relational database goes through the ODBC library. The interface can thus connect a relational database, which runs either on an interface node or can be remote. This remote node does not have to be Windows platform.

2. Principles of Operation

The PI Relational Database Interface runs on Windows operating systems as a console application or as a Windows NT service. As already stated, it uses the extended PI API and PI SDK to connect to the PI Server node and the specified ODBC driver for connection to the Relational DataBase (RDB). For the ODBC connection, the Data Source Name (DSN) must be created via the ODBC Administrator (the Data Sources ODBC icon in Windows Control Panel). This DSN name is then passed within the start-up parameters of the interface; example: /DSN=Oracle8.

SQL Server queries are provided by the user in the form of either ASCII files, or via direct definition in the PI point's Extended Descriptor. Queries are executed according to the scan class type (cyclic or event driven) of a PI point holding the query definition.

In the direction from a relational database to PI, the appropriate SELECT must be specified and the interface converts the result-set into the PI concept of: [timestamp], value, status, [annotation]. See section Concept of Data Input from Relational Database to PI.

The opposite direction – writing data out of the PI system (to RDB) uses the concept of run-time placeholders. See section Concept of Data Output from PI to Relational Database.

General Features Supported by the Current Version

• 
  Query Timestamp, Value, Status and Annotation in RDB Tables
  
• 
  Scan or Event based (input)
  

• 
  SELECT queries or Stored Procedures calls
  
• 
  Query data (input) for: Single tags, Multiple tags (Tag Group), Multiple tags via TagName Key (Tag Distribution and RxC Strategy).
  

• 
  Event or Scan based (output): INSERT, UPDATE and DELETE statements and Stored Procedures
  
• 
  Support of multiple statements – multiple SQL statements per PI tag
  
• 
  Statements can be one single transaction (/TRANSACT keyword)
  
• 
  Support of runtime placeholders: Timestamp (Scan Time, Snapshot Time,…), Value, Status and Annotation, including the Foreign Tags – tags outside the interface point source (‘tagname’/VL)
  
• 
  Support of all PI point attribute (classic point class) placeholders (AT.x)
  
• 
  Support of batch placeholders for PI Batch replication (BA.x)
  
• 
  Support for new batch system (batches and unit batches)
  
• 
  Recording the PI point attribute changes into RDB
  
• 
  History recovery for input and output points
  
• 
  Millisecond and sub-millisecond timestamp resolution
  
• 
  Support for different Timezone/DST settings than PI Server
  
• 
  RDB timestamps as well as timestamps taken from PI (through placeholders) can optionally be in UTC (/UTC start-up parameter)
  
• 
  And many others.
  

The two sections that follow briefly explain how the data is transferred from RDB to and from PI. More detailed description of SQL Server statements, retrieval strategies, hints to individual RDBs are discussed in section SQL Statements.

Concept of Data Input from Relational Database to PI

The SELECT query is generally expected to provide a result-set consisting of the following columns: [timestamp], value, status, [annotation]. The interface then internally transforms the result-set according to the specified distribution strategy. For more information, see chapter Inputs to PI via SELECT Clause – Detailed Description. The following paragraphs briefly describe the individual strategies that can be used for getting data from an ODBC compliant database to PI.

Query for Single Tag – One Value per Scan

There are Distributed Control Systems (DCS) that keep only current values in relational database tables. Via the scan-based, simple SELECT queries, the interface can read the data in the timely manner and emulate the behavior of a standard DCS interface. An example is getting data from an ABB IMS station where the SELECT is expected to return only one row, which the interface forwards to the PI Snapshot. The disadvantage of this kind of data retrieval is low performance and accuracy that is limited to the scan frequency.

See an example in Appendix C: Examples Example 1.1 – single tag query.

Query for Single Tag – Multiple Values per Scan

A good strategy for high data throughput is to have low scan rates (e.g. 1+ minute) instead of doing one query every second. In other words, getting the same amount of data in one call is faster than getting it in many calls. This approach assumes that RDB tables get populated by INSERT (not UPDATE) statements and there is the timestamp column, which allows rows can be ordered by this timestamp. The task of the interface then is to read just the newly inserted rows since the last scan.

Note: A typical low throughput query is:
SELECT Timestamp, Value, Status FROM Table WHERE Name= ?;
Extended Descriptor: P1=AT.TAG
Location2: 0
It is expected that the interface only takes one row. That is, the interface works similarly as an online DCS interface; cyclically reads one row from a table.
The higher performing query is like:
SELECT Timestamp, Value, Status FROM Table WHERE Timestamp > ? ORDER BY Timestamp;
Extended Descriptor: P1=TS
Location2: 1
The interface gets a succession of rows; however it only gets the new ones since the last scan. This is achieved by asking for rows bigger than the question-mark. Because the result-set is ORDERed the interface can utilize the PI exception reporting.

Note: Supported SQL syntax and parameter description (Pn) is given later in the manual.

See an example in Appendix C: Examples Example 1.2 – query data array for a single tag.

The section SQL SELECT Statement for Single PI Tag that has more details.

Tag Groups

Another way of improving performance (compared to reading value(s) for a single tag) is grouping tags together. The RDB table should be structured in a way that multiple values are stored in the same record (in more columns); for instance, transferring LABoratory data, where one data sample is stored in the same row. Only one timestamp is allowed in a result-set, which is then used for time-stamping of all tags in such a group.

The result set for Tag Groups has the following form:

[Timestamp],Value1,Status1,[Annotation1],Value2,Status2,..

Note: The group is created out of points that have the same Instrument Tag attribute; that is, the group member tags share the same ASCII SQL file and are in one scan class (same Location4).

For a more detailed description see section SQL SELECT Statement for Tag Groups.

See an example in Appendix C: Examples Example 1.3 – three PI points forming a GROUP.

Tag Distribution

Compared to Tag Groups, where grouping happens in the form of multiple value, status columns in a result-set; Tag Distribution means multiple records per query. Each record (row) can contain data for a different tag. To achieve this, an additional field must be provided – a field that contains the tag name (or an alias) telling the interface to which target point a particular row should be distributed. Target points are searched either according to their tag name (value retrieved in the PI_TAGNAME column should match the TagName of the point), or according to the /ALIAS=alias_key keyword, defined in the Extended Descriptor (of the given target point).

The result set for Tag Distribution should thus have the following form:

[Timestamp],TagName,Value,Status,[Annotation]

Note: For administration purposes, the Distributor Tag, which defines the actual SQL Server statement, does not receive any actual data from the result set. Instead, it gets information about how many events have been SELECTed and how many events have been successfully delivered to target tags. For more information about the distribution strategies, see these sections:
SQL SELECT Statement for Tag Distribution
SQL SELECT Statement for RxC Distribution
Detailed Description of Information the Distributor Tags Store.

Note: Similar to the group strategy, the target points have to be in the same scan class (as the DistributorTag) but mustn’t have any SQL Query defined; that means InstrumentTag must be empty as well as there can’t be any /SQL=statement definition in their ExtendedDescriptor.
When the target points are referenced through the /ALIAS keyword, they do not have to be in the same scan class (as the DistributorTag).

See an example in Appendix C: Examples Example 1.4 – Tag Distribution.

RxC Distribution (combination of Group and Distribution)

Some laboratory data in RDB tables have a common structure that looks like the following. Note that the columns below are meant to compose one row.

SAMPLETIME,TANK_NAME,TANK_LEVEL,TANK_LEVEL_STATUS,

TEMPERATURE_NAME,TEMPERATURE_VALUE,TEMPERATURE_STATUS,
DENSITY_NAME, DENSITY_VALUE, DENSITY_STATUS, …

To transform this kind of result-set to PI tags the interface implements a strategy that accepts data being structured as follows:

[PI_TIMESTAMP],PI_TAGNAME1,PI_VALUE1,[PI_STATUS1], PI_TAGNAME2, PI_VALUE2, [PI_STATUS2],… PI_TAGNAMEn, PI_VALUEn, [PI_STATUSn],…

In case there is a timestamp column for every name/value/status:

[PI_TIMESTAMP1], PI_TAGNAME1,PI_VALUE1,[PI_STATUS1], [PI_TIMESTAMP2], PI_TAGNAME2,PI_VALUE2,[PI_STATUS2], … [PI_TIMESTAMPn], PI_TAGNAMEn, PI_VALUEn, [PI_STATUSn], …

Note: For administration purposes, the Distributor Tag, which defines the SQL statement, does not receive any actual data from the result set. Instead, it gets information about how many events have been SELECTed and how many events has been successfully delivered to target tags. For more information about the distribution strategies, see sections:
SQL SELECT Statement for Tag Distribution
SQL SELECT Statement for RxC Distribution
Detailed Description of Information the Distributor Tags Store.

Note: Similar to the group strategy, the target points have to be in the same scan class (as the DistributorTag) and mustn’t have any SQL Query defined; that means InstrumentTag is empty as well as there can’t be and /SQL=statement definition in their ExtendedDescriptor.

See an example in Appendix C: Examples Example 1.5 – RxC Distribution.