Welcome to another Power B I tutorial. Today, we will learn to display real time data on Power B I dashboard. There are many ways you can display real time information, on your Power B I dashboard. We will display real time information, without using any paid application. We will use free version of power B I desktop, And Microsoft SQL Server Express. So let's start by creating a database table, in Microsoft SQL Server Express. In the Microsoft SQL Server Management Studio, right click on the tables and select new table from menu. Create two columns named sensor one, and sensor 2 in the new table, with decimal as data types. These columns will store random information, depicting data received and stored from sensors. This information will then be displayed on power B I dashboard in real time. Save the table as sensors. Open a new query, to enter some data into the sensors table. You can view columns of a table, by expanding its child objects, in the object explorer window. Select command is used, to retrieve data from a table. Result is blank, because currently there is no data in our table. We will use insert command, to insert data into our table. We will insert zeros in both columns. Our database is ready, now it is time to connect it to power B I. In power B I desktop, use the get data feature to connect power B I to SQL server. Select SQL server database, in get data window. Enter the name of the server, and the database which contains the table. Select Direct Query, as the data connectivity mode, and press ok. In the Navigator window, select the sensors table, and press the load button. Wait for the power B I, to connect to the SQL server database. Once connected, the table and fields will appear on the right side, in Data area. Add a title to your dashboard, using the text box visual. Add a gauge visual for sensor 1. A gauge visual shows single value as progress, in comparison with minimum, maximum, and target values. And another gauge visual for sensor 2. Select the first gauge visual, and add the sensor 1 field into the value area. Select the second gauge visual, and add the sensor 2 field into the value area. Congratulations! your dashboard is now linked with the SQL server database. However, it will not show the data in real time. For that, you need to turn on the page refresh setting in format your report page. After turning on the page refresh setting, you need to set the interval, to an appropriate value. For the purpose of this demonstration, we will set the interval to one second. After creating dashboard in power B I, we will come back to the SQL server management studio, and create a new query. In the query, declare an integer, and set its value to 1. Start a while loop. The while loop will repeatedly run, as long as the value of integer declared above, is less or equal to 100. Start a begin-end Block. Add the delay of one second. Set the value of sensor 1 to integer i, which is a count down number. Set the value of sensor 2, to a random number. The random number generates a number between 0 and 1, therefore it is multiplied to 100 to generate a bigger number. Increase the value of integer i, by one. Close the begin-end block. This code will loop hundred times, in hundred seconds, and each time the values of sensor1 and sensor2 will be updated. Let's execute this code, and see its effects in power B I dashboard. The dashboard is updating perfectly, displaying information in real time. However, the maximum value of both sensors need to be set to 100. Create a new measure named Max. And set its value to 100. Add the max measure to the maximum value of both gauge visuals. Let's execute the SQL query again, and see how the dashboard looks like now. This is perfect, just as we desired. Both sensor 1 and sensor 2 values are being displayed through the gauge visuals in real time. Instead of the update command, we will now use insert command, to add sensor information into new rows, instead of updating the same row. The query will now add hundred rows, with sensor 1 and 2 information, which will be displayed in the dashboard. Lets add a line chart, below the gauge charts, in our power b i dashboard. Add the sensor 1 field to the x axis. And sensor 2 to the y axis. Change the sensor 2 value of gauge visual, to average. And sensor 1 gauge value to maximum. Now, lets execute our query in SQL server management studio, and see the results in Power B I dashboard. Nice ! we have been successful in displaying real time information in our power b i desktop. To perform this task, you need SQL server express, SQL server management studio, and the Power B I desktop. All of these tools are available for free on Microsoft website. Thanks for watching. We hope you liked our tutorial. Don't forget to like and subscribe for more tutorials and updates.
