GENESIS V11 - SQL Query Engine startup

GENESIS V11 - SQL Query Engine startup

Info
This article explains how to start using the SQL Query Engine functionality with GENESIS V11.

For information about the old SQL Query Engine from version 10 check this article: https://www.fasupportme.com/portal/en/kb/articles/iconics-suite-hyper-historian-sql-query-engine-examples

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

Notes
The SQL Query Engine requires GENESIS version 11.05 or later.

SQL Query Engine installation

By default the SQL Query Engine is not included even in a full GENESIS installation. If you want to install it, make sure to switch to the Advanced tab of the installer and select it. If you are modifying an existing installation or performing an upgrade from an earlier version select it as well.

Warning
If you only want to add SQL Query Engine to your installation without touching other components, select the SQL Query Engine checkbox but don't unselect other components. When you modify the installation and unselect something, that component can be uninstalled in the process.


Proceed until you get a message about a successful installation. If your installation is unsuccessful check the logs for more information.

System configuration

After installing SQL Query Engine on an existing server run the Configure System utility (backup your project and configuration database first!):


Warning
Follow all the steps carefully to make sure you don't overwrite your project and configuration database and maintain desired security settings. If you never used the Configure System utility before, read the description in online documentation first: https://documentation.iconics.com/v11/Content/Installation-Upgrade-Guide/starting-system-configuration.htm
In the Configuration Settings step, make sure to select the Automatically upgrade all configurations option:


Complete the configuration for SQL Query Engine stored procedures to be added to your database.

Alert
If you didn't perform a fresh GENESIS installation but added SQL Query Engine to an existing application, it is possible for the Configure System to return an error:



After your configuration is completed, make sure all GENESIS services you need for your application are running and adjust their autostart settings if necessary (the utility may have changed them). To be able to access Data Historian data, ensure the GENESIS Data Historian Query Interface service is running.

Testing the SQL Query Engine with basic queries


If the Configure System utility completed all the operations without errors, you should be able to find the SQL Query Engine stored procedures in the master database of your SQL Server:


Use some basic queries to make sure you are able to read data. You can find query examples here: https://documentation.iconics.com/v11/Content/Data-Historian/SQL-Query-Engine/querying-historical-data-with-sql-query-engine.htm



Alert
If your query result is empty (0 rows) even though historical data should be available, there are two things you should check as described in the online documentation here: https://documentation.iconics.com/v11/Content/Data-Historian/SQL-Query-Engine/using-sql-query-engine-with-security.htm

1. If you have Security enabled it may be necessary to add optional username and password arguments to your query - follow the chapter To query the SQL Query Engine with GENESIS Security enabled
2.  FrameWorX Server Location settings for the SQL Query Engine may be incorrect - follow the chapter Applying FrameWorX Server Location Changes


    • Related Articles

    • Iconics GENESIS V11 - Installation guide

      This article will guide you how to prepare your operating system and database to install Iconics GENESIS Version 11. If you need help with installation of an older version of Iconics, please follow this article instead: ...

    • Iconics Suite - Data storage in Hyper Historian Logger

      Summary: this document describes difference in setting in Hyper Historian Logger. Storage Limit Condition, Actions are described. Hyper Historian Logger - Delete Old Files First test was done using the 'Delete Old Files' option. Condition is Minimum ...

    • Configure GOT2000 MES Interface function with MS SQL 2017

      This article will show You how to configure GOT2000 panel to log data to MS SQL 2017. Prepare before configuration - Install Microsoft SQL Server Express 2017 (called MS SQL Server). - Install Microsoft SQL Server Management Studio. - Install DB ...

    • Using Context Time variable to display data from the past

      This article explains how to use the Context Time variable available in Data Historian (or Hyper Historian in v. 10.x), so a GENESIS display can present data from a selected moment in the past. The display file and asset configuration used in the ...

    • GENESIS V11 - MQTT connection to iQ-F PLC (FX5-ENET)

      This article explains how to read data from an iQ-F series FX5-ENET module to GENESIS V11 using a dedicated decoder. A sample GX Works3 project used in this example is added as an attachment. Precautions The MQTT communication function is officially ...