SQL Query Engine Quick Start

Hyper Historian logs data to a proprietary database. While you can use the TrendWorX64 and TrendWorX32 Viewers to see and edit your logged data in a graphical format, you may wish to create reports for the logged data or edit data in bulk. For this reason, Hyper Historian comes with a SQL Query Engine that allows you to use common SQL Data Manipulation Language (DML) queries to retrieve and edit data. For additional information, refer to the SQL Query Engine topic.

 

 

Note: When you edit logged data, Hyper Historian keeps the original data and marks the changed data as edited.

Hyper Historian Linked Server

First, open SQL Server Management Studio to see the providers, which are automatically installed and configured together with Hyper Historian.

  1. Open SQL Server Management Studio Express by going to Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express.
  2. When asked, fill in credentials to connect to your local SQL server.
  3. In the Object Explorer on the left, expand Server Objects.
  4. Double-click on Linked Servers to expand it. You should now see a linked server called HH2, which stands for Hyper Historian.
  5. Expand the Providers folder. You will discover a provider called ICONICS.HHOleDbProvider, which is necessary for your queries to work.

Figure 1 - Hyper Historian Provider

 

Building SQL Queries

In this example, you will use a simple SQL command that reads the list of all tags available in one Hyper Historian Logging Group. On top of that, you will create another simple query to retrieve logged data related to the selected tag.

 

You’ll use the default samples Logging Group:

 

Figure 2 - Logging Group

  1. Click on the New Query button, located in the standard Toolbar of Microsoft SQL Server Management Studio Express.
  2. In the SQL Editor that opens on the right, write the following query:

select * from openquery(HH2, 'select *

from Signals.TAGS')

  1. Click the Execute button in the SQL Editor Toolbar. You will see a result for the query that is like the figure below.

Figure 3 - Getting Tag Names

 

NOTE: If you get no results for the query, you probably have no logged data. Open Hyper Historian (with the default configuration) and go into runtime mode. Then repeat step 3.

  1. Now select one of the tag names to query. On the next line of the query editor add the following query, which will list all values for the specified tag:

select * from openquery(HH2, 'select *

from Signals.RAWDATA where TAGNAME

= ''Sine''')

 

Figure 4 - Getting Logged Data

SQL Clients – MS Excel

The Hyper Historian SQL Query Engine can be used in other containers.

  1. Open MS Excel by going to Start -> Programs -> Microsoft Office -> Microsoft Office Excel 2010.
  2. In the Data tab, click the From Other Sources button.
  3. Select the From Data Connection Wizard option.

Figure 5 - Creating New Data Connection

  1. Choose the Other/Advanced data source, and click Next.
  2. Select ICONICS HyperHistorian SQL Query Engine, and click Next.

Figure 6 - Selecting Hyper Historian SQL Query Engine

  1. On the Connection properties table, select the Catalog and Schema name, which correspond to the Hyper Historian logging group names.

Figure 7 - Selecting Logging Group Name

  1. A list of tables appears as shown in Figure 8. You can select table TAGS (display all tags in HH configuration), RAWDATA (display logged data for selected logging group) or QUERYSETTINGS (display setting for the SQL query). Select one and click Next.

Figure 8 - Selecting Table

  1. The Data Connection Wizard window appears. Configure the name and description for your data connection file. Click Finish.

Figure 9 - Data Connection Wizard

  1. The Import Data properties dialog appears. You can change properties to your liking or leave them as they are. Click OK when you are ready to continue.

Figure 10 - Import Data Dialog

  1. The result of the query will look like the following:

Figure 11 - Logged Data in MS Excel

 

NOTE: You can use Hyper Historian SQL Query Engine in the BizViz ReportWorX application, too, for creating scheduled reports.

 

See Also:

SQL Query Engine