Querying the Historian Using Power BI (version 24)

Knowledge Base / Version 24 / Client Tools / ODBC Client

updated 6 mths ago

Ensure the ODBC Server is installed/licensed on the Canary Historian and the ODBC Client is installed on the client machine wishing to query the Historian.
Open Power BI and select Get data
Search for ODBC then click Connect

The From ODBC window will appear. Expand Advanced options to type in a SQL query. In this example, we are requesting a daily maximum value over a 1-week span.

select * from data where tag_name like 'HistorianServerName.{Diagnostics}.%'
and time_stamp >= '1/1/2023 00:00:00'
and time_stamp < '1/8/2023 00:00:00'
and aggregate_id = 'Maximum2'
and aggregate_interval = '1.00:00:00'

Click OK.
Another window will appear prompting the user to specify how to connect. If connecting anonymously, select the Default or Custom tab then click Connect. (This requires the Anonymous provider to be enabled within the Identity service.)

If the Anonymous provider is disabled, an API token must be created and used to connect to the Views service. This can be the same token that was used to configure the ODBC client as a System DSN from Step 1.
Choose the Database tab and set the User name to "apitoken" and paste the token into the Password field.
A preview window will appear where the user can then Load or Transform Data.