Datasets and SQL Queries

Datasets Functionality

The Dataset module makes it possible to exchange information with SQL databases and query data within the application. Users can create custom visualizations using Displays, implement complex logic with Scripts, and configure dynamic Alarms using database data. Moreover, the Dataset module can integrate real-time Tags within query strings, enhancing the dynamism of data interaction. It can also manage files and recipes in ASCII, Unicode, or XML formats, broadening its applicability across different data types.  

Feature Highlights

• Multi-threaded concurrent connections for efficient data handling.
• Dedicated SQLite editor for streamlined database management.
  
• Intuitive visual query builder that simplifies the process of query creation and manipulation.

Processing Data Requests

The Datasets Data Server Service forms an integral part of the module, ensuring high performance and seamless integration while exchanging structured data collections in real-time. It efficiently processes data requests from other modules, enabling connectivity with various components such as HMI/SCADA screens, scripting, and reporting tools.

The Dataset module also serves as the data storage configuration hub for other modules. The Alarm and Historian modules, which generate events and records for long-term retention, utilize the Dataset module for defining data storage. Similarly, the Security Module can utilize user definitions from an external database within the Dataset module.

Data Source Virtualization

Data Source Virtualization is an advanced feature that simplifies data management across multiple data sources. It provides a unified interface for accessing, querying, and manipulating data, regardless of the underlying data storage technology. This feature allows modifications to the underlying data source repositories without impacting the rest of the application. The module works seamlessly with various data storage technologies. It enables centralized management of data connections and configurations, streamlining the integration of different data sources into your projects.

For a more detailed explanation of how the Dataset module works, please refer to the Advanced Dataset Topics page.

Main Components

To configure the Datasets module, you need to understand four main components:

• Datasets DBs
• Queries
• Tables
• Files

You find a brief description of each feature below.

Dataset DBs

For the Dataset module to communicate with an external database, you must create a connection with specific parameters. These connections, created within the module's Datasets → DBs section, are referred to as Dataset DBs.

Dataset Queries

In the context of the Dataset module, a Dataset Query refers not only to an SQL query string but also to a Project object that has a logical name, an SQL query related to that logical name, and other parameters defined within the Datasets → Queries section. There are many ways to map the results of a query execution with Tags automatically.

Dataset Tables

A Dataset Table is a logical name created within a project to set up access to a specific table in a connected database. These tables are listed within the Datasets → Tables section of the module. The Tags in the real-time database can easily be mapped to columns in the tables to insert, update, or read operations.

Dataset Files

A Dataset File is a logical name that defines parameters for reading and writing files in ASCII, Unicode, or XML formats. To manage the Dataset Files, go to Datasets → Files. 

Configuring the Dataset module

The basic process for configurating the Dataset module is as follows:

1. Create a Database connection. Learn more at Database DBs.

2. Create Queries, assiging logical names and adding realtime parameters. Access Dataset Queries for more information.

3. Manage your project tables and connect inserted data with project Tags. See the Dataset Tables for more details.
4. Exchange data via files. Learn more at Dataset Files.

The above steps are a simplified explanation of the configuration process.  For additional information on how to configure the Datasets module, access Configuring Datasets.

More On The Datasets Module

To learn more about the Datasets module, you can use the additional documentation pages available on the documentation:

• Learn how to use the Datasets module together with other modules accessing the Working with Datasets page.

The Advanced Dataset Topics page presents complementary information about the following: 

• Databases Connections and Interactions
• Data Management

• Datasets Runtime attributes

• Preventing SQL Injections
• Time zone handling

Use the Datasets Best Practices And Troubleshooting pages to access best practices tips and troubleshooting guides.

In this section: