How to store and access time-series data with an ODBC database

No access to YouTube? Click here to view locally

Summary

  1. Make sure you have an ODBC-compliant database accessible with a system DSN.
  2. Download and install Cogent DataHub software.
  3. Start the DataHub.
  4. Configure the connection to your database.

For more details and options, see the documentation.

DataHub product used: ODBC Historian

Transcript

  1. How to store and access time-series data with an ODBC database.
  2. To make this connection, you will need an ODBC-compliant database installed on your network that you can access with a system DSN.  Please see the documentation for details.
  3. First, download and install Cogent DataHub software.
  4. Start the DataHub, and from the Properties window, select the External Historian option, and press the Add button.
  5. Select the Historian Type ODBC.
  6. In Connection Settings, create a label for this connection.
  7. Enter the label of the system DSN for the database you are connecting to.
  8. If the database uses Windows Authentication, leave the username and password fields empty.
  9. Or, if the database uses legacy authentication, enter the legacy username and password. Leave the other options at the default settings.
  10. In Tables and Columns, enter the name of the database table that will receive the data.
  11. Then enter the names of the columns from the table for point name, value, quality and timestamp.
  12. For the row index, enter the name of the index column from the table, which is typically the row number.
  13. Then select the datatype used by the index column.
  14. Now we will use the script editor to generate Insert and Query Statements for the database, with example templates. Click the three dots button to open the ASP document editor.
  15. From the Templates list, click on the plus ( + ) symbol to add the template for your database. This will populate the script fields with the correct entries.
  16. Click “Yes” on the dialog box to overwrite all nine script properties.
  17. After installing a template, the script editor window will contain the working script associated with each field listed on the left.  Click OK to save your changes.
  18. The Insert and Query entries are now filled in.
  19. Leave Modify Point Names, Data Sampling and Forwarding at the defaults for now.
  20. Select the datapoints to be recorded by the historian.  We are using DataPid data in this demo.
  21. Click the OK button to close the dialog, and the Apply button in the Properties window to save your configuration. Now you are recording your data.
  22. To access your data from the database, you can make the ODBC historian a historical data source.  Select its label in “Read values from this historian”, and click the Apply button.
  23. You can use Quicktrend to view the data history. In the Properties window, select Quicktrend, then click the Open Quicktrend button.
  24. Select your points and Quicktrend will display their history. The data is also available for store-and-forward and other applications.
  25. This is how to store, access, and display time-series data with an ODBC database, using DataHub software.

Background

ODBC (Open Database Connectivity) specifies a standardized, common interface that is available from almost every database vendor, including SQL Server, MS Access, MySQL, Oracle, and many more. The DataHub External Historian feature has an option for connecting to and exchanging data with any database that supports ODBC connections.

Connecting Cogent DataHub software to an ODBC database is a good way to store DataHub data from supported protocols like OPC DA and UA, OPC A&E and A&C, MQTT, Modbus, ODBC, and more, and to access data that has been stored. This feature is often used in store and forwarding scenarios.

Skkynet provides Cogent DataHub secure-by-design software and services to let you acquire, aggregate, monitor, control visualize, and network live process data in-plant or over insecure external networks, making it ideal for OT to IT and cloud connections. You can isolate control networks from cyber attacks and integrate industrial data under a unified namespace, all without compromising the plant.

Cogent DataHub products wheel diagram