About Data Retrieval

The data retrieval subsystem receives SQL queries from clients, locates the requested data, performs any necessary processing, and then returns the results.

• Implementation of a SQL Server data provider – Determines whether the requested data resides in normal SQL Server tables or in the history blocks
• Retrieval service – Responsible for extracting the requested data from the history blocks and presenting to the Historian Server OLE DB provider as virtual history tables
• Set of SQL Server extensions – Implemented as columns in the history tables. You can use these extensions to specify the nature of the rowset to be returned, such as the number of rows returned, the resolution of the data, or the retrieval mode.

You can manage data retrieval with a variety of tools. Historian Client tools, such as Trend, are addressed in the Historian Client class.

Microsoft SQL Management Studio tools are addressed in this class.

Data Retrieval Features

Some of the main features of the data retrieval subsystem are:

• Allows you to include all tag types in the same query when retrieving from the history table. You can submit any combination of tags in a query.
• Supports both fixed length and variable length strings
• Returns live data from the active image independent of the tag types in the list, unless an ORDER BY clause is applied to the query
• Uses the Win32 FILETIME type for all internal time computation and manipulation. The resolution of FILETIME is 100 nano-seconds. The resolution exposed in queries depends on the version of SQL Server used.
• Handles all times internally as absolute time (UTC). Conversions to and from local time are handled going in and out of retrieval so the external interface is local time.
• Supports non-real time data (for example, store-and-forward data or data imported from a comma-separated values (CSV) file)
• Supports retrieval of different versions

History Blocks: An SQL Server Remote Data Source

Remote data sources are data repositories that exist outside of an SQL Server database file (.MDF). Microsoft sometimes refers to these types of data sources as non-local data stores. For Historian Server, a remote data source is the set of history block files. OLE DB technology allows you to access data in any remote data store. This access is accomplished though a software component called an OLE DB provider.

Extension (Remote) Tables for History Data

Some of the history tables are rowset representations that provide a means for handling acquired plant data. These tables are not part of normal SQL Server functionality. A normal SQL Server table stores data directly in the database data device file (.mdf). An extension table, however, presents data as if it were a real table, but it does not physically exist in the data device. An extension table is a logical table that is populated from other types of data files; thus, the data is stored remotely from SQL Server. In the case of Historian Server, the data files are the history blocks generated by the storage system.

The extension tables are:

1. AnalogSummaryHistory – (INSQL.Runtime.dbo.AnalogSummaryHistory)
2. History – (INSQL.Runtime.dbo.History)
3. HistoryBlock – (INSQL.Runtime.dbo.HistoryBlock)
4. Live – (INSQL.Runtime.dbo.Live)
5. StateSummaryHistory – (INSQL.Runtime.dbo.StateSummaryHistory)
6. StateWideHistory – (INSQL.Runtime.dbo.StateWideHistory)

The AnalogHistory, DiscreteHistory, StringHistory, AnalogLive, DiscreteLive, StringLive, AnalogWideHistory, DiscreteWideHistory, StringWideHistory, and v_SummaryData tables are provided for backward compatibility.

The AnalogHistory, DiscreteHistory, StringHistory, and History tables are the only tables that can be updated. The remaining tables are read-only.

Query Syntax for the Historian Server OLE DB Provider

The most common Historian Server query is a SELECT statement.

SELECT select_list FROM table_source

WHERE search_condition

[ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]

There are four variations for issuing a SELECT statement to the Historian Server OLE DB provider to retrieve history data.

• Four-Part Naming Convention
• Historian Server OLE DB Provider View
• OPENQUERY Function
• OPENROWSET Function

Four-Part Naming Convention

The linked server name is simply a name by which the Historian Server OLE DB provider is known to the Microsoft SQL Server. For a query to be passed on to the Historian Server OLE DB provider, you must specify the linked server name, the catalog, the schema, and the extension table name (object-name) as part of a four-part naming convention.

For example, this is a query to retrieve data from the History extension table in the Historian Server OLE DB provider:

SELECT * FROM INSQL.Runtime.dbo.History WHERE TagName = 'SysTimeSec'

AND DateTime >= '2012-09-12 12:59:00'

AND DateTime <= '2012-09-12 13:00:00'

The first line of the query (INSQL.Runtime.dbo.History)contains the four-part naming convention described as follows:

Historian Server OLE DB Provider View

Microsoft SQL Server views have been provided to access each of the extension tables, eliminating the need to type the four-part server name in the query. These views are named the same as the provider table name. For example:

SELECT * FROM History

WHERE TagName = 'SysTimeSec'

AND DateTime >= '2012-09-12 12:59:00'

AND DateTime <= '2012-09-12 13:00:00'

Note: Backward compatibility views are named according to the v_ProviderTableName convention.

OPENQUERY Function

You can use the linked server name in an OPENQUERY function to retrieve data from an extension table. The OPENQUERY function is required to retrieve wide table data. For example:

SELECT * FROM OPENQUERY(INSQL, 'SELECT * FROM History

WHERE TagName = "SysTimeSec"

AND DateTime >= "2012-09-12 12:59:00"

AND DateTime <= "2012-09-12 13:00:00" ')

The following example retrieves data from a wide table:

SELECT * FROM OPENQUERY(INSQL, 'SELECT DateTime, SysTimeSec

FROM WideHistory

WHERE DateTime >= "2012-09-12 12:59:00"

AND DateTime <= "2012-09-12 13:00:00" ')

SQL Server sends the quoted statement, unchanged and as a string, to the Historian Server OLE DB provider. Consequently, only the syntax that the Historian Server OLE DB provider can parse is supported.

You should supply the datetime in an OPENQUERY statement in the following format:

yyyy-mm-dd hh:mm:ss.fff

Wide Table Transformation

Wide Table Transformation is defined as the ability to provide the same historical data in two different table formats: one in Narrow table format, and one in Wide table format, for each of the basic data types.

For example, you can view the tables WideHistory and History. These two formats are appropriate for very different uses.

Data for three tags from the Wide Table typically looks like this:

The same data from the Narrow Table looks like this:

The data is physically stored only once.

The narrow format is best for most relational query use. The wide format is best for most engineering uses..