In order to configure the source connection from the interface, click Configuration and select Source Configuration.

The next screen will display parameters such as Data Source, Username, Password, Connection and Command Timeouts, and allow you to Test Connection. The Source Configuration screen has two tabs: one to setup the connection to the database and the other to define the table mappings/cross references.

Database Connection - Configure the settings needed to connect to your database

• Configuration Template - no templates are currently defined. Choose 'User Defined' to setup a connection to your database.
• Data Source - this field specifies, in loose terms, the "Database/Datasource server name" where your database system is configured to store data. "Database/Datasource server name" means different things depending on whether you are running against Oracle or MS SQL:
  • Oracle - you can type in either the Oracle TNS name or a string in the form of host:port/SID, if using the Oracle HOSTNAME adapter.
  • MS SQL - this is in the form of host\sql_instance_name
• Database Name - the name of the database that you are connecting to.
• DBMS Type -select the appropriate type of your Hach WIMS database
• OLE DB Provider - this specifies which "database driver" the interface will use when connecting to your database and again depends on the database type:
  
  • Oracle - the default string should be fine
  • MS SQL - if connecting to MS SQL 2005 or later, use the native client to connect. Specify SQLNCLI as the provider. NOTE: You will need to have the Microsoft SQL Native Client installed for this to work. If connecting to MS SQL 2000, specify SQLOLEDB as the provider.
  • MS Access - add the full path and file name of the MS Access MDB file to Data Source
  • ODBC - need to create a System DSN (Data Source Name) to connect to MS Access-type database
• Username - The username you want to use to connect to the database. This user should have permission to access the database and tables that contain data.
• Password - The password that will be used to connect to the database
• Connection Timeout in Seconds - how many seconds the program should wait when establishing a connection to the Hach WIMS database before aborting.
• Command Timeout in Seconds - specifies how many seconds the program should wait for a query to complete before aborting the operation.
  WARNING: Large values for these two settings can cause the program to not respond for a long period of time! Do NOT use 0 unless you know what you are doing - it will cause the program to wait indefinitely, and if your database server is down, the only way to cancel the operation is to forcefully quit the program.
  
• Customizations - this should be left empty unless you were told otherwise by a Hach Support Engineer or you know what you are doing.

Table Mappings - Allows you to tell the interface which fields in the database table contain the Date, Tag, Value and optionally Source. The interface needs to know which column in the table contains the date field, which column contains the tag, which column contains the value/data and if the tag is not unique then a table column that contains the source. The interface allows you to retrieve data from one or more tables. Each table that you configure needs to define the column mappings.

Table Name - This interface is designed around monthly tables.  Month and Year are part of the table name.  The Interface needs to be able to resolve the table name when building queries.  The following tags can be part of the table name so the table name can be resolved based on the date of the data being queried ({m}, {mm}, {yy}, {yyyy}). These tags will be replaced with the current month or year for the table name used in a query. Note: The month or day tags are not case sensitive.

Daily Tables - Support for daily tables has been added.  Daily tags work the same as monthly or yearly tags described above. They are case insensitive. If using Daily tables check the Daily Tables checkbox in Table Frequency.  This will insure variables that are set to After that go into the next day will be resolved correctly.

• {m) or {M}  Single digit month.  example  1,5,11
• {mm} or {MM} month with leading zero.  example  01, 05, 11
• {yy} or {YY}  two digit year  example 21
• {yyyy} or {YYYY}  four digit year example 2021
• {d} or {D} single digit day. example 1,30
• {dd} or {DD} two digit day. example 01, 30

Examples of table name conversion for March 5, 2021 

• Ignition_values_{m}_{yyyy}    Ignition_values_3_2021
• Ignition_values_{mm}_{yy}     Ignition_values_03_21
• Igintion_values){d}_{m}_{yyyy}    Ignition_values_5_3_2021

• Select Table - Allows you to pick a table that you want to modify or test
• Add Table - Adds another table that the interface can collect data from. You need to click 'Add Table' before beginning to type in your table mappings. When you click 'Add Table' it will create a table that you are then able to set it up to import data from.
• Remove Table - Removes the currently selected table
• Narrow Table Format - The interface supports two types of table configurations: one with date, tag and value columns and one with date,source,tag and value columns. If your tag is unique across the system then choose date,tag, value. If your tag is not unique then choose date, source, tag, value.

Typically, SCADA data is stored in a timestamped data table in either "narrow" or "wide" format.  A narrow format table usually consist of a record for each Tag/Time with the following structure:

NARROW Format table example (date,tag, and Value):

Timestamp	Tag	Value
12/1/2020 00:00:00	03005001	469.311
12/1/2020 01:00:00	03005001	473.462
12/1/2020 02:00:00	03005001	472.504
12/1/2020 03:00:00	03005001	474.288
...

NARROW Format table example (date,source, tag, and Value):

Timestamp	Source	Tag	Value
12/1/2020 08:15:22	BASIN1	03005001	469.311
12/1/2020 08:15:22	BASIN2	03005001	473.462
12/1/2020 08:16:22	BASIN1	03005001	472.504
12/1/2020 08:16:22	BASIN2	03005001	474.288
…

 

Advanced - Allows you tweak advanced configuration parameters

• Connection Alive Test Strategy - Allows you to pick what query the interface should run against source when testing whether the connection is still alive.  Prior to every interface import a simple query is executed to verify we are still connected to the source. The default (Test with SELECT TOP ROW Query) behavior is valid for most cases other than when using database views instead of tables. In case of views, you may need to select Test with GET DATE TIME Query

Test with 'SELECT TOP ROW'  Query - Executes a query against the first mapped table depending on the DBMS type:

• For Oracle:  Select * from Owner.TableName where RowNum <=1
• For MS SQL: Select Top 1 * from Owner.TableName
• For MySQL: Select * from Owner.TableName LIMIT 1
• All others: Select top 1 * from [TableName]

Test with 'GET DATE TIME' Query

• For Oracle: Select SYSDATE from dual
• For MS SQL: Select getdate()
• For MySQL: Select curdate()
• All others: Select top 1 * from [TableName]

Test with Custom Query - Executes the entered SQL Query statement. 

• Source Date Settings
• Source Dates are in UTC - Check if dates are stored as UTC.
• Source Dates are in Epoch/Unix - Check it dates are stored as Epoch/Unix

 

• Status Values to Import (Optional)

• Data Type - Data Type of the Status column in the database.
• Status Values - Define the Status Values of the records you want to include in the import.

Note: If Status Values or the STATUS Column in Table Mappings are not defined, this feature will not be used.

XREF by TagPath - Allows you to Cross Reference WIMS Variables using the Tag Path

• Cross Reference by Tag Path
• If this is checked, Variable Cross Referencing will be done by Tag Path
• Tag ID for data lookup will be done at run time for each timeslot.  The Tag ID can change, but there is a table with Created and Retired dates for a Tag ID
• Table Name
• Name of the table containing Tag Paths and IDs
• Table Owner / Schema
• Database table Owner/Schema
• [ID] Column Name
• Column Name containing the Tag ID
• [TagPath] Column Name
• Column Name containing the Tag Path
• [Created] Column Name
• Column Name containing the date this ID was created
• [Retired] Column Name
• Column Name containing the date this ID was retired
• If this field is blank, the ID is still valid

Notes on this feature:

• The ID field in this screen is the same as the [TAG] Column defined on the Table Mappings Screen.
• The names may be different, but they both need to refer to the Tag ID
• If Epoch dates are used,  they must be Epoch dates in the XRef table as well as the Data table.
• If this feature is used, the GenBrowser operation will load the browser table with Tag Paths.