| Product(s): | WaterGEMS, WaterCAD |
| Version(s): | 23.00.00.19 |
| Area: | SCADA Signals |
| Original Author: | Douglas Miranda |
Problem
How can I integrate field data into my model connecting to non-relational databases through ODBC/OLEDB Driver provided by the SCADA/Historian platform vendor?
Solution
SCADA/Historian vendor that stores the historical/ real-time field data in non-relational databases typically offer ways to run relational queries to It by using OLEDB drivers.
Each Driver can have its own language structure to perform queries to extract the data, so it’s important to check the Driver’s documentation to understand more on this.
After having the Driver installed in the machine, start the connection by loading the SCADA Signals tool in WaterCAD / WaterGEMS.
Choose the option to connect to a database source:
In the Connection setting, select the option “OLEDB Source”:
Click on the Ellipsis button to load the Driver’s connection properties dialog box.
The Driver installed should be listed in the “OLEDB Provider” drop down menu:
If your Driver operates in 32 bit only, you will need to reopen WaterCAD/GEMS using the 32 bit version (see this article).
Enter with user credentials to connect to the database.
Check the Driver’s documentation to find out if an Initial Catalog must be selected.
For PI System, for instance, it’s necessary to select the “PI Archive” as Initial Catalog.
Click on Test Connection to evaluate the settings and run troubleshooting if necessary.
Click Ok to close the Connection Properties dialog box. And Click Ok again to close the Database Connection box and open the Database Source window:
For Table Name, select the option according to the Driver’s documentation. In some cases you can find different tables that store the raw and interpolated tables separately.
For Source Format, if the Driver provides attributes separately for Tag Name or ID, Value and timestamp, then select the “One Value per row” option and choose the attributes for “Signal Name”, “Value” and “TimeStamp”. If the Driver provides one column with value per Signal Name, then the “Multiple values per row” option is better. Finally select the field for TimeStamp. From here you can proceed with the regular SCADAConnect configuration as explained in this article.
Starting by the WaterCAD/GEMS version 23.00.00.19, in cases where the driver doesn´t provide a single table/view with all information, you can leave the Table Name menu unselected. With this, the 3 fields can be queried and chosen using the SQL Statements option.
If you need to specify queries, select the Historical option on the left side of the window to get the SQL Statement button enabled. In the SQL Statements option, you can specify the queries that list the signals and to get the historical measurements. Refer to this article to understand how to define the Custom Queries. From here you can proceed with the regular SCADAConnect configuration as explained in this article.
Here are some example of Queries used for different SCADA/Historian vendors . Queries always need small adjustments depending on your project’s details, or by the Driver and/or platform versioning or another detail. Always check the documentation of the Driver, specially if the SCADA platform you are using is not listed below.
PI System - PI Archive (PI System’s Driver: PI OLEDB Provider)
Available Signals SQL Statement
SELECT DISTINCT [tag] FROM pipoint..pipoint2
Signal Data SQL Statement - Historical, One Value per Row
SELECT [tag],[value],[time],[questionable] FROM piarchive..picomp2 WHERE [tag] in (@requestedsignals) and (([time]>=@startdatetime) and ([time]<=@enddatetime))
GE Proficy – GE IFix (GE’s Driver: iHistorian OLEDB Provider)
Available Signals SQL Statement
set rowcount=0 select Tagname from ihTags
Signal Data SQL Statement - Historical, One Value per Row
set intervalmilliseconds=900s, samplingmode=interpolated
select Tagname,Value,TimeStamp from ihRawData where Tagname = (@requestedsignals) and ((TimeStamp>=@startdatetime) and (TimeStamp<=@enddatetime))
Ellipse EPM – (EPM’s Driver: EPM OLEDB Data Source Manager)
Available Signals SQL Statement
SELECT * FROM EpmBasicVariablesView()
Signal Data SQL Statement - Historical, One Value per Row
SELECT Name, Timestamp as DateTime, Quality, Value FROM EpmQueryRawFunction(0, @startdatetime("yyyy-MM-dd HH:mm:ss"), @enddatetime("yyyy-MM-dd HH:mm:ss"),0, 1, @requestedsignals_nobrackets)