Adatis BI Blogs

Data Data Revolution

Following the DISCO theme, Adatis decided to present all the SQLBits attendees with a challenge based on the game Dance Dance Revolution. At the end of the game, the players were presented with two Power BI dashboards, one that streamed the data in near real time and the other representing historical data. This blog will detail the different components used in the demo.        (High Level Architecture)   The starting point The first requirement was to have a game that could run on a laptop and store the output data in a file. Based on the theme of the conference, we chose the game Stepmania 5 ( After understanding how it worked and what type of details we wanted to capture, we adapted the program so it was possible to save the output in a TXT file every time a key was pressed. Following is an example of how the data was structured. {"Player": "0", "Row": "768", "Direction": "Left", "NoteType": "Tap", "Rating": "OKAY", "Health": "Alive", "Combo": "0", "Score": "0", "Artist": "Katrina feat. Sunseaker", "Song": "1 - Walking On Sunshine", "Difficulty": "Easy"}   Capturing player details To complement the game output, we decided to create an MVC application that had two functions, capturing the player details in an Azure SQL DB, and, upload a new Game ID along with the player details to a reference BLOB stored in an Azure Storage Container.   Sending the data to an Event Hub Since we wanted to stream the data in near real time, we needed an application that could read the data from the output file as soon as it was updated. To achieve this, we built a C# application that was sending the data to an Event Hub. To make sure we didn’t upload duplicate data, we implemented a logic that compared the last row with the previous one. If they were different, the row was uploaded and if not, the program would wait for the next input.   Distributing the data To distribute the data between the Azure SQL DB and the Power BI dataset, we used two separate Stream Analytics Jobs. The first job was using the Event Hub and the reference BLOB as inputs and the Azure SQL DB as output, while the second job was using the same inputs but having a Power BI dataset as an output. Due to the dataset limitations, we ensured that all the formatting was applied in the Stream Analytics Query (eg. cast between varchar and bigint, naming conventions, …).   Power BI streaming datasets In this scenario, the streaming datasets only work properly when created by the Stream Analytics Job. Any of the following actions invalidates the connection between the jobs and the dataset: · Create the dataset in Power BI · Change column names · Change column types · Disable the option Historic data analysis When the dataset crashes, the only solution to fix the issue is to delete and re-create it. As a result, all the linked reports and dashboards are deleted.   Representing the data By the time the demo was built, the connectivity of live datasets to the Power BI Desktop was not available, which means the live streaming dashboard was built using the online interface. It is important to note that it is impossible to pin an entire page as a dashboard when using live datasets since it won’t refresh as soon as the data is transmitted. Instead, each individual element must be pinned to the dashboard, adding some visual limitations.   The performance of the players could be followed by checking the dashboard streaming the results in near real time. The use of the word near was used several times in the blog because the streaming is limited not only by the internet connection but also by the Power BI concurrency and throughput constraints, meaning the results were not immediately refreshed. The second report was built using Power BI Desktop and was connected to the Azure SQL DB. At the end of the game, the players could obtain the following information: · Who was the winner · How did they perform during the game · The number of hits for each rating · Which direction they were more proficient

Azure Event Hubs and IoT Hub

Imagine that you are the CEO of a big Logistic & Transport Company that works across the UK. In order to obtain multiple insights that will allow you to efficiently analyse how your company is performing and help you take better decisions, you decide to start collecting different telemetry information from the vehicles fleet. The question is, how will you manage to deal with hundreds of connected devices producing millions of telemetry data? The answer is…. Event Hubs Back in 2014, Microsoft announced the release of Azure Event Hubs, a service that allows the collection of high throughput ingress of data streams generated by devices and services in an easy, secure and reliable way. The Event Hubs can be created either through the Azure Portal or the Management API and gets immediately available without the need of further setups or management/maintenance requirements. The information stored in customer partitions will provide the message streaming. Each customer will only read a specific subset of the message stream due to the portioned customer pattern.   IoT Hub In a very simplistic way, the Azure IoT Hub is the bridge between our devices and the cloud. It is fully manageable, enables reliable and secure device-to-cloud and cloud-to-device communication between millions of IoT devices and provides a service interface to support the application development.   Event Hubs or IoT Hub If we consider the IoT Hub as an improvement of Event Hubs, shall we assume that the solution to the scenario described on the top of the article will be the first option? The answer is… depends on what we want to achieve. If our needs require bidirectional communication and millions of simultaneously connected devices, IoT Hub would be the choice, however, combining the IoT Hub and Event Hubs instead of using them separately is a better option. While IoT Hub can deal with the device-to-cloud communication, the Event Hubs can deal with the huge amount of event ingresses produced by our vehicle fleet.   More Info: