SQL Query Engine Overview

The SQL Query Engine is an optional component in the GENESIS install that enables Microsoft SQL Server to query historical data stored in Data Historian using standard SQL syntax. It exposes historical data as stored procedures that you can call from SQL Server Management Studio (SSMS), reporting tools, or any application with a SQL Server connection, making it useful for SQL-based reporting, Power BI dashboards, ad-hoc analysis and auditing, and integration with existing SQL-connected applications.

The SQL Query Engine installs a set of stored procedures into a SQL Server database of your choosing. When a stored procedure is called, it forwards the request to the Data Historian Query Interface service, which communicates with Data Historian to retrieve the requested historical data. The data is then returned to SQL Server as a result set, so it behaves like any other table-valued query.

Relationship to Data Historian

The SQL Query Engine does not store data independently; it reads exclusively from Data Historian and requires a running Data Historian instance. Configure and maintain data logging in Data Historian as usual; the SQL Query Engine provides an alternative query interface on top of that existing data.

The SQL Query Engine complements other Data Historian access methods:

Access Method	Description
OPC HDA	Standard OPC HDA protocol access for compatible OPC HDA clients.
OPC UA Historical Access	Modern protocol-based access for OPC UA clients.
Data Exporter	Scheduled bulk export to SQL Server, Azure Data Lakes, Kafka, or CSV.
SQL Query Engine	On-demand T-SQL access to historical data via SQL Server stored procedures. Useful for ad-hoc queries, reporting tools, and SQL-based integrations.

Available Query Functions

The SQL Query Engine provides the following categories of stored procedures:

• Point names—Lists available Data Historian tags and their metadata, including engineering units, data type, and configured value range. Use this to discover what data is available before constructing queries. Also includes variants that can help to filter and find specific points based on defined criteria.

• Raw data—Returns unprocessed historical samples as logged by Data Historian. Typed variants are available for all common .NET data types, which can improve performance when the tag data type is known.

• HDA Analog—Returns aggregated values for numeric tags over a specified time range, using OPC HDA–compatible aggregate types: minimum, maximum, average, time-weighted average, totalized average, and interpolated.

• HDA Analog 5—Runs HDA Analog queries for up to five numeric tags in a single call, making it easier to scale the same aggregate query pattern across multiple points.

• HDA Bool—Returns aggregated state data for boolean (on/off or true/false) tags.

• HDA Bool 5—Runs HDA Bool queries for up to five boolean tags in a single call, making it easier to scale boolean state queries across multiple points.