1

Connecting Canary to Power BI

This article will take you through the process to read data into Power BI using Canary's web API. In this example, we are connecting to the anonymous endpoint (55235) on the local machine. The following parameters will be used to generate the url for the API request:

  • '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/getTagData?tags=localhost.{Diagnostics}.Sys.Memory%20Physical&tags=localhost.{Diagnostics}.Sys.CPU%20Usage%20Total&startTime=day-7d&endTime=day&aggregateName=TimeAverage2&aggregateInterval=1d

Requesting the Data

  1. Ensure the 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. http://localhost:55235/api/v2/getTagData?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!  
Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular