Introduction
The Dataset Module is a versatile tool designed for collecting, analyzing, and visualizing data from various sources within the platform. Supporting a wide range of database technologies, such as ADO.NET, ODBC, OleDB, and native interfaces, it offers seamless connectivity to key databases, including SQL Server, Oracle, SQLite, PostgreSQL, and more, making configuration simple and efficient.
Designed with real-time applications in mind, the module boasts several useful features such as multi-threaded concurrent connections, a built-in editor for SQLite, and an intuitive visual query builder. Additionally, it enables users to integrate real-time tags within query strings and manage files and recipes in ASCII, Unicode, or XML formats, enhancing the overall functionality and user experience.
On this page:
Purpose and Key Concepts
Dataset DBs
In order for the Dataset Module to communicate with an external database, a connection must be created with certain parameters. These connections, which are created within the Datasets → DBs section of the module, 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 automatically map the results of a query execution with Tags.
Dataset Tables
A Dataset Table is a logical name that is 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 perform 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.
Understanding the Datasets Module
Features Highlights
SQL Query Support: The Dataset Module provides support for SQL queries, allowing users to easily extract, manipulate, and transform data from a variety of sources.
Integration with External Data Sources: The Dataset Module can integrate with a wide range of external data sources, including databases, CSV files, and other external sources, providing a flexible and powerful tool for data collection and analysis.
Access Types: Access Types allow users to group and organize data points based on their usage and permissions, providing a powerful tool for managing and controlling access to data within the Dataset Module.
Visual Query Editor: The Visual Query Editor provides a user-friendly interface for creating and editing SQL queries, making it easy for users to define complex queries without needing extensive SQL knowledge.
Customizable Dashboards: The Dataset Module provides the ability to create custom dashboards and visualizations based on the data collected by the module, allowing users to easily view and analyze data in real-time.
Store Procedures Execution: The Dataset Module can execute Store Procedures and return the results to the platform, allowing users to perform advanced data manipulation and analysis within the context of the platform.
Real-Time Execution: The Dataset Module supports real-time execution of SQL queries, allowing users to monitor and analyze data as it is generated in real-time.
Datasets Data Server Service
The Datasets Data Server Service is an essential part of the Datasets Module. It is responsible for managing and providing efficient access to datasets, which are structured collections of data used in various project applications such as data analysis, reporting, and visualization. The service ensures high performance and seamless integration with other components, offering flexibility and ease of use when working with datasets.
Processing Requests from Other Modules
The Processing Requests from Other Modules feature in FactoryStudio enables smooth communication between different project modules. It handles data requests and interactions, ensuring efficient data exchange and coordination among various components such as HMI/SCADA screens, scripting, and reporting tools.
Databases Used when Running the Project
The platform has pre-configured databases that store essential project information, including real-time and historical data, alarms, events, and system configurations. These databases provide a reliable and efficient foundation for data storage and retrieval, allowing users to focus on building and customizing their projects without worrying about database setup and management.
Data Source Virtualization Benefits
Data Source Virtualization is an advanced feature that simplifies data management across multiple data sources. It offers a unified interface for accessing, querying, and manipulating data, regardless of the underlying data storage technology, ensuring flexibility and ease of use.
Agnostics, Standards, Centralized Management
FactoryStudio's Data Source Virtualization is designed to be agnostic and adhere to industry standards, allowing it to work seamlessly with various data storage technologies, such as SQL databases, OPC UA servers, or custom data sources. This approach enables centralized management of data connections and configurations, streamlining the process of integrating different data sources into your project.
Configuring the Dataset Module
Learn how to connect to data sources, create queries, and optimize performance for efficient data management.
This section provides essential guidance for setting up and customizing the Dataset Module, including:
- Datasets and SQL Queries
- Managing DB Connections
- Datasets Queries Configuration
- Datasets Tables Configuration
- Datasets and SQL Queries
Configuration Workflow
The typical configuration workflow for the Dataset Module has the following sequence:
| Dataset Module Configuration Workflow | ||
|---|---|---|
| Action | Where | Comments |
| Create the required database connections (DBs) | Datasets → DBs | Collect the information to connect with the databases required to your Project. Use the built-in SQLite database as a temporary development tool if one of your connected database is not available yet.The virtualization model with logical names for queries and tables will make your project work directly with the new connection with the production database, without having to change anything on the Project Configuration other than that database connection, |
| Prepare the Queries the Project uses | Datasets → Queries | Either using the Visual Query Editor, or getting the query string from IT or plant facilitator, collect and create the logical names Dataset.Query to identify those queries. |
| Modify the Query to add real-time tags | Datasets → Queries | Easily modify the query with the parameters that need be connected with real-time values. For instance, a query that has the text. WHERE col1 = 5 can be modified to WHERE col1 = {{tag.Test}}. The value of the Tag will be added to proper position when the query is executed. |
| Prepare the Tables the Project uses | Datasets → Tables | When you need to Insert or Modify data, you need to access the Database Table directly. In some cases, all the information you need is one table, so there is no needing to create a Query. You can easily connect the contents what are inserted in the table with Tags in the Project. |
| Configure the Stored Procedures | Datasets → Queries | The Module Database can execute Stored Procedures; just define it using the same interface for the queries. |
| Configure data exchange with Files | Datasets → Files | If necessary to exchange values of Tags with plain text or XML files, set that configuration. |
| Use your Dataset logical objects | All Project | The logical object names created for Queries, Tables and Files can be used in any part of the project. Examples: Script calculation, Display visualization, and others |
Managing DB Connections
When using SQLite databases, the Module Dataset can automatically create the Database if necessary; for other ones, the Database itself must already exist before you set your connection. Users with any Permission groups can create new connections in the Project, but only the Administrator can configure databases password logins.
To create a new Database connection:
Go to Datasets → DBs.
Click Create New. The Create New Database Connection window displays.
Enter or select information, as needed.
Click OK. The database is added as a new row in the table.
Edit the row fields to modify the required settings.
Dataset DB Configuration Properties | |
|---|---|
Column | Description |
Name | Enter a name for the database configuration. The system allows you to know if the name is not valid. |
Provider | Identifies the Provider technology used in this connection |
Database | Identifies to which type of dataset is this connection |
ConnectionString | Enter the information needed to connect with the database. You use macros on the connection string. Example: for the filename in a SQLite connection string, use <ProjectName> that is replaced by the name of the project. |
LogonName | Enter a valid login name for the database. |
LogonPassword | Enter the password that corresponds to the database login. (Only accessible by Administrators) |
ServerIP | Optionally, an IP or DNS name for a computer to be used as a Secure Gateway. |
Description | Enter a description for the database connection. |
Please check the Connecting with SQL Server and Connecting with Excel for additional information.
There are four database connection already created in any new Project:
Datasets DB - Pre-defined database connections | |||
|---|---|---|---|
DB | Database | Path Location | Usage |
Retentive | SQLite | <ProjectNameAndPath>.dbRetentive | Stores values for Retentive Tags. |
RuntimeUsers | SQLite | <ProjectNameAndPath>.dbRuntimeUsers | Stores dynamically created Users. |
AlarmHistorian | SQLite | <ProjectNameAndPath>.dbAlarmHistorian | Stores Alarm and AuditTrail records. |
TagHistorian | SQLite | <ProjectNameAndPath>.dbTagHistorian | Stores Tag Historian and Annotations. |
Any of them can be customized to any type of database.
The selection of best storage location depends on all kind of factors, from internal company procedures to the volume of data and how the data shall be used. Therefore, that is decision to each Project according to its requirements.
If needed to use another database for the pre-defined connections, execute the following steps:
Rename or Delete the previous DB. This step is necessary, as the system would not allow to create two objects with the same name.
Crate a new DB with the same name of the previous DB, with the required Database and connection strings.
Datasets Queries Configuration
You can configure queries to perform more advanced functions with SQL statements to work with data from external databases.
To configure Dataset queries:
Go to Datasets → Queries.
Enter the field values as needed.
Dataset Query Configuration Properties | |
|---|---|
Column | Description |
Name | Enter a name for the query. The system allows you to know if the name is not valid. |
DB | Select the database configuration. |
SqlStatement | Enter the query using SQL syntax. |
Mapping | Click "..." to select the tags that you want to populate with data from specific columns returned by the query. |
MappingDateTime | Select the time reference (UTC or Local). |
Description | Enter a description for the table configuration. |
With the Visual Query Editor, users can drag and drop tables, define relationships, and add filters and conditions using a simple graphical interface. Once the query is created, it can be saved and executed like any other query within the Dataset Module. Check the Visual SQL Query Builder page for complete information
Datasets Tables Configuration
To configure dataset tables:
Go to Datasets → Tables.
Enter the field values as needed.
Dataset Table Configuration Properties | |
|---|---|
Field / Column | Description |
Name | Enter a name for the table configuration. The system lets you know if the name is not valid. |
DB | Select the database connecton. |
TableName | Select or type the table name in the Database you want to access |
WhereCondition | Specify the parameters that will filter the data using SQL syntax. E.g. " |
Access | Select the access permissions for the table. |
Mapping | Click "..." to select the tags that you want to populate with data in the first row of the table with data from specific columns. |
MappingDateTime | Select the time reference (UTC or Local). |
Description | Enter a description for the table configuration. |
Datasets Files Configuration
To configure dataset files:
Go to Datasets → Files.
Enter the field values as needed.
Dataset File Configuration Properties | |
|---|---|
Column | Description |
Name | Enter a name for the file configuration. The system allows you to know if the name is not valid. |
FileName | Enter the full path to the file. The file path can have Tag values embedded using curly brackets syntax. E.g.: When executing, the area in curly brackets is replaced by the value of the Tag. |
FileType | Select the type of file. |
Objects | Click "..." to select the tags that you want to populate with data from the file with data from specific columns. |
Description | Enter a description for the file configuration. |
XMLSchemaType | Represents the schema type of an XML file, which can be: a TagList, XML that contains a tag list with the tag name and tag value; or a TagObject, XML that contains the entire tag tree and its children. |
Working with the Dataset Module
Runtime Execution
One of the key features of the Dataset Module is the ability to execute SQL queries and retrieve data in real-time. Here are some ways to leverage the runtime execution features of the Dataset Module:
- Create SQL queries to retrieve data from external databases.
- Use query results to trigger events and actions within the platform environment.
- Configure event triggers based on specific query criteria, such as changes to a specific data point or a threshold value being exceeded.
The Dataset Module can be easily integrated with other modules within the software environment. Here are some examples of how the Dataset Module can be used in conjunction with other modules:
- Alarm Manager: Configure alarms based on query results to trigger notifications and actions.
- Visualization: Display query results on screens and dashboards using DataGrids and other visualization tools.
- Scripting: Use query results to trigger custom scripts and perform complex data processing and analysis.
By leveraging these integration options, users can gain greater insight and control over their data sources within the platform. With the ability to execute SQL queries and trigger actions based on query results, the Dataset Module provides a powerful set of tools for working with data.
Monitoring Databases Connections
Monitoring Database Connections is an essential aspect of maintaining a reliable and efficient system within the platform. By keeping track of database connections, you can ensure that your data is being accessed and updated correctly. Here are some ways to monitor database connections in FactoryStudio:
- Connection Status: Use the ConnectionStatus runtime attribute to check if a database connection is active or inactive. This can help you identify any connection issues and take corrective action when necessary.
- Query Status: The QueryStatus runtime attribute indicates whether a query is currently executing or not. This information can help you monitor query performance and identify potential bottlenecks or issues.
- Query Execution Metrics: Keep track of query execution details using attributes like QueryLastExecution, which shows the date and time of the last query execution, and QueryExecutionCount, which indicates the number of times the query has been executed. These metrics can provide insights into system performance and help you optimize your queries.
- Data Acquisition Rate: Monitor the rate at which data is being acquired by the Dataset Module using the DataAcquisitionRate attribute. This information can help you identify potential issues with data retrieval and ensure that your system is operating efficiently.
Showing DataGrids Tables on Displays
One of the key features of the Dataset Module is the ability to display query results on screens and dashboards using visualization tools like DataGrids. Here are some steps for using DataGrids to display query results:
- Create a query in the Dataset Module to retrieve the desired data.
- In the Visualization Module, add a DataGrid control to the screen or dashboard.
- Configure the DataGrid to display the query results by selecting the data source and column mappings.
- Save and preview the screen or dashboard to display the query results on the DataGrid.
Using Query Results on Scripts and Tags
Users can use query results to trigger actions in custom scripts and tags. Here are some steps for using query results in scripts and tags:
- Create a query in the Dataset Module to retrieve the desired data.
- In the Scripting Module, create a custom script that references the query results.
- Use the query results to trigger specific actions within the script, such as sending notifications or updating tags.
- Save and execute the script to perform the desired actions.
Check the Using Stored Procedures page for additional information.
Dataset Module Runtime Attributes
- ConnectionStatus: indicates whether the database connection is active or inactive.
- QueryStatus: indicates whether the query is currently executing or not.
- QueryLastExecution: indicates the date and time of the last query execution.
- QueryExecutionCount: indicates the number of times the query has been executed.
- DataAcquisitionRate: indicates the rate at which data is being acquired by the Dataset Module.
Troubleshooting and Best Practices
<<<<For each issue/error, there must have a step-by-step procedure about how to apply the solution. Need technical support.>>>>
Common #Issues and Solutions
# Connection errors
If the connection to an external database is lost or otherwise interrupted, queries and stored procedures may fail to execute.
Solution: To resolve this issue, check the connection settings in the Dataset Module and ensure that the external database is reachable. If the connection settings are correct, try restarting the external database or resetting the connection in the Dataset Module.
# Query performance issues
If a query is taking too long to execute or is consuming too many system resources, it may be necessary to optimize the query or restructure the underlying database schema.
Solution: Some strategies for improving query performance include adding indexes, reducing the number of joins or subqueries, and limiting the amount of data returned by the query. Additionally, consider using the Query Analyzer tool in the Dataset Module to identify specific performance bottlenecks and optimize the query accordingly.
# Data type compatibility issues
If the data types returned by a query or stored procedure are incompatible with the data types expected by the platform, it may be necessary to use data conversion functions to transform the data into a compatible format.
Solution: Check the data type settings in the Dataset Module and ensure that the query or stored procedure is returning data in the correct format. If necessary, use data conversion functions like CAST or CONVERT to transform the data into the correct format.
# Tag mapping issues
If the mapping between query results and tags is not properly configured, tags may not update or display the correct values.
Solution: Review the tag mapping settings in the Dataset Module and ensure that the correct tags are being updated with the correct query results. Additionally, check the data type settings for the tags and ensure that they are compatible with the data types returned by the query or stored procedure.
# Authentication errors
If the authentication credentials for an external database are incorrect or expired, queries and stored procedures may fail to execute.
Solution: Check the authentication settings in the Dataset Module and ensure that the correct credentials are being used to access the external database. If the credentials are correct, check with the database administrator to ensure that the user account has the necessary permissions to execute the query or stored procedure.
Best Practices and #Recommendations
# Plan queries carefully
When creating queries, take the time to carefully plan and structure the SQL statement to ensure that it returns the desired data in an efficient manner. Avoid using overly complex SQL statements or joining large tables without first considering the potential impact on system performance.
# Use parameterized queries
Use parameterized queries whenever possible to prevent SQL injection attacks and improve query performance. Parameterized queries use placeholders for input values that are replaced at runtime, rather than directly embedding user input in the SQL statement.
# Validate input values
Always validate user input values before passing them to SQL queries or stored procedures to prevent potential security vulnerabilities. Use input validation functions or regex patterns to ensure that user input values are in the expected format and do not contain malicious code.
# Monitor query performance
Use performance monitoring tools like the Query Analyzer to identify slow-running or resource-intensive queries and optimize them as necessary. Consider using query caching or stored procedures to reduce the overhead of frequently executed queries.
# Secure database connections
Use secure database connections to protect sensitive data and prevent unauthorized access. Use SSL or other encryption protocols to secure database connections, and use strong authentication mechanisms like Kerberos or LDAP to authenticate users.
# Document queries and procedures
Document queries and stored procedures to ensure that other developers and system administrators can understand and maintain the code. Use clear and descriptive comments and document the purpose and expected results of each query or procedure.
# Test queries and procedures thoroughly
Before deploying SQL queries or stored procedures to production environments, thoroughly test them in a development or testing environment to identify potential issues or compatibility problems. Use test data that accurately reflects the expected production data and ensure that the queries or procedures return the desired results.