Connecting Canary to Power BI (version 23)

This article will take you through the process of reading data out of the Historian and into Power BI using Canary's web API. In the 2 examples below, we are connecting to the anonymous endpoint (55235) on the local machine.

If wishing to make a secure connection (55236), the Views service must be configured to use a valid certificate. Power BI does not trust the self-signed certificate Canary generates by default and throws an error when attempting to connect.

Once a valid certificate is installed on the server, the Views service can be pointed to the trusted certificate by navigating to the Views tile>Configuration>Endpoints screen and modifying the certificate information.

Additionally, if making a secure connection, it will be necessary to create an 'accessToken' within the Canary Admin>Views tile>Configuration>Tokens. The accessToken may be used in the url such as this example...

https://ServerName:55236/api/v2/getTagData2?accessToken=4765d103-8ade-4b51-8c9c-89de6d9ec2c0&tags=ServerName.{Diagnostics}.Sys.Memory%20Physical

...but it is recommended that the token be passed in using a header parameter so that it is not within the URL itself. By selecting the 'Advanced' option, the user can build the parts of the URL and add an 'Authorization' header and setting it equal to 'Bearer <accessToken>'.

There are 2 methods for passing in tag names. The first example uses the 'tags' parameter and specifies the exact tag names. The second example uses a combination of the 'path', 'deep', and 'search' parameters. This is more useful if requesting data for multiple tags. In Example 2, we are pulling data for any tags that contain 'Memory' in the {Diagnostics} DataSet. For more description on these parameters visit readapi.canarylabs.com.

Example 1

• 'tags' =
  • localhost.{Diagnostics}.Sys.Memory Physical
  • localhost.{Diagnostics}.Sys.CPU Usage Total
• 'startTime' = day - 7d
• 'endTime' = day
• 'aggregateName' = TimeAverage2
• 'aggregateInterval' = 1d

http://localhost:55235/api/v2/getTagData2?tags=localhost.{Diagnostics}.Sys.Memory%20Physical&tags=localhost.{Diagnostics}.Sys.CPU%20Usage%20Total&startTime=day-7d&endTime=day&aggregateName=TimeAverage2&aggregateInterval=1d

Example 2

• 'path' = localhost.{Diagnostics}
• 'deep' = true
• 'search' = Memory
• 'startTime' = day - 7d
• 'endTime' = day
• 'aggregateName' = TimeAverage2
• 'aggregateInterval' = 1d

http://localhost:55235/api/v2/getTagData2?path=localhost.{Diagnostics}&deep=true&search=Memory&startTime=day-7d&endTime=day&aggregateName=TimeAverage2&aggregateInterval=1d

Requesting the Data

1. Ensure the 'Http - Anonymous' endpoint (55235) is enabled by opening the Canary Admin, navigating into the Views tile, then selecting the Configuration tab at the bottom.
   
2. Select the 'Access' tab on the left side to ensure the 'ANONYMOUS LOGON' is listed in the 'ALLOW' list. By default, it is listed.
   
3. Open Power BI and select 'Get data'. Choose the 'Web' source.
   
4.  Type the API request into the 'URL' field. (We are using Example 1
   http://localhost:55235/api/v2/getTagData2?tags=localhost.{Diagnostics}.Sys.Memory%20Physical&tags=localhost.{Diagnostics}.Sys.CPU%20Usage%20Total&startTime=day-7d&endTime=day&aggregateName=TimeAverage2&aggregateInterval=1d

Transforming the Data

1. Power BI will attempt to transform the data into what it thinks is a usable format. These 'APPLIED STEPS' will appear on the right-hand side. Remove 'Changed Type' and 'Expanded data' from this list by clicking the 'X' next to each item.
   
2. Select 'Record' under the 'data' column'.
   
3. Convert the Record into a Table by selecting the 'Into Table' button in the top-left corner.
   
4. Click the button in the 'Value' column and select 'Expand to New Rows'.
   
5. Repeat this process again by clicking the  button in the 'Value' column then selecting 'OK'.
   
6. Right-click on the 'Value.t' column header and select 'Change Type'. Choose 'Date/Time/Timezone'.
7. Repeat this process for the 'Value.v' column. Set its type to 'Decimal Number'.
8. Select 'Close & Apply' in the top-left corner to apply these changes.

Visualizing the Data

Once the data transformation is complete, the user will be presented with a blank canvas to build a report.

1. Select the 'Clustered column chart' from the 'Visualizations' menu.
   
2. Expand the 'getTagData...' table in the 'Fields' column to show 'Name', 'Value.t', and 'Value.v'.
   
3. Click-and-drag 'Name' into the 'Legend' field, 'Value.t' into the 'Axis' field, and 'Value.v' into the 'Values' field.
   
4. Click the down-arrow in the 'Axis' field and select 'Value.t'.
   
5. Finished!