<< Click to Display Table of Contents >> Navigation:  Live > Concepts > OSIsoft PI Integration

This article explains how to configure export of real time values from OSIsoft PI into EQuIS Live database.

 

To configure export of real time data to EQuIS Live, OSIsoft PI RDBMS interface needs to be installed. Please refer to PI user manual for the steps necessary to accomplish this task. Once installed, the interface allows transfer of data between the PI System and any Relational Database Management System (RDBMS) that supports Open DataBase Connectivity (ODBC) drivers. The interface runs on Microsoft Windows operating systems, and is able to connect to any PI Server node available on the network. Please refer to the following YouTube video explaining how to install and configure the interface.

 

The approach requires the presence of LiveInsert stored procedure in the destination EQuIS database. The procedure will be called by PI RDBMS interface whenever PI TAG value changes. Currently, a new export TAG needs to be created for each existing TAG we want to export data for. This new export TAG will be linked with the existing TAG, as described in the steps below. The new export Tag contains the information necessary to export data from the original TAG into the EQuIS database.

 

Configure Real Time Export of TAG Values

 

1.Install PI RDBMS Interface.

2.Configure Connection to EQuIS database using PI Interface Configuration Utility, make note of setting: General - Point Source value.

a.In the rdbodbc – Optional Parameters tab – Select Times are UTC – to export data with UTC timestamps and not local server time of where the PI RDBMS interface is installed.

3.Start the interface.

4.Create/Adjust Stored Procedure LiveInsert in EQuIS database.

5.Create new TAG, configure PI Points via Point Builder:

General tab - Point Type - must be numerical type (float32, int, etc.)

General tab - Point Source - as configured in Step 2

General tab - Exdesc: /SQL="{CALL LiveInsert(?,?,?,?)};"P1=TAG P2=TS P3=VL P4=AT.SOURCETAG

General tab - Source Tag: Name of PI TAG to export values to Live

Classic tab - Location1: 1

6.Save the TAG.

 

Please ensure the account configured in PI RDBMS interface has INSERT permissions to DT_LOGGER, DT_LOGGER_SERIES and DT_LOGGER_DATUM tables as well as EXECUTE permission to the LiveInsert stored Procedure.

 

The Stored Procedure (called LiveInsert) used to accept incoming data from PI needs to have following input parameters:

   @tag varchar(1024) NULL, -- name of the export / new tag

   @ts datetime NULL, -- UTC-0 datetime of exported value

   @value varchar(80) NULL, -- exported value

   @tag_source varchar(1024) NULL -- name of the original tag for which the value is exported

 

Its logic needs to be adjusted on a per client basis. The most complex part is matching of the TAG to an EQuIS Live logger/series row, and optionally to facility/location.

 

In some cases, direct TAG name to series-name can be used. In other cases, more advanced matching logic needs to be implemented (as parsing of facility/location codes embedded in the TAG name).

 

Please contact support@earthsoft.com for assistance with Stored Procedure creation.

 

The following example assumes TAG name equals series name, one logger contains all TAG related series, with one series per TAG.

 
/****** Object:  StoredProcedure [dbo].[LiveInsert]  ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[LiveInsert]

   @tag varchar(1024) = NULL,

   @ts datetime = NULL,

   @value varchar(80) = NULL,

   @tag_source varchar(1024) = NULL

AS

BEGIN TRY

 

  -- prevent error: [ODBC SQL Server Driver] Connection is busy with results for another hstmt

  SET NOCOUNT ON;

 

  -- get logger_id for Pi-RDBMS insert

  declare @logger_id int;

  set @logger_id = (select top 1 logger_id from dt_logger where live_data_source = 'Pi-RDBMS' and status_flag = 'A');

 

  -- no logger? create

  IF @logger_id IS NULL

  BEGIN

      exec equis.get_ids 1, @logger_id output;

      INSERT INTO dt_logger(logger_id, logger_code, live_data_source, status_flag)

      VALUES (@logger_id, 'Pi-RDBMS', 'Pi-RDBMS', 'A');

  END

 

  -- get logger_series_id for Pi-RDBMS insert, match by series_name

  declare @logger_series_id int;

  set @logger_series_id = (SELECT top 1 logger_series_id from dt_logger_series where series_name = @tag_source AND status_flag = 'A' AND logger_id = @logger_id);

 

  -- no series? create

  if @logger_series_id IS NULL

  BEGIN

      exec equis.get_ids 1, @logger_series_id output;

      INSERT INTO dt_logger_series(logger_id, logger_series_id, series_name, status_flag)

      VALUES (@logger_id, @logger_series_id, @tag_source, 'A');

  END

 

  -- insert provided values

  INSERT INTO dt_logger_datum(logger_series_id, datum_utc_dt, datum_value)

  VALUES(@logger_series_id, @ts, @value)

 

END TRY

BEGIN CATCH

END CATCH

 

GO