Skip to main content

Power BI & Consibio Cloud

EJ
Updated

Many of our customers use other 3rd party systems to report internally within their organization. One popular tool is Microsoft's Power BI which enables rich data transformation, automated reporting and ease of use when integrating data further to the Microsoft Ecosystem that many enterprises rely on.

In this short guide, we will give an example of how to integrate data from Consibio Cloud into Power BI so you can work with data in your Microsoft Ecosystem

How to retrieve data from Consibio Cloud into Power BI?

You can retrieve your data using the Consibio Cloud REST API v2 which is our OAS compliant API, that enables to get data from your devices and cloud projects programmatically.

Head over to the documentation site to see all available endpoints and select the one that suits your needs the most.

 

In this guide, we assume you want to know the uptime of a particular device in order to report that inside Power BI. This can be done with the following steps:

  1. Open a new Power BI Report
  2. Allow the new report to receive data from Consibio Cloud Rest API v2
  3. Login to Consibio Cloud via. Power BI and get your Authorization Token
  4. Add a new Black Query for getting the uptime of a device
  5. Visualize the data in your preferred way

Open a new Power BI Report

  • Open Power BI Desktop on your Windows Computer
  • Select "File" and chose new "Report"

Allow the new report to receive data from Consibio Cloud Rest API v2

  • Go to "File" and chose "Options and settings" and select Options
  • Go to the "Current File" Section, chose Privacy
  • Select "Ignore the Privacy levels and potentially improve performance" and press "Done"
    • This step is needed to use the correct authorization method when contacting the Consibio Cloud REST API v2

Login to Consibio Cloud via. Power BI and get your Authorization Token

  • Press the dropdown menu Get data button in the Data ribbon in the Home Menu.
  • Select Blank Query

  • Press the Advanced Editor in the Query ribbon
  • Insert the code from the code block underneath and replace your_username_here and your_password_here with the username and password you use to login into Consibio Cloud
  • Press Done 
let
    url = "https://api.v2.consibio.com/login",
    body = "{
        ""username"": ""your_username_here"",
        ""password"": ""your_password_here""
            }",
    Source = Json.Document(Web.Contents(url,[
        Headers = [#"Content-Type"="application/json"],
        Content = Text.ToBinary(body)])),
    payload = Source[payload],
    auth_token = payload[token]
in
    auth_token

Get Device Uptime in a second Query

  • Select Blank Query from the New source dropdown menu to create another Query (e.g. Query2)
  • Open the Advanced Editor
  •  Insert the code from the code block underneath and replace
    • Device_ID with the Consibio device id found under the Devices section in Consibio Cloud
    • From_Time with the Unix timestamp in seconds from when the uptime calculation should start
    • To_Time with the Unix timestamp in seconds to when the uptime calculation should end
    • Query1 with the exact name of your previous query (e.g. the one to retrieve the auth_token)
  • Press Done 
let
// Specify the parameters
device_id = "Device_ID",
from = "From_Time",
to = "To_Time",

// Construct the URL with parameters
url = "https://api.v2.consibio.com/devices/" & Text.From(device_id) & "/uptime?from=" & Text.From(from) & "&to=" & Text.From(to),

// Make the HTTP GET request
Source = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & Query1]])),
payload = Source[payload],
checkins = payload[checkins]
in
checkins

Here you will be greeted with the resulting uptime for your device in the specified period between the from_time and to_time. For instance, this device has an uptime of 27,9%.

Build your desired Power BI Visuals with the data

After pressing the Close & Apply button, you can now manipulate your Consibio Cloud data inside Power BI and visualize it the way you want to.

For instance, you could make a traffic light or gauge meter, that turns red if your devices uptime is less than you expect. It could also be a simple conditional formatting, that highlights the table background red or green depending on the desired level of uptime.

The possibilities are endless. Power BI offers a wide range of interactive visualization options, including charts, graphs, maps, and gauges, enabling users to explore Consibio Cloud data from different perspectives and uncover hidden patterns or trends.

 

In need of assistance?

There are endless possibilities to integrating data from Consibio Cloud into your most favorite data analyses environment, so do not hesitate to reach out to us at support@consibio.com. Our data integration experts will then help you on the way to harness the full potential of the Consibio Cloud Platform.

Related articles