Analyze data in Azure Data Explorer dashboards

Azure Data Explorer is a fast and powerful Azure service for analyzing real-time data. All of the IoT projects I worked on lately, we used Azure Data Explorer. Mostly for the (data) engineer to, for example, analyze the data or to identify anomalies. But Azure Data Explorer is less suitable for some users because of the special (Kusto) query language. The new dashboard feature is a good addition for this problem. A data engineer can prepare a dashboard and users don’t need to have any knowledge about Kusto.

Let’s see how to create a dashboard in Azure Data Explorer.

Prepare cluster and sample data

It is necessary to have a Kusto cluster with some data. I will use the free Kusto cluster that I also used in the Kusto Detective Agency. If you don’t have a cluster, you can create your free cluster here: https://aka.ms/kustofree.

Copy the Cluster URI, as highlighted in the following image, we will need it later:

Source: https://detective.kusto.io/inbox

To add data into your new cluster, go to the query editor. You can either click on the Query button highlighted in the below image, or open in the Kusto Explorer desktop tool.

Source: https://detective.kusto.io/inbox

Execute the code below to ingest a sample dataset, the dataset is also from the Kusto Detective Agency.

.execute database script <|
// Create the table with the traffic information.
// The data loading process estimated to take ~3-4min to complete (114M+ rows of data).
// Notes: VIN - is Vehicle ID 
.create-merge table Traffic (Timestamp:datetime, VIN:string, Ave:int, Street:int)
.ingest async into table Traffic (@'https://kustodetectiveagency.blob.core.windows.net/digitown-traffic/log_00000.csv.gz')
.ingest async into table Traffic (@'https://kustodetectiveagency.blob.core.windows.net/digitown-traffic/log_00001.csv.gz')
.ingest async into table Traffic (@'https://kustodetectiveagency.blob.core.windows.net/digitown-traffic/log_00002.csv.gz')

Create a dashboard

Navigate to “Dashboards (Preview)” and create a new dashboard.

Enter a name for your dashboard, click create, and it will open the editor mode.

PAY ATTENTION! The dashboard is not created yet. So, if you close the window all changes will be lost. To save your dashboard, click on the save icon in the top right of the screen.

We start by adding a new tile, click on “Add tile” as seen in the screenshot below.

Because it is our first tile, we will need to create a data source, click on “+ Data source”. A new window will pop up to enter the properties for the new data source. We copied the cluster URI before, so now you can paste it, as seen in the screenshot below:

When the data source is created, it will open the query window. For this showcase, I need the Traffic information of 3 different vehicles:

Traffic
| where VIN in ('GT8DBE4EDD','IR6066CAFE','OL40A804E3')

Run the query to check if it’s ok (nr 1 in the screenshot below). If everything is ok, then the result will be shown in the bottom half of the screen (nr 2 in the screenshot below).

To the right of the results tab, we can add a visual. When clicked, a new window will open on the right side of the screen. We can choose different visuals and set their properties.

Thanks to a colleague who came up with the idea, I create a scatter plot of the routes of each vehicle. In the screenshot below you will see all settings. After adding all the settings, click on “Apply changes” in the top right of the screen.

If you want to know more about all the different visuals, visit: https://learn.microsoft.com/en-us/azure/data-explorer/dashboard-customize-visuals

Now we have our first tile on our dashboard. It’s possible to add more tiles if needed, for this showcase this is enough. To save your dashboard, click on the save icon in the top right of the screen.

Adding parameters

Now we created a dashboard with a static tile. Because the tile is static, we will always get the three vin numbers that we specified. To make it more dynamic we can add parameters, so that a user can specify which vin numbers are selected. To add a new parameter, click on “Parameters” (nr 1 in the screenshot below) and then on “New parameter” (nr 2 in the screenshot below).

I configured the parameter properties as seen in the screenshot below. The query that I used for the parameter is below the screenshot. I prefer to use a prefix, like an underscore, to identify the parameters. This way they are easier to identify in the query window.

The query for the parameter:

Traffic
| distinct VIN
| top 100000 by VIN asc

The results of the parameter query are limited to 500.000 records. I used a top 100.000 in the query because my browser crashed several times when I used 500.000 records.

Changing the tile

Now that the parameter is created, we need to change the query, so that it will use the parameter. Edit the tile by clicking the pencil, as highlighted in the following image:

Change the query to:

Traffic
| where VIN in (_VIN) 

“Apply changes” in the top right of the screen and save the dashboard.

Users can now select one or more vin numbers by a simple selection window.

Share the dashboard

To make the dashboard available to other users we need to perform one last step. We need to set the permissions by clicking on “Share” > “Manage permissions”.

A new window will open on the right side of the screen. Here you can add new users or groups for the dashboard. Start typing in the name of the user or group (nr 1 in the screenshot below) and suggestions will be shown below the field, select one of the suggestions. In the permissions dropdown (nr 2 in the screenshot below) you can choose between “Can view” and “Can edit” to set the authorization level. Don’t forget to click on the “Add” button.

And that’s a wrap. The dashboard is now created and available for other users.

By the way, it may take a while before the dashboard is shown within the dashboard overview of the user.

Conclusion

In this showcase, we’ve only shown the tip of the iceberg when it comes to the dashboarding capabilities of Azure Data Explorer. But in this showcase, we already experienced the ease of analyzing real-time data and to make it available for users who may not be familiar with Kusto.