The Canary Historian ODBC is used for generating reports using SQL queries and populating data from the Enterprise Historian into SQL databases. It is a server/client based architecture in which the ODBC server component must be installed on the historian machine itself and the ODBC client installed on any machine wishing to make queries against the Canary database.
Licensing is server based and can be managed through the Canary Admin>License Tile.
This article will take you through the basic steps of installing, licensing, and making a simple query using the ODBC client.
Step 1: Installing the Server Component
Using the Enterprise Historian install, select the Canary Historian ODBC icon from the list as a feature to install along with the Canary Administrator and Canary Enterprise Historian.
Step 2: Licensing the Historian ODBC
Once installed, open the Canary Admin and select the License Tile. From here you can choose which applications you wish to license. If the server has internet connection fill out the necessary information underneath ADD LICENSE THROUGH INTERNET and select "Historian ODBC". Otherwise, you may license your product by visiting https://www.canarylabs.com/en/support/license.
Click GET LICENSE. This completes the server component of the install.
Step 3: Installing the Canary ODBC Client
Using the ClientApps install, select HistorianODBC from the list as a feature to install on any client machine that will query the historian.
The client installs as a system DSN (Data Source Name). To configure, open the ODBC Data Source Administrator (32 or 64-bit, depending on the application using the Canary ODBC client) and select the System DSN tab. (Control Panel>System and Security>Administrative Tools>ODBC Data Sources>System DSN tab). Choose the appropriate client and click Configure.
The Data Source Name defaults to CanaryODBCClient. The server IP is the IP address or machine name of the historian machine that will provide the data. If the local machine is the historian, leave this as the loopback address or localhost. Otherwise, change it to the IP address or machine name of the remote historian.
The server port defaults to 55260. If changed, the ListenPort of the historian server must also be changed and the service restarted.
The connection timeout is left as 0 and the login timeout is blank.
Click OK. This completes the installation component of the Canary ODBC Client.
Step 4: Querying the Historian
Open your SQL test client and start a new connection. This example uses Microsoft SQL Server.
1. Add a new linked server.
2. Give your linked server a name. In this example we are using "CANARY".
3. From the Provider drop-down menu select "Microsoft OLE DB Provider for ODBC Drivers".
4. For the Data source type in "CanaryODBCClient".
5. Click "OK". No other fields need modified.
There are four tables to choose from: aggregates, currentdata, data, and tags.
For a simple test of connectivity use the following query:
select * from openquery(CANARY, 'select * from aggregates')
This query will produce a list of all available aggregates and their descriptions.
This is another example that uses Microsoft Query which can be found by opening Excel and clicking on the Data tab then selecting Get Data>From Other Sources>From Microsoft Query.
Select the CanaryODBCClient data source and uncheck the box "Use the Query Wizard". Click OK.
There are four table to choose from: aggregates, currentdata, data, and tags. Select the data table and click Add, then Close.
Click the SQL button and paste in the following command assuming you have the dataset TestData.
select * from data where tag_name like '%TestData%'
and time_stamp > '06-01-2019 00:00:00'
and time_stamp < '06-01-2019 00:01:00'
The '%' sign acts as a wildcard so that any tag with TestData embedded in it will be pulled. The results should appear similar to what is shown below.