Weidmüller AC: Azure IoT Cloud

Welcome to a new video on my Weidmüller Automation Controller series, and today we are going to look at cloud services how you can store data in the cloud and how you can visualize it and when I talk about cloud I specifically talk about Microsoft Azure so how you can use the various services in Microsoft Azure and how you can use the reporting tool of Microsoft which is Power BI to build your own visualization and then create various reports and dashboards out of that visualization. I think this episode is a perfect continuation to things that we have seen in the past because we have looked at various ways how you can store the data how you can send the data and how you can visualize data we have seen the dashboard how you can create visualizations locally We have also talked about how you can store data locally on the files how you can send it off to different servers and with this episode we are looking at sending these specifically to cloud services which are designed to store data and also use the visualization that are built around these cloud services With these cloud services you can have one central server or well in this case the cloud where all the information is stored so all your Automation Controllers can feed into the same Azure account where you start collecting the data and you start visualizing it and analyzing it in Power BI Of course it doesn’t mean that you have to use Azure maybe you can use a combination of dashboards sending data across your Automation Controllers and maybe sending the aggregated data to Microsoft Azure and as we are going to see later on I think this Azure and especially Power BI works very well especially if you have aggregated data it’s maybe not so much as like you know real time and a lot of time series data definitely the Azure cloud can handle the the amount but what you can do in Power BI is i think it’s a little bit more suited for you know sort of like a data crunching and analyzing and then and looking at more aggregated data. But I could be wrong maybe it’s just done to the fact that i’m not really an expert in Power BI We are going to cover a lot today so i think I better get started and I think what i’m going to do is i’m going to give more an overview of how this whole process is built up what are the different steps and the various solutions that you need to use to get from the data that you have on the Automation Controller all the way to the dashboards that you have in Power BI or your Power BI account. Just like with every session we are going to start from Node-Red and we are going to look at the various special Azure IoT Hub nodes that we are going to use and how we can register new devices and how we can send data to the Azure IoT Hub of course we are going to use the various configuration steps that you need to do in Azure so for example we need to create an IoT Hub we also need to create Stream Analytics Jobs that will process the data that is in the IoT Hub and present it in tables that the Power BI can read and then we will start creating reports in Power BI and probably we are going to create a few different reports for the reasons i’m going to go into the details a little bit later And then the final step is when once you have all these various reports in Power BI you can merge them together under your workspace into various dashboards and that will be the end of the video once you have the data on the Automation Controller and you can pipe it all the way through to bi dashboards Let’s start from the beginning and let’s look at the example flow which actually is going to be fairly simple but before we do that let me just filter on the various Azure nodes that we have on the automation controller this is something that comes out of the box so you don’t have to install any special component and out of these we are going to basically just use three of the four nodes We are going to use the Azure IoT Hub that’s the node that we use to send data into the IoT Hub next we are going to use the IoT Hub Registry where you can register new device and we also have an IoT Hub Receiver which is pretty much like a node where we can receive messages from the IoT Hub like if something new get created and once you download this example flow and if you click on this top note you are going to get some additional help here and this is where I wanted to leave some of the links that I have also used and also here in the Useful Links that I used to also follow how you create some of these resources

in the Azure portal that i’m going to talk about in a few minutes To understand these flows first i have to go to the Microsoft Azure which is portal.azure.com and once you log in with your credentials then what I usually use is I click on this all the All Resources so that’s going to show you everything that you have created under Azure and actually we only have to create a few things so it’s not going to be that complicated this Log Analytics Workspace is actually not required I was just playing around with some settings so what we really need is we need the Azure IoT Hub and you can think about the IoT Hub as some sort of data storage so we are going to send all our data into this IoT Hub and that’s going to store like measurements in our case i’m just going to send in some weather data because that’s very easy and nice to visualize in reports and i’m also going to send in various data about the state of the switches and on-time counters that i’ve also talked about in previous videos and everything is going to be stored in the IoT Hub once we have the IoT Hub up and running we need to get the data out of this IoT Hub into a format that Power BI can understand and within Microsoft Azure this is done by the Stream Analytics Job so in the Stream Analytics Job you specify that I want to read the IoT Hub I want to get the the readings or the records out of this Hub and the reason I have two jobs because i’m just using two different data for this demonstration purposes i’m going to use some metadata and i’m also going to use these switch states and once we have done that that is going to create some tables that we can read in in Power BI and from that we can start creating you know various visuals within Power BI That is probably enough for the introduction and actually let’s start with the IoT Hub so as I mentioned here under Useful Links you can find the first link here which explains you how you set up an IoT Hub and how you also set up the Stream Analytics Jobs what we need here really in the first place is the IoT Hub and actually it is very simple to set up as you can see you have to provide the name i’ve given name as node-red and and yeah actually that’s pretty much it you don’t really have to provide an awful lot here you just follow the documentation that I left in the link and also keep in mind that Microsoft Azure is not free you can do a lot of stuff for free everything that i’ve shown here i did by setting up an account and i’m using the free pricing tier which definitely has some limitations so you probably want to study the various pricing schemas and you know what they offer in terms of how many devices you can use what is the data retention policy and decide what is suitable for you and of course how much it’s going to cost you on a monthly basis and there is a lot of stuff that you can set up in the IoT Hub for example you can you set up diagnostics you can have various events if something goes wrong then it gives you a notification but to get started just creating an IoT Hub is fine so one thing that you would need to remember is of course what is the the name of the IoT Hub and then what is your host name so the host name is something that we are going to use later in the settings but the other thing which we are going to do is we are going to create IoT devices and actually you can create IoT devices here on the Azure portal but it is also possible to create them from Node-Red. And I actually I created them from Node-Red. So as you can see i’ve created a few of them we are only going to use the weather the pump1 and the pump2 at this point the other thing we will need to use in the Settings group click on Shared Access Policies you select the iothubowner you click on this one and then you get a few settings here so you’re getting the primary key secondary tree Connection String Primary Key this is something that we would again need to use in Node-Red so all you need to know about the IoT Hub at this point that this is our cloud data storage so now I go to the flow and what you can see here on the top is this is where you can register various devices and if you double click on the payload here you can see that you specify a device id so let’s say i you know i’ve the last thing that i’ve specified is a pump2 and so you can if you want to register the device you just change this one to hatever name that you want and then you click on the inject node and that is going to create your device and then the log is going to give you some ids that we are going to use later on

And within the Azure IoT Hub registry you have to provide a connection string which has a hostname and a Shared Access Key and once you have created your devices you can send data in to the Azure IoT Hub node and here you don’t have to provide a Connection String you just provide the hostname here which is same as what i’ve just mentioned before there is a certain format how you need to send the data in which you can see here so within the payload you have to send in an object which has a device id and that’s the pump2 that you just specified up here when you created a registry it has a key and that’s the key that you are getting in the message when you register the device so when you register the device definitely go into the debug node and then take a note of that key but of course if you forget to do that you can go back to the Azure portal and you can go to the IoT Devices and then click on the device and that’s going to give you another screen where you can get to the key So you have the key here you have the protocol set to amqp and then you also have a data attribute And within that data attribute you send your data in as an object so what I have done in my case is if you look at this debug as you can see it generates a data which has an uptime value and it also has a state and whenever the data goes into this Azure IoT Hub i’m also adding two more attributes i’m adding a latitude and the longitude attribute so what I send in a data is an object which has an uptime attribute a state attribute a lat and a long attribute and of course it has all of these and this is the format that you always have to send in so it always have to have these attributes and within the data you can send in any object with any number of attributes and that is all going to be handled within the IoT Hub this is why I said it doesn’t really have a format like doesn’t have a fixed number of fields that you can set in everything gets stored and accepted and I am doing pretty much the same thing on the second half of the flow so i’m sending the data in it also is going to send in uptime and the state and in here i’m also going to add the latitude and longitude this is going to be different coordinates and of course this is the second device so this is pump 2 which has a different key so I just registered pump1 and the pump2 on up here and also taking note of the keys that I have entered sorry not here but here on this function node and the rest of the flow is actually very very similar to what we have done previously so i’m getting two variables from the Automation Controller the out2 and the out9 these are the two outputs and I run this through this count node so it counts how many seconds this output has been on and when it’s getting switched on or switched off it sends out a message with this format and that’s what gets sent to the Azure IoT Hub with the additional coordinates and actually that’s the end of the flow as you can see we are sending two device data in pump1 and pump2 and yeah and we have an example flow here how we can register a new device but once you have registered the device you don’t have to use these. And actually this is not all since I wanted to do a little bit more rich reporting and I wanted to use a few more data i’m also using my personal Node-Red server to send in some weather information so temperature and humidity readings because those are nice changing values that can be visualized on reports and especially i’m using that because i wanted to show how you can process various data and store them in the IoT Hub so as i mentioned here this pump1 and pump2 is sending uptime state latitude and longitude and my weather device is sending uh temperature humidity and rain measurements so completely different number of fields and different type of fields And with this i’m back at the Microsoft Azure portal and as you can see this is the IoT Hub that i’ve just talked about before and pretty much the only thing I can do here is I can look at the traffic how many messages are received by this IoT Hub and you know how many got processed and you know how many IoT devices I have and because i’m using the free tier i have a daily message limit of 8 000 messages The next thing we have to do is we have to create new Stream Analytics Job so either on the home page or for example here you can click and create a new Microsoft Azure Object so if I start typing stream analytics job this is how you create a stream

analytics job it tells you what it is and then you click on create and again you follow the documentation that i’ve linked in the Useful Links and then it’s going to tell you how you create a Stream Analytics Job and you have to follow documentation almost the same as it is on the Microsoft portal but of course i’ve made some changes so let’s see what I have done one thing that we need to understand and the reason I have two stream analytics job because as I said within the IoT Hub you can store any data any number of fields any number of attributes type of attributes but we need to convert it into tables so we need Stream Analytics Job which is going to filter the data that we have in the IoT Hub and then basically you know pick out the various fields so i’ve created two as I said I have some weather data that’s is processed by the PowerBIVisualizationJob and also the pump data which is processed by PowerBIPumpJob so let’s look at the PowerBIVisualizationJob first and if you follow the guide it shows you how you create the main attributes what is the name of the Stream Analytics Job and it also tells you how you create the input you need to create an input stream which comes from the IoT Hub actually there is not much within this input stream so it’s you know fairly simple settings i just followed the guide and you have to also create an output stream which is going to be our Power BI stream and also here you just have to specify some values what would be the name of the data set what would be the name of the table and you have to also authorize your Azure account to use your Power BI and the last thing that you create is you create a query and this is where i’ve made some modifications from the standard guide because what I really need to do here is I need to separate my various devices out from each other. My weather device have different type of fields and my pump device have different type of fields and that’s what as I said this is why we need separate Stream Analytics Jobs and that’s what we can customize here What you basically specify here let’s start from the bottom is first of all I want to filter the data in my IoT Hub where the ConnectionDeviceId is weather this is the device id that you used in our flow when you register the device sorry not here but here in the inject node so this is the device id and this is the same device id that you are using in your Azure IoT payload so the device id And here what i’m telling to Azure that for this Stream Analytics Job i’m only interested in the data that is sent by the device id and the FROM section is my input stream which comes from the IoT Hub the output or the INTO is my BI stream which is the output stream and up here in the SELECT statement I specified each of the fields that I wanted to basically transfer from the IoT Hub into the bi table stream and first of all I wanted the EnqueueTime so this is the time when the message was received by the IoT Hub and I just renamed it to EventDate and temperatures temperature humidity as humidity rain as rain in the preview window I can already see some data so this is the raw data which is stored in the IoT Hub you can see it has a lot of different other things like what was the queue date what was the process date and some other objects in the IoT Hub but if I test the query you can see that the data gets selected so it has an EventDate Temperature, Humidity and Rain and nothing else and I think it’s going to make a little bit more sense if I switch to the other Stream Analytics Job so I go back to my Home and the pump job and pretty much everything is the same on this pump job so it also have an input stream and it has an output stream and in the query i’m doing almost the same as before so again i’m filtering on the device id pump1 or pump2 the input stream is well it has a different name but it’s the same IoT Hub the output obviously goes to a different table and i’m using you know different fields here so i’m using still the EnqueuedTime as an EventDate I have the uptime the state i also wanted to include the device id for Power BI because obviously this table is going to contain readings of multiple pumps or the data for multiple pumps and I have the latitude and longitude Here so as you can see uptime,state, lat and long are the various attributes that i’m sending in from node-red and in here I just took the opportunity to rename these and use capital letters

and fully spell out latitude and longitude if I test this query then we can see that the result is showing you know pump1 and pump2 data and of course it’s not showing the weather data because we filtered on these various device ids and actually that’s pretty much it. So the only thing that you have to make sure that your Stream Analytics Jobs are running and also that your IoT Hub is active and then that’s pretty much the setup part of Microsoft Azure The next thing that we are going to do is we need to download Power BI and you can download it from the Microsoft website or if you have a Windows 10 computer you can just download it from the Microsoft Store I’ve already created one report but what you do is you start creating a new report and when you are creating a brand new report you click on Get Data and you are going to be presented with a list of services that Power BI can use and you can just use Power BI DataSets so you connect and what you’re going to see here you can see the two DataSets that you have defined in Azure as you can see PowerBIPumpDataSetNew and PowerBIWeatherDataSet and if I go back to my Stream Analytics Job and you go to the output and you open this up this is the DataSet that you have specified here and you have to think about these data sets like an online Microsoft Access table or Microsoft Excel table and the reason I wanted to stress this one because it is important that once your data set is created then its structure is fixed this is why my data set is called a PumpDataSetNew because obviously originally I had a PumpDataSet and I was missing a couple of fields and I actually had to create a new data set because you can’t to make changes to an existing data set I want to recommend that you really think about what type of reports you need to build and what data you would need for those reports and then make sure that you are feeding that data in to the IoT Hub because unless you have the data in the IoT Hub you won’t be able to get that data into the data sets and use them in the Power BI Also what I have found is most of the modeling functions that you have with Power BI if you work with Power BI you probably know that you can go into the table view and you can create new fields and everything and all of these are not available when you are using Power BI DataSets so if you need some calculated data or calculated fields you most probably better off if you are doing it in Node-Red and sending that data in already in the IoT Hub One more limitation when you’re using Power BI DataSets then in a regular report when you are using various tables excel documents or database tables you can use multiple tables but with a Power BI DataSet you can only use one data set per report i’ve actually created one report for the weather data one report for the pump data each have different visuals and i’m combining those visuals in a dashboard so this is how it all comes back together onto the dashboard In Power BI the options you have with the different visuals as you can see here and the fact that you can create other measures besides the the fields you have in the dataset The language to create new measures called the DAX database language There is a lot of potential in there, I think I’m just going to really scratch the surface by showing you a couple of things that you can do and you pretty much just need to play around and then try to figure out how you can best visualize the data that you have What I have done here is I have imported the weather data if you remember from the Stream Analytics Job defines EventDate, Humidity, Rain and the Temperature value I’ve just used the simple line chart and i created two line chart on the x axis I have the EventDate and on the y axis which is the values I have the temperature and you can have multiple lines in a single chart so you can have secondary values and I’ve also done a few things here on the chart settings so I think I have added a custom title so I specified it as temperature and for each of the visualizations you can set up additional filters so for example if i have the EventDate I can set up a filter for example I only want to look at the data for the last day so I don’t see so much history in the data and maybe I can do the same here as well So Advanced Filtering sorry not this one Relative Date past one day. I’ve also used two cards which is this one which display a single value so for example this card

shows the maximum humidity and as you can see that was probably the maximum humidity And this one shows the current temperature and this current temperature is a measure that i’ve specified by clicking on the New Measure and I use the DAX language to select the temperature from the latest value and i’m getting the temperature value From this data set and i’m using this FILTER command to filter on the max even date so basically the latest date so that’s always going to show me the current temperature. And there is one more feature in Power BI that all of these views can be filtered so if I click any of the values then it applies a filter to all of the other views so I selected a single data point and now my temperature graph got filtered to that simple that same date which well has a corresponding temperature and of course this also gets selected because I have well I picked the value which is well it’s 58 we are talking about one single value it doesn’t make an awful lot of sense in this particular case and we are going to see how it makes much more sense when we look at the pump example. And finally when you have done with the reports you can of course have multiple pages in the reports you click on this publish button and you are publishing this to the power BI Service and the reason we are doing that because the things that you publish is something that you can use in the dashboard at the end. And this is the other report which is based off the PowerBIPumpDataSetNew and here you can see that i have all the fields that i’ve using so the device id the even data latitude longitude the state and the uptime and i’ve also created a few other things for example i’ve created two measures which calculate the current state of the pumps so pump one and pump two current state and again i’ll use the dax language to filter the latest data filter on you know pump1 and then return either on or off based on whether they are off or on and actually I can show you that it is actually working so if I go back to my Node-Red now if I delete this one and if I switch pump one on so now we can see that this is pump on well it doesn’t really show here but it says state 1 and if I go back to my Power BI and in the home I refresh and we just wait for a little then now we can see that pump is on now here in the Power BI desktop I need to refresh otherwise it doesn’t get refreshed so quickly it maybe only gets refreshed every five minutes or so so it is definitely live data and the data is flowing all the way from my Automation Controller up onto the Azure portal into the IoT Hub and down to Power BI A couple of things that i’ve done here so first of all I have a card which shows the current pump state and i’ve also used this map and here in the map I can use the latitude and longitude fields to visualize the data where it is on the map and this is why I was feeding these particular coordinates in from Node-Red so I can just use this visualization and here we just seeing the two dots where the where these coordinates are and also we are seeing different circles and these circles represent the uptime so this is 600 and the other one is 800 It gets automatically scaled by the report based on the you know the relative difference of the two value and i’m also showing these pump values here so you can see as whenever I switch on the pump the uptime gets increased I mean really it’s not the best visual for this particular data but i just had to use what I had available and the reason you see this drop because I was restarting the server so it the the time restarted back to zero And with this report I want to show you that selection that I was talking about previously. So if I select one of the pump in the map what we are going to see that all the other reports refresh and now all these reports are showing the data relative to that pump so the line graph got updated so I only see the data for one of the pumps and also this pump maintenance gauge has updated so this is I think this is probably pump1 which has the up time of 828 and if I select the other one this has only 671 and also everything is updated so it’s a very you know nice and interactive functionality out of the box that you can use if you want to visualize more data so the user can just click and then everything gets filtered and then it clicks back to an empty part of the map and then it now shows all the data

I’ve also created two measures the TargetOnTime and the MaxOnTime which have fixed values so this is how I was able to scale these graphics to show me what is the maximum value which is 2000 and the target value which is 1400 so again you can look at these two reports you can look at the various measures that I have created so you can understand some of the things that you can do in the DAX language of course this is a you know fairly complicated language it’s very similar to the various functions that you have in Microsoft Excel but it’s really more suited to crunch database tables and columns and you know select data and filter them Once I was happy with all my reports and i’ve also pressed publish on these reports and then this got published to my BI Service as well The last step is that you log into app.powerbi.com and you log in with the same credentials that used in Power BI and now I can see the two reports that i published so one was called Azure IoT and the other one was Azure IoT pump and if I click on these then we can see the same visuals that we have seen in Power BI so the only reason this is not filtered to the last day because i did the filter on the Power BI report but I haven’t haven’t published the changes This is one of the report and this is the other one so it’s the same what we have seen in Power BI. Once you are in any of these reports you can still select the various visuals and you can click on this pin icon so it says pin visual and it asks you whether you want to pin it in one of your existing dashboards i have a dashboard which is called Weidmüller which I created when I clicked on New Dashboard. You can create a new dashboard so you can select these visuals from various reports that you have created and those reports can be fed from various data sets and that’s how i was managed to combine them into a single dashboard I started pinning some of these charts and and so for example this gauge visual into my dashboard so I just use the pin icon and then pin it on a dashboard Once you’re done with that you go back to your home and you should be able to see your dashboard and when I click on my dashboard I can see all the stuff that I have created and actually I can even go to full screen and if I switch on one of the pump then hopefully we are going to see after a little bit of delay that that pump get turned on so actually that delay wasn’t so bad it was probably only five seconds and i think that’s probably considering pretty good that all this information goes through all the cloud and all these services and it’s basically coming back to our bi dashboard And the filtering that I mentioned previously still works here so if i click on that i’m taken to my specific report I can go back and i see my entire dashboard with the various visuals that I compiled from different reports I think this video is long enough already I squeezed a lot of information into this Head over to the description you will find the download link where you can download the flow which contains the links that i’ve talked about and it also going to contain the two Power BI reports that i’ve created so one for the weather data and the other one for the pump data and hopefully you would be able to open that and look around even though that you will not be able to create the same data source which obviously exists under my account. As always if you have any questions just ask them in the comment section below and I will do my best to answer all of them i think that will be all for today thanks for watching and hopefully see in the next video