Page History

Key Concepts and Terminology

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

Key Concepts and Terminology

Dataset DBs

To each external database the Module Database will communicate, a connection needs to be created with certain parameters. Each connection, created on Datasets → DBs is called in this Module as Dataset DB.

Dataset Queries

In the context of this module, when we refer to a Dataset Query, we mean not only the SQL query string, but the Project object that has a logical name, the SQL query related to that logical name, and other parameters as defined on Datasets → Queries. There are many ways to automatically map the result results of a query execution with Tags. 

Dataset Tables

Similar to the queries, a A Dataset Table refers to is a logical name , created in the project, to setup the that is created within a project to set up access to a specific Table table in a connected database. The These tables in use are listed on 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 parameter to read and write from parameters for reading and writing files in ASCII, Unicode, or XML formats.

Access Types

Access Types are associated with Dataset Tables, Queries, and Files, and can be used to define access permissions for these data sources. For example, you might create an Access Type for "Administrators" that allows might allow them to view and modify all data points, while restricting access an Access Type for "Operators" might restrict access to a specific subset of data points.

When defining Access Types, users can choose from a range of permissions, including "Read", "Write", "Read/Write", and "None". These permissions can be applied at the column level, allowing users to restrict access to specific columns within a Dataset Table.

<<<The original is "Device Module", but I think it was meant to be "Dataset Module", so I reformulate the title. Please check if it's correct. >>>

How the Dataset Module Works

The Dataset Module is a key

How the Device Module Works

component of the platform,

At a high level, the Device Module works by establishing connections with external devices and data sources, and managing the flow of data between these sources and the platform. This involves setting up communication protocols, such as OPC-UA or Modbus, configuring device parameters, and managing data transmission and synchronization.

Once data is collected by the Device Module, it can be passed on to other modules for processing and visualization. For example, data might be stored in a Dataset Table for further analysis, or displayed on a custom dashboard or visualization tool.

The Device Module also provides a range of features for managing and configuring devices, such as managing device drivers, configuring data acquisition rates, and setting up alarms and notifications based on device events or data thresholds.

Overall, the Device Module is a critical component of the FactoryStudio platform, responsible for integrating and communicating with a wide range of external devices and data sources. By providing a flexible and powerful framework for managing device data, the Device Module enables users to collect, analyze, and visualize data in real-time, and make informed decisions based on actionable insights.

Features Highlights

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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.

designed to enable data collection, analysis, and visualization. The module provides the ability to connect to a wide range of data sources, including external databases, CSV files, and other data sources, and extract, transform, and load data using SQL queries.

The Dataset Module uses Access Types to manage and organize data points based on their usage and permissions. Users can create Access Types to group and categorize data points, and assign different levels of access to different users or groups. Access Types allow for fine-grained control over data access and management, making it easy to keep track of data points and ensure that they are being used appropriately.

Once data is collected by the Dataset Module, it can be easily displayed on the platform using a variety of visualization tools, including DataGrids and custom dashboards. Data can also be passed on to other modules for further processing, such as using query results on scripts and tags, or starting the execution of store procedures.

The Dataset Module provides a range of configuration options, including the ability to create custom database connections and customize pre-defined databases. Users can configure queries, tables, and files, and use the Visual Query Editor to create and edit queries.

Overall, the Dataset Module is a powerful tool for managing data in the platform, providing a flexible and efficient framework for data collection, analysis, and visualization. With its range of features and options, the Dataset Module enables users to quickly and easily extract insights from data and make informed decisions based on actionable insights.

Features Highlights

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

7. 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.

Managing Channels and Nodes

The Dataset Module provides a powerful toolset for managing channels and nodes. A channel is a logical grouping of data points, while a node represents a single data point within a channel. Channels can be created to group related data points together, making it easier to organize and manage large datasets.

To create a new channel, users can simply navigate to the Datasets → Channels section of the platform and select "Add New Channel". They can then assign a logical name to the channel and add any necessary metadata, such as descriptions or comments.

Once a channel has been created, users can add nodes to the channel to represent individual data points. Nodes can be added manually or imported from external data sources, and can be assigned to one or more channels for easier organization.

Organizing the Points with Access Types

Access Types are a key feature of the Dataset Module, enabling users to group and categorize data points based on their usage and permissions. Access Types allow users to define fine-grained access controls for different data points, making it easy to manage data access and ensure that data is being used appropriately.

To create a new Access Type, users can navigate to the Datasets → Access Types section of the platform and select "Add New Access Type". They can then assign a logical name to the Access Type and add any necessary metadata, such as descriptions or comments.

Once an Access Type has been created, users can assign it to one or more data points, such as channels or nodes. They can then define the level of access that is granted to users or groups for each Access Type, such as read-only, read-write, or no access.

Handling Read and Write Events

The Dataset Module allows users to handle read and write events for data points, which can be used to trigger actions based on changes in data. When a data point is read or written to, the Dataset Module can be configured to trigger an event, which can be used to execute a script or perform some other action.

Read and write events can be useful in a variety of scenarios, such as triggering alarms or notifications when certain data points are updated, or executing custom scripts to perform data transformations or updates. By providing a flexible and powerful framework for handling events, the Dataset Module enables users to create sophisticated data processing pipelines and respond to changing data in real-time.

Managing Channels and Nodes

Organizing the Points with Access Types

Handling Read and Write Events

Configuring the Dataset Module

Configuration Workflow

The typical configuration workflow for the Dataset Module has the following sequence:

Creating 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.

Customizing Pre-defined Databases

There are four database connection already created in any new Project:

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.
• That is all!
  

ConnectionString example for SQL Express 

• Data Source: The server path and instance that will have the databases.
• Initial Catalog: The name of the database that will be used.

Additional Settings for Tag Historian and AlarmHistorian 

• Store and Forward: Enabling this option will cause the system to store the data locally if communication with the database is lost, and forward the data to synchronize once the connection is back again.

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.

Datasets Tables Configuration

To configure database tables:

• Go to Datasets → Tables.
• Enter the field values as needed.

Datasets Files Configuration

To configure dataset files:

• Go to Datasets → Files.
• Enter the field values as needed.

Using the Visual Query Editor

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.

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.

Integration with Other Modules

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.

DataGrids 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:

1. Create a query in the Dataset Module to retrieve the desired data.
2. In the Visualization Module, add a DataGrid control to the screen or dashboard.
3. Configure the DataGrid to display the query results by selecting the data source and column mappings.
4. 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:

1. Create a query in the Dataset Module to retrieve the desired data.
2. In the Scripting Module, create a custom script that references the query results.
3. Use the query results to trigger specific actions within the script, such as sending notifications or updating tags.
4. Save and execute the script to perform the desired actions.

Starting the Execution of Store Procedures

In the platform, the Dataset Module can execute stored procedures in external databases. Here are some steps for starting the execution of stored procedures:

1. Create a query in the Dataset Module that references the stored procedure in the external database.
2. Configure the query to pass any necessary input parameters to the stored procedure.
3. Set up event triggers based on the stored procedure execution results, such as to trigger an alarm or update a tag.
4. Save and execute the query to start the execution of the stored procedure.

Advanced Features and Options

Our platform provides many advanced features and options for working with data sources and SQL queries in the Dataset Module. Here are some examples of advanced features and options available:

• Pass input parameters to SQL queries or stored procedures by using tags as input variables.
• Map query or stored procedure output to specific data points or tags within the platform.
• Use event triggers based on query or stored procedure execution results to trigger notifications, alarms, or other actions.
• Use advanced SQL features like joins, subqueries, and aggregate functions to perform complex data processing and analysis.

Troubleshooting and Best Practices

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.

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.

In this section...