Querying the Historian Using Power BI (version 23)

Knowledge Base / Version 23 / Client Tools / ODBC Client

updated 1 yr 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'.

If security is enabled within Views, select the 'Windows' tab and specify the user account. (*Note, this user account must have permissions configured in Views and will override the user account provided in the ODBC Data Source Administrator if one is configured there.)
A preview window will appear where the user can then 'Load' or 'Transform Data'.