Real time dashboards with SQL server and logic apps
Visualization of real time data over dashboards can be complicated because refresh rate of incoming data at a particular time frame is high. In is this post, I will try to explain how dynamic values in a table gets updated in real-time basis.
Consider a scenario, where we want to measure and visualize the count of vehicles that past a check post @9.00pm, violating speed. Suppose we get the metrics in which the number of vehicles past the given time frame are over speeding a lot and thus we can trigger alerts based on this real time data.
Azure SQL DB
In this scenario, I am preparing a SQL DB that gets data inserted through sensors(I am manually inserting it). This data will be stored in a table along with speed and vehicle details.
I am also creating a stored proc dbo.MyProc(which is later used in the logic app part) is used to filter out the last entered speed as well as number of vehicles which overspeed in a given time frame.
Logic App
Preparing the logic apps can be bit tricky as you need to deal with the API connectors. In this scenario, I am using a recurrence task to query the table via stored Proc in order to fetch the latest speed or the last row of the table. I am using two API connectors for this experiment, i.e SQL server task and Power BI followed with a for-each loop.
The SQL task fetched the connection strings of the database on creating a successful API connection. Once connection is made, select the stored proc that will be executed on every 5 sec of the recurrence task.
The use of a for-each is to iterate over the result-sets of the stored proc and fetches the latest inserted row.
Before creating this connector, an end to end streaming dataset connection needs to be setup in the power BI.
Power BI
In order to setup an end to end streaming dataset connection, Log in app.powebi.com>> Select API >> Create new dataset >> add fields >>Create a dashboard in power BI and you are good to go>> Point this dataset to the Logic app output as shown in the above lines.
Once the dashboard is setup, run the logic app and try inserting data in the SQL db. You can clearly see how real time ingestion synchronizes with the dashboard data.
