![[Tutorial] Visualizing Historical Data at the Edge with Microsoft PowerBI with Real-Time Notifications Using Twilio SMS](https://assets-global.website-files.com/5ba3150a54f21cfe3f02b031/6310215481e79cb12c45994e_Header-Power-BI.png)
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.
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.
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.
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.
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”.
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.
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.
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!
