Gravio Blog
September 5, 2022

[Tutorial] Visualizing Historical Data at the Edge with Microsoft PowerBI with Real-Time Notifications Using Twilio SMS

Tutorial on how to visualize historical data with Microsoft Power BI. We will show how to plot a line chart and display stored CO2 values collected using Gravio as the platform for data collection and writing to CSV. We will also go through how to create a trigger using Gravio to notify in real time through SMS using Twilio and other alternative messaging platforms.
[Tutorial] Visualizing Historical Data at the Edge with Microsoft PowerBI with Real-Time Notifications Using Twilio SMS

Introduction

This article is part of a series to show how easy it is to get into IoT using Gravio in a low code environment. Every article builds on a different use case which anyone may encounter daily and how Gravio can help build solutions in a low code and flexible manner.

We will tackle how to visualize CO2 historical data with Power BI Desktop running on Windows and at the same time, send real time notifications via SMS to notify users when a certain threshold is surpassed. At the same time, we will write all the CO2 values gathered into a CSV file to be used as storage. In an ideal situation, we would use a proper database like MySQL but in this example scenario, we would like to show how writing to CSV will create a very quick method of visualization.

We’ll be sharing other examples of ‘Easy IoT’ in future posts, follow us on Twitter or join our Gravio Slack community to find out when.

Tutorial

Requirements

  • For this tutorial we are using Gravio Studio on Windows
  • Gravio Standard account - sign up here (note: we use Standard Tier Subscription to qualify for  CO2 Sensor bundle)
  • Microsoft Power BI Desktop on Windows (Download from the Microsoft Store)
  • For this tutorial we are currency using the Free Trial version
  • Devices: 1x Gravio CO2 Sensor, 1x Gravio Hub OR 1 x Gravio Dongle
  • Devices to be paired and layers to be set up in Gravio Studio - setup info here

Steps

Part 1: CO2 Data Gathering and SMS Notification

Step 1: Creating an Action for Writing CSV

Launch Gravio Studio and go into your Actions page. In this page, create a new Action and name it appropriately. In this case, we want the Action to write into a CSV file and then send a text if a certain threshold value has been exceeded. So as an example, I have named it “CSVWritePowerBI”.

Next, in the Actions Editor, add a component for “CSV File Write”.

Type out your desired filename and check the “Append” box to enter values in a new line. Set the pre-mapping of the Payload to:

cv.Payload = [DateFormat(Now(), '15:04:05'), tv.Data]

You can change the way you would like to format the date. To do this, you can refer to our documentation and refer to the Date Functions tab.

Now, the Action is ready to write values to a CSV file.

Step 2: Creating a Trigger To Record CO2 Values in CSV


Create a new Trigger in the Triggers tab and set the conditions to receive the value of CO2 readings sent by the Gravio CO2 sensor. Next, set the Action to trigger to be the Action you have just created. In this case, “CSVWritePowerBI”.

You’re all set! Make sure you have the Trigger set to “On” and now you have started recording CO2 values and storing the readings in a CSV file locally.

Step 3: Check Records

To check whether your data is being recorded as planned, you can download the file from Gravio Studio by using the “Explore” function as shown.

Once you have the file, the data gathered should look something like this.

Step 4: Updating Action to Get Real-Time Alert SMS Notifications

After successfully creating your application, you can enhance it by getting real-time alerts when CO2 values exceed a certain threshold. This is to notify the appropriate users that the CO2 value is too high. You can do this entirely without displaying it on a dashboard so that users do not have to constantly check it. To achieve this, you can use the Twilio SMS component from the Actions Editior and add it on as a step from the base Action.

In this tutorial, we have used a Trial Twilio Account with trial credits provided. You will need to have your Account SID, Token and Twillio Phone Number ready for the integration.

Lastly, in the Actions editor, you will need to add a “Filter” software component. This component is to set the threshold before sending an SMS via Twilio. In this example, we have set the threshold of the CO2 value to be more than or equal to 1200ppm. To write this in the expression, you can write “tv.Data >= 1200”. 

For more information on expressions and how you can use them, you can refer to our Gravio documentation here.

Add the last step in the Action, Twilio SMS and input your credentials in the fields. For the message, you can personalize the alert. In this case, we have put:

cp.Message = ‘The current CO2 Value is’ + tv.Data

When the threshold value has been met, you will receive an SMS something like this.

Part 2: Setting up Power BI Desktop

Step 1: Launch Power BI Desktop and Connect Data Source

Setting up Power BI Desktop is very straightforward, if you are using a Windows machine, you can download via the Windows Store and login. For this tutorial we are currently using a Trial Account.

Connect a datasource by selecting “Get Data” on the top navigation bar and select “Text/CSV”. Select the file that you have been recording CSV values with and confirm the connection. Check the values and select “Load”.


Step 2: Create Line Chart and Plot Values for Visualization

Visualize your data the way you want by using one of the many Power BI tools. On the main dashboard, select Line Chart and resize the card. On the right hand side, you can identify your data source connected and check in the checkbox those that you want to visualize on the line chart. In this case, we want to show CO2 values on the Y-axis and Timestamp on the X-Axis.

Step 3: Refreshing the Data Source

The data source also can be refreshed after a certain period of time, however this has to be done manually by using the “refresh data” button as shown. This will help visualize historic data very easily and quickly in a repeated manner.

Summary

That is it! We have finally completed this short tutorial on how to visualize historical data using Power BI and Gravio quickly. This is a very simple and cost effective way on how to use Gravio with various BI tools in the market. There are many more ways and configurations that can be done using Gravio and other BI tools but most importantly, this is done completely at the Edge. Which means, this entire setup can run without being connected to the internet, with an exception to Twilio SMS, which requires the internet because it is a cloud service afterall. Let us know what you think by reaching out to us and we can tailor our content to what you would like to see. Have a great day!

Latest Posts
Taiyo Foods Corporation: Using Gravio and the Lenovo ThinkCentre Nano to Improve Efficiency
Trial Use of an IoT Temperature and Humidity Control Solution to Maximize Production Efficiency in Nori Processing
Tuesday, January 31, 2023
Read More
7 Ways in Which an Edge IoT Platform Such as Gravio Creates Value for System Integrators
Here's a list of 7 Ways in which an Edge IoT Platform Such as Gravio Creates Value for System Integrators
Friday, January 20, 2023
Read More