Azure Data Explorer Blog articles

Introducing Data Series Colors: tell clearer stories with ADX Dashboards

Michal_Bar — Mon, 23 Feb 2026 10:55:16 GMT

A frequent request we receive from dashboard editors is the ability to have control over color settings.

Until now, color assignments in ADX dashboards were largely automatic. While this worked for basic scenarios, it often fell short in operational and reporting use cases where color isn’t decoration - it’s meaning. Today, we’re introducing Data Series Colors, a new capability that gives editors direct control over how colors are applied to their visuals.

With Data Series Colors, dashboard editors can now:

Assign colors directly to each data series
Override system defaults with intentional choices
Maintain consistency across visuals and dashboards

This feature is supported across pie charts, time charts, line charts, area charts, bar charts, column charts, anomaly and scatter charts, covering the most used visualization types.

Using color to convey meaning - and tell a story

When colors are assigned intentionally, users no longer need to read legends or labels to understand what is happening. A spike, a drop, or a comparison immediately carries context. Over time, viewers learn the language of the dashboard - what each color represents, what’s normal, and what needs attention.

Before:

After:

This makes dashboards more than monitoring tools. They become a way to:

Highlight what matters most.
Reinforce shared understanding across teams.
Present a clear narrative with each visual.

Learn more by exploring the documentation: Customize Azure Data Explorer Dashboard Visuals - Azure Data Explorer | Microsoft Learn

Billing Announcement: Azure Data Explorer Storage Adjustment (November 2025)

bwatts670 — Thu, 06 Nov 2025 17:52:15 GMT

It was recently discovered that some Azure Data Explorer clusters were undercharging for the amount of data being stored. This was corrected on November 3^rd which may result in an increase in the Azure Data Explorer cluster storage cost for some customers. Below are the two meters that are affected by this change.

Meter Name	Cluster Type
Hot LRS Data Stored	Single Availability Zone
Hot ZRS Data Stored	Multiple Availability Zones

This does not affect any other meter categories but it’s possible that you’ll see an increase in the storage cost around November 3^rd. We apologize for any inconvenience this might cause.

Labeling Kusto Data in Azure Managed Grafana for Machine Learning Workflows

Hauke — Mon, 14 Jul 2025 13:01:37 GMT

Labeling Kusto Data in Azure Managed Grafana for Machine Learning Workflows

In today's data-driven world, the quality of machine learning models heavily depends on the quality of labeled training data. Whether you're detecting anomalies in manufacturing processes, identifying potential health risks, or analyzing weather patterns, having properly labeled datasets is crucial for building reliable AI systems.

This blog post demonstrates how to create an interactive labeling system for Kusto (Azure Data Explorer) data using Azure Managed Grafana, enabling subject matter experts to efficiently label large datasets for machine learning applications.

Why Data Labeling Matters

Labeled data serves as the foundation for supervised machine learning models. Consider these real-world scenarios:

Healthcare: Medical professionals labeling diagnostic test results to train models that can detect false positives in virus tests or identify anomalies in medical imaging
Manufacturing: Quality engineers marking defective products in chip production data to build automated quality control systems
Finance: Fraud analysts labeling suspicious transactions to train fraud detection models
Weather Monitoring: Meteorologists categorizing storm events to improve weather prediction models
Automotive: Engineers labeling sensor data from vehicle testing to identify component failures or performance issues
Retail: Analysts categorizing customer behavior patterns to improve recommendation engines

Without accurate labels, even the most sophisticated algorithms will produce unreliable results.

Architecture Overview

In this solution, large time series datasets stored in an Azure Data Explorer (Kusto) database are enriched with labels. The labeling metadata is maintained in an Azure SQL Database and updated through the Volkov Labs Business Table plugin in Grafana. To provide a unified view, we use external tables in Kusto to seamlessly combine the raw time series data with the labeling information stored in SQL.

In summary, the architecture integrates three key Azure services:

Azure Data Explorer (Kusto): Stores the large-scale time series data that needs to be labeled.
Azure SQL Database: Holds the labeling metadata, which can be updated interactively.
Azure Managed Grafana: Serves as the user interface for labeling, enabling users to view, assign, and update labels directly from the dashboard.

High-Level-Architecture

Prerequisites

Before starting, ensure you have:

An Azure SQL Database
A Kusto database (Running on Azure or Microsoft Fabric)
An Azure Managed Grafana workspace
Appropriate permissions on all services

Demo Dataset: StormEvents

For this demonstration, we'll use the StormEvents table from the Azure Data Explorer help cluster. This publicly available dataset contains detailed information about storm events in the United States from the National Weather Service, making it perfect for demonstrating labeling workflows.

You can explore this dataset by connecting to help.kusto.windows.net and querying:

StormEvents | take 10

The dataset includes fields like EventType, State, EventNarrative, and DamageProperty, providing rich context for labeling exercises.

Setting Up the SQL Database Schema

The labeling system uses a well-structured SQL schema that separates label types from actual event labels. Below are the key components (you can find the full SQL code in the linked gist):

1. Label Type Table

The LabelType table defines the available label categories with ordering and validation:

CREATE TABLE LabelType ( LabelId INT IDENTITY(1,1) PRIMARY KEY, LabelShortDesc NVARCHAR(50) NOT NULL UNIQUE, LabelLongDesc NVARCHAR(500) NOT NULL, LabelOrder INT NOT NULL DEFAULT 999, LabelCreatedTimestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(), LabelUpdateTimestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE() );

2. Event Labeling Table

The EventLabel table stores actual labels applied to events:

CREATE TABLE EventLabel ( EventLabelId BIGINT IDENTITY(1,1) PRIMARY KEY, EventId INT NOT NULL, LabelId INT NOT NULL DEFAULT 1, EventLabelDesc NVARCHAR(1000) NULL, EventLabelUserName NVARCHAR(256) NOT NULL DEFAULT SUSER_SNAME(), EventLabelCreatedTimestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(), EventLabelUpdateTimestamp DATETIME2(3) NOT NULL DEFAULT GETUTCDATE(), CONSTRAINT UQ_EventLabel_EventId UNIQUE (EventId) );

3. Upsert Stored Procedure

The most critical component is the sp_AddEventLabel stored procedure that handles label insertion and updates safely. It performs an upsert operation using the SQL MERGE statement, ensuring data consistency without duplicate entries. This approach significantly simplifies the dashboard implementation by abstracting the complexity of label management.

CREATE PROCEDURE sp_AddEventLabel @EventId BIGINT, @LabelShortDesc NVARCHAR(50), @EventLabelDesc NVARCHAR(1000) = NULL, NVARCHAR(256) = NULL AS BEGIN -- Get LabelId from short description with validation DECLARE @LabelId INT; SELECT @LabelId = LabelId FROM LabelType WHERE LabelShortDesc = @LabelShortDesc; IF @LabelId IS NULL BEGIN THROW 50001, 'Invalid label short description provided', 1; RETURN; END -- Use MERGE for true upsert functionality MERGE EventLabel AS target USING (SELECT @EventId AS EventId, @LabelId AS LabelId, @EventLabelDesc AS EventLabelDesc, ISNULL(@UserName, SUSER_SNAME()) AS EventLabelUserName) AS source ON target.EventId = source.EventId WHEN MATCHED THEN UPDATE SET LabelId = source.LabelId, EventLabelDesc = source.EventLabelDesc, EventLabelUserName = source.EventLabelUserName, EventLabelUpdateTimestamp = GETUTCDATE() WHEN NOT MATCHED THEN INSERT (EventId, LabelId, EventLabelDesc, EventLabelUserName) VALUES (source.EventId, source.LabelId, source.EventLabelDesc, source.EventLabelUserName); END;

This procedure ensures data integrity and provides automatic timestamping for audit purposes.

4. Consolidated View

The vw_EventLabeling view brings together both tables and introduces calculated fields to streamline querying and analysis. This unified view simplifies access to labeling data, making it easier to build reports and dashboards.

Configuring Authentication and Permissions

Grafana Managed Identity Setup

Azure Managed Grafana uses its workspace name as the name for the name of the managed identity. We use the Grafana Workspace managed identity, accessing the Azure SQL database. Configure the SQL database users and permissions accordingly:

CREATE USER [your-grafana-workspace-name] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [your-grafana-workspace-name]; ALTER ROLE db_datawriter ADD MEMBER [your-grafana-workspace-name]; GRANT EXECUTE ON SCHEMA::dbo TO [your-grafana-workspace-name];

User Group Access

To enable seamless integration, make sure your user group—already configured for read access in the Kusto database—is also granted read permissions on the SQL tables via external tables. This ensures consistent access across both data sources and simplifies cross-platform querying.:

CREATE USER [your-entra-id-user-group] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [your-entra-id-user-group];

Creating External Tables in Kusto

Since Grafana can only connect to one data source per panel, we need external tables in Kusto to access the SQL Database data. This enables us to:

Make SQL data available in the Kusto database
Work around Grafana's single data source limitation per panel

// Create external table for the event labeling view .create external table EventLabeling ( // EventLabel fields EventLabelId: long, EventId: long, EventLabelDesc: string, // ... other fields LabelStatus: string ) kind=sql table=vw_EventLabeling ( h@'Server=<your-sql-server>.database.windows.net;Database=<your-database>;Authentication=Active Directory Integrated;' )

The complete source code definition is available in the linked gist. We use Entra ID integrated authentication to securely connect to the SQL database. To ensure proper access, verify that the user group with read permissions in Kusto also has read access to the SQL database. This alignment is essential for seamless querying through external tables.

Building the Grafana Dashboard with Volkov Labs Business Table

The magic happens in Grafana using the Business Table plugin from Volkov Labs. This plugin provides an editable table interface perfect for data labeling workflows.

The final dashboard

Key Configuration Elements

1. Workspace Data Sources Configuration

Ensure that both the Kusto and SQL database data sources are properly configured in your Grafana workspace. For SQL authentication, the Grafana workspace uses its managed identity, allowing secure and seamless access to the external SQL tables without the need for storing credentials. For Azure Data Explorer we are using the current user authentication method here, passing through the current user in Azure Managed Grafana to the Kusto database (see also my previous blog post here).

2. Panel Configuration

We configure two datasources for the business table

1. events: Querying the StormEvents and joining the labeling data from the SQL database:

let selectedEvents=toscalar( cluster("help.kusto.windows.net").database("Samples").StormEvents | where $__timeFilter(StartTime) and EventType in ($v_EventType) and State in ($v_State) |summarize make_list (EventId)); let EventLabel=external_table('EventLabeling') | where EventId in (selectedEvents); cluster("help.kusto.windows.net").database("Samples").StormEvents | where $__timeFilter(StartTime) and EventType in ($v_EventType) and State in ($v_State) | lookup EventLabel on EventId | order by StartTime, EndTime, State asc , EventType asc

In our example, the join to the external SQL table is performed using the EventId column. However, in real-world scenarios, events often lack a single identity column. Instead, a composite natural key—such as StartTime, EndTime, and EventType—may be used to uniquely identify records. To ensure efficient query performance, it's important to verify that filters are pushed down to the SQL database. You can confirm this by reviewing the audit logs. In our case, we explicitly filter on EventIds within the query to achieve pushdown. Note that performing a join (used here as a lookup) does not automatically push the EventIds as filters to the SQL database, which can lead to less efficient execution.

2. LabelType We are using this to populate the drop down list for the label types.

Queries used in the business table

3. Editable Columns Configuration

The Business Table plugin allows specific columns to be editable with different editor types.

With the editor type select, we are confguring the drop down list, based on the LableType-Query:

Example configuration with Editor Type Select

4.Update Query Configuration

Users can add or update labels through the interface. For this we have to configure the Update Request:

Update Request configuration

The upsert operation is executed via the sp_AddEventLabel stored procedure, which receives input from the business table using predefined variables. This is the SQL code:

EXEC sp_AddEventLabel @EventId = '${payload.EventId:int}', @LabelShortDesc = '${payload.LabelShortDesc}', @EventLabelDesc = '${payload.EventLabelDesc:sqlsting}', ='${__user.login}';

Interactive Labeling Workflow

With everything configured, users can:

Browse Events: View unlabeled events from the Kusto dataset with context information
Apply Labels: Use dropdown menus to categorize events (e.g., "False Positive", "Investigation Needed")
Add Context: Include detailed descriptions for complex cases
Track Changes: Automatic timestamping and user tracking for full audit trail
Monitor Progress: View labeling statistics and completion rates
Collaborate: Multiple users can label different events simultaneously

Best Practices

Start Simple: Begin with basic label categories and expand as needed
User Training: Ensure labelers understand the categories and criteria
Quality Control: Implement review processes for critical labels
Performance: Index your tables appropriately for large datasets (as shown in our schema)
Backup: Regular backups of your labeling data are essential
Validation: Use database constraints to ensure data quality
Audit Trail: Maintain full history of who labeled what and when

Conclusion

This integrated approach combining Azure SQL Database, Kusto, and Grafana provides a powerful, scalable solution for data labeling workflows. The interactive interface empowers domain experts to efficiently label large datasets, while the robust backend ensures data integrity and traceability.

Key benefits include:

Unified Interface: Single pane of glass for data exploration and labeling
Real-time Collaboration: Multiple users can work simultaneously
Full Audit Trail: Complete history of labeling activities
Flexible Schema: Easy to adapt for different labeling scenarios
Enterprise Ready: Built on Azure services with proper authentication

Whether you're building the next generation of medical diagnostic tools, optimizing manufacturing processes, or improving weather prediction models, this labeling system provides the infrastructure needed to turn raw data into actionable insights.

The result is high-quality labeled data that forms a solid foundation for building reliable machine learning models—ultimately driving better business outcomes across a wide range of industries.

Additional Resources

Azure Data Explorer's Advanced Geospatial Analysis: Breaking New Ground

cosh23 — Sun, 13 Apr 2025 05:33:15 GMT

In the rapidly evolving landscape of data analytics, Azure Data Explorer (ADX) continues to push boundaries with its latest enhancement to geospatial capabilities. These new features, meticulously developed by Michael Brichko and the Kusto team, represent a significant advancement in how organizations can analyze and derive insights from location-based data directly within the Kusto Query Language (KQL).

The Evolution of Geospatial Analysis in ADX

Azure Data Explorer has long supported basic geospatial operations, but this latest release dramatically expands its capabilities with functions that address specific analytical challenges faced by data engineers and analysts working with spatial data.

New Powerful Lookup Plugins and Joins

At the core of this update are powerful additions that solve complex spatial relationship problems: the geo_polygon_lookup and geo_line_lookup plugins, alongside comprehensive geospatial join capabilities.

geo_polygon_lookup

This plugin efficiently determines relationships between points and polygons, answering questions like "which sales territory contains this customer?" or "which service zone covers this location?":

let territories = datatable(region_name:string, polygon:dynamic) [ "Northeast", dynamic({"type":"Polygon", "coordinates":[[[-73.97375,40.74300],[-73.98653,40.75486],[-73.99910,40.74112],[-73.97375,40.74300]]]}), "Southwest", dynamic({"type":"Polygon","coordinates":[[[2.57564,48.76956],[2.42009,49.05163],[2.10167,48.80113],[2.57564,48.76956]]]}), ]; let customer_locations = datatable(customer_id:string, longitude:real, latitude:real) [ "Customer1", -73.98000, 40.74800, "Customer2", 2.50000, 48.90000, "Customer3", 10.00000, 50.00000 ]; customer_locations | evaluate geo_polygon_lookup(territories, polygon, longitude, latitude) | project customer_id, region_name

The performance benefits here are substantial - instead of complex self-joins or multi-step operations, this plugin handles the spatial relationship calculations in a single, optimized operation.

geo_line_lookup

Similarly, the geo_line_lookup plugin identifies lines (like highways, pipelines, or power lines) within a specified distance of points:

let infrastructure = datatable(line_id:string, line:dynamic) [ "Highway 101", dynamic({"type":"LineString","coordinates":[[-122.40297,37.79329],[-122.38855,37.77867]]}), "Main Pipeline", dynamic({"type":"LineString","coordinates":[[-118.35645,34.17247],[-118.32962,34.09873]]}), ]; let maintenance_reports = datatable(report_id:string, longitude:real, latitude:real, issue:string) [ "R001", -122.39500, 37.78500, "Debris", "R002", -118.34000, 34.15000, "Leak", "R003", -120.00000, 36.00000, "Damage" ]; maintenance_reports | evaluate geo_line_lookup(infrastructure, line, longitude, latitude, 500) // within 500 meters | project report_id, line_id, issue

This capability is invaluable for infrastructure management, transportation analysis, and network optimization scenarios.

Advanced Geospatial Joins

Beyond the lookup plugins, ADX now provides comprehensive support for various geospatial join strategies. These capabilities allow for sophisticated spatial analysis using different geo-hashing approaches:

// Join locations using S2 cells let locations1 = datatable(name: string, longitude: real, latitude: real) [ "Store A", -0.12433080766874127, 51.51115841361647, "Store B", -0.12432651341458723, 51.511160848670585, "Store C", -0.12432466939637266, 51.51115959669167, "Store D", 1, 1, ]; let customer_visits = datatable(customer_id: string, longitude: real, latitude: real) [ "Customer1", -0.12432668105284961, 51.51115938802832 ]; let s2_join_level = 22; // Higher level = more precision locations1 | extend hash = geo_point_to_s2cell(longitude, latitude, s2_join_level) | join kind = inner ( customer_visits | extend hash = geo_point_to_s2cell(longitude, latitude, s2_join_level) ) on hash | project name, customer_id

For more complex proximity requirements, the H3 geo-hashing system with neighbor awareness provides an elegant solution:

// Join locations using H3 cells with neighbor awareness let retail_locations = datatable(store_name: string, longitude: real, latitude: real) [ "Downtown", -0.12433080766874127, 51.51115841361647, "Westside", -0.12432651341458723, 51.511160848670585, "Eastside", -0.12432466939637266, 51.51115959669167, "Remote", 1, 1, ]; let customer_events = datatable(event_id: string, longitude: real, latitude: real) [ "Purchase123", -0.12432668105284961, 51.51115938802832 ]; let to_hash = (lng: real, lat: real) { let h3_hash_level = 14; // Precision level let h3_hash = geo_point_to_h3cell(lng, lat, h3_hash_level); array_concat(pack_array(h3_hash), geo_h3cell_neighbors(h3_hash)) }; retail_locations | extend hash = to_hash(longitude, latitude) | mv-expand hash to typeof(string) | join kind = inner ( customer_events | extend hash = to_hash(longitude, latitude) | mv-expand hash to typeof(string) ) on hash | distinct store_name, event_id

For proximity-based joins that require precise distance calculations, the buffer-based approach provides exceptional flexibility:

// Join locations based on precise distance buffers let venues = datatable(venue_name: string, longitude: real, latitude: real) [ "O2 Entrance", 0.005889454501716321, 51.50238626916584, "North Gate", 0.0009625704125020596, 51.50385432770013, "Greenwich Park", 0.0009395106042404677, 51.47700456557013, ]; let points_of_interest = datatable(poi_id: string, longitude: real, latitude: real) [ "O2 Arena", 0.003159306017352037, 51.502929224128394 ] | extend buffer = geo_point_buffer(longitude, latitude, 300, 0.1); // 300-meter radius venues | evaluate geo_polygon_lookup(points_of_interest, buffer, longitude, latitude) | project venue_name, poi_id

Beyond joins and lookups, ADX now offers specialized functions for precise geospatial calculations:

geo_from_wkt()

The new geo_from_wkt() function bridges the gap between different geospatial systems by converting Well-Known Text (WKT) format - a standard in GIS systems - into GeoJSON objects that ADX can process:

// Convert WKT to GeoJSON for further analysis let wkt_shapes = datatable(shape_id:string, wkt_representation:string) [ "City Boundary", "POLYGON((-122.406417 37.785834, -122.403984 37.787343, -122.401826 37.785069, -122.404681 37.782928, -122.406417 37.785834))", "Highway", "LINESTRING(-122.33707 47.60924, -122.32553 47.61803)" ]; wkt_shapes | extend geojson_shape = geo_from_wkt(wkt_representation)

Result:

shape_id	wkt_representation	geojson_shape
City Boundary	POLYGON((-122.406417 37.785834, -122.403984 37.787343, -122.401826 37.785069, -122.404681 37.782928, -122.406417 37.785834))	{ "type": "Polygon", "coordinates": [ [ [ -122.406417, 37.785834 ], [ -122.403984, 37.787343 ], [ -122.401826, 37.785069 ], [ -122.404681, 37.782928 ], [ -122.406417, 37.785834 ] ] ] }
Highway	LINESTRING(-122.33707 47.60924, -122.32553 47.61803)	{ "type": "LineString", "coordinates": [ [ -122.33707, 47.60924 ], [ -122.32553, 47.61803 ] ] }

This function supports all standard geometry types including Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

Route Analysis Functions

Two complementary functions provide sophisticated route analysis capabilities:

geo_line_interpolate_point

geo_line_interpolate_point() calculates a point at a specified fraction along a line:

// Find points along a delivery route at 25%, 50%, and 75% of the journey let delivery_routes = datatable(route_id:string, route:dynamic) [ "Route A", dynamic({"type":"LineString","coordinates":[[-122.33707, 47.60924], [-122.32553, 47.61803]]}) ]; delivery_routes | extend start_point = geo_line_interpolate_point(route, 0), quarter_point = geo_line_interpolate_point(route, 0.25), midpoint = geo_line_interpolate_point(route, 0.5), three_quarters = geo_line_interpolate_point(route, 0.75), end_point = geo_line_interpolate_point(route, 1)

Result:

route_id	route	start_point	quarter_point	midpoint	three_quarters	end_point
Route A	{ "type": "LineString", "coordinates": [ [ -122.33707, 47.60924 ], [ -122.32553, 47.61803 ] ] }	{ "type": "Point", "coordinates": [ -122.33707, 47.609240000000007 ] }	{ "type": "Point", "coordinates": [ -122.33418536369042, 47.611437608491833 ] }	{ "type": "Point", "coordinates": [ -122.33130048494128, 47.613635144663533 ] }	{ "type": "Point", "coordinates": [ -122.3284153637215, 47.615832608503482 ] }	{ "type": "Point", "coordinates": [ -122.32553000000002, 47.61803 ] }

geo_line_locate_point

geo_line_locate_point() performs the inverse operation, determining how far along a route a specific point is located:

// Calculate what percentage of the route has been completed let active_routes = datatable(vehicle_id:string, route:dynamic, current_long:real, current_lat:real) [ "Truck1", dynamic({"type":"LineString","coordinates":[[-122.33707, 47.60924], [-122.32553, 47.61803]]}), -122.33000, 47.61500 ]; active_routes | extend completion_percentage = geo_line_locate_point(route, current_long, current_lat) * 100 | project vehicle_id, completion_percentage

Result:

vehicle_id	completion_percentage
Truck1	63,657018697669

Together, these functions enable precise tracking and analysis of movement along routes, critical for logistics, fleet management, and transportation applications.

Closest Point Calculations

Two new functions address the need to find exact points on geometric features that are closest to reference points:

geo_closest_point_on_line

geo_closest_point_on_line() identifies the exact point on a line nearest to a reference point:

print point_on_line = geo_closest_point_on_line(-115.199625, 36.210419, dynamic({ "type":"LineString","coordinates":[[-115.115385,36.229195],[-115.136995,36.200366],[-115.140252,36.192470],[-115.143558,36.188523],[-115.144076,36.181954],[-115.154662,36.174483],[-115.166431,36.176388],[-115.183289,36.175007],[-115.192612,36.176736],[-115.202485,36.173439],[-115.225355,36.174365]]}))

geo_closest_point_on_polygon

geo_closest_point_on_polygon() calculates the closest point on a polygon boundary to a given location:

let central_park = dynamic({"type":"Polygon","coordinates":[[[-73.9495,40.7969],[-73.95807266235352,40.80068603561921],[-73.98201942443848,40.76825672305777],[-73.97317886352539,40.76455136505513],[-73.9495,40.7969]]]}); print geo_closest_point_on_polygon(-73.9839, 40.7705, central_park)

These functions enable precise proximity analysis for applications ranging from emergency response to facility planning.

Technical Implementation and Performance Considerations

What makes these new geospatial features particularly impressive is their integration with ADX's query engine. The functions leverage ADX's columnar storage and parallel processing capabilities to perform complex spatial operations efficiently at scale.

For large datasets, the lookup plugins use optimized spatial indexing techniques to avoid the performance pitfalls that typically plague geospatial joins. This means that operations that might take minutes or hours in traditional GIS systems can execute in seconds on properly optimized ADX clusters.

Real-World Applications

The new geospatial capabilities in ADX enable sophisticated solutions across industries:

Telecommunications

Network operators can analyze signal coverage against population density polygons to identify optimization opportunities:

let cell_towers = datatable(tower_id:string, signal_longitude:real, signal_latitude:real, signal_strength:int) [ "Tower1", -73.98000, 40.74800, 85, "Tower2", -73.97500, 40.75200, 72, "Tower3", -73.99000, 40.74100, 90 ]; let population_zones = datatable(zone_name:string, population_density:int, zone_polygon:dynamic) [ "Midtown", 25000, dynamic({"type":"Polygon", "coordinates":[[[-73.97375,40.74300],[-73.98653,40.75486],[-73.99910,40.74112],[-73.97375,40.74300]]]}) ]; let signal_threshold = 80; let density_threshold = 20000; cell_towers | evaluate geo_polygon_lookup(population_zones, zone_polygon, signal_longitude, signal_latitude) | summarize avg_signal_strength=avg(signal_strength) by zone_name, population_density | where avg_signal_strength < signal_threshold and population_density > density_threshold

Energy

Pipeline operators can identify sensors that might be affected by maintenance on nearby infrastructure:

let planned_maintenance = datatable(maintenance_id:string, maintenance_longitude:real, maintenance_latitude:real, start_time:datetime) [ "M001", -118.34500, 34.16000, datetime(2025-04-15), "M002", -118.33000, 34.10000, datetime(2025-04-20) ]; let pipelines = datatable(pipeline_id:string, pipeline_path:dynamic) [ "Main", dynamic({"type":"LineString","coordinates":[[-118.35645,34.17247],[-118.32962,34.09873]]}), "Secondary", dynamic({"type":"LineString","coordinates":[[-118.36000,34.15000],[-118.34000,34.08000]]}) ]; let sensors = datatable(sensor_id:string, pipeline_id:string, sensor_type:string, next_scheduled_reading:datetime) [ "S001", "Main", "Pressure", datetime(2025-04-16), "S002", "Main", "Flow", datetime(2025-04-18), "S003", "Secondary", "Temperature", datetime(2025-04-22) ]; planned_maintenance | evaluate geo_line_lookup(pipelines, pipeline_path, maintenance_longitude, maintenance_latitude, 500) | join kind =inner sensors on pipeline_id | project maintenance_id, sensor_id, sensor_type, next_scheduled_reading

Transportation & Logistics

Fleet operators can optimize routing by analyzing historical trip data against road networks:

let completed_trips = datatable(trip_id:string, trip_success:bool, incident_longitude:real, incident_latitude:real, incident_type:string) [ "T001", false, -122.40000, 47.61000, "Delay", "T002", false, -122.39500, 47.60800, "Traffic", "T003", false, -122.41000, 47.62000, "Weather" ]; let road_networks = datatable(road_name:string, road_type:string, road_path:dynamic) [ "5th Avenue", "Urban", dynamic({"type":"LineString","coordinates":[[-122.40297,47.59329],[-122.40297,47.62000]]}), "Highway 99", "Highway", dynamic({"type":"LineString","coordinates":[[-122.39000,47.60000],[-122.41000,47.63000]]}) ]; completed_trips | where trip_success == false | evaluate geo_line_lookup(road_networks, road_path, incident_longitude, incident_latitude, 100) | summarize incidents_count=count() by road_name, road_type, incident_type | order by incidents_count desc

Environmental Monitoring

Researchers can correlate sensor readings with geographic zones to track pollution dispersion:

let sensor_readings = datatable(sensor_id:string, reading_type:string, reading_value:real, timestamp:datetime, sensor_longitude:real, sensor_latitude:real) [ "ENV001", "PM2.5", 35.2, datetime(2025-04-01), -122.33000, 47.61500, "ENV002", "PM2.5", 22.8, datetime(2025-04-01), -122.34000, 47.62000, "ENV003", "PM2.5", 41.3, datetime(2025-04-01), -122.32000, 47.60500 ]; let air_quality_zones = datatable(zone_name:string, zone_boundary:dynamic) [ "Downtown", dynamic({"type":"Polygon","coordinates":[[[-122.34000,47.60000],[-122.34000,47.62000],[-122.32000,47.62000],[-122.32000,47.60000],[-122.34000,47.60000]]]}) ]; sensor_readings | where reading_type == "PM2.5" and timestamp > ago(7d) | evaluate geo_polygon_lookup(air_quality_zones, zone_boundary, sensor_longitude, sensor_latitude) | summarize avg_reading=avg(reading_value) by zone_name, bin(timestamp, 1h)

Getting Started with ADX Geospatial Analysis

The geospatial functions are available in all ADX clusters without requiring any special configuration.

For optimal performance with large spatial datasets:

Consider partitioning strategies that account for spatial locality
Pre-compute and cache complex geometries when dealing with static boundaries
Monitor query performance to identify opportunities for optimization

Conclusion

Azure Data Explorer's enhanced geospatial capabilities represent a significant advancement in making spatial analysis accessible and performant within a cloud analytics platform. By eliminating the need for specialized GIS tools and complex data movement, these features enable organizations to derive deeper insights from location-based data more efficiently than ever before.

Whether you're analyzing telecommunications networks, optimizing logistics operations, managing energy infrastructure, or monitoring environmental patterns, ADX now provides the tools to incorporate sophisticated geospatial analysis directly into your data workflows.

#AzureDataExplorer #ADX #Kusto #KQL #GeospatialAnalysis #DataAnalytics #CloudComputing #BigData #SpatialIntelligence #BusinessIntelligence #DataEngineering #MicrosoftAzure #DataScience #GIS #Analytics

Deprecation of variable length edge dot notation in graph-match

cosh23 — Tue, 01 Apr 2025 07:27:58 GMT

In our ongoing efforts to improve the efficiency and clarity of graph queries in KQL, we are introducing changes to the way variable length edge properties are used in graph-match constraints and projections. Specifically, we are deprecating the use of variable edge properties together with binary operators or scalar functions.

asd

G | graph-match <Path Pattern> where <Constraints> project <Projections>

Constraints: A Boolean expression composed of properties of named variables in the Pattern

Projections: The project clause converts each pattern to a row in a tabular result

Constraints

For graph-match constraints, we will no longer support the use of variable edge properties with binary operators or scalar functions. Instead, we encourage the use of the all() graph function.

For example:

The pattern (n1)-[*1..3]->(n2) with the constraint e.prop has "abc" should be updated to all(e, prop has "abc").
Similarly, isnotempty(e.prop) should be updated to all(e, isnotempty(prop)).

Graph Projections

For graph-match projections, we are also deprecating the use of variable edge properties with binary operators or scalar functions. We recommend using the map() graph function instead.

For example:

The projection project strcat(e.prop, "suffix") should be updated to map(e, strcat(prop, "suffix")).
The projection bag_pack("key1", e.prop1, "key2", e.prop2) should be updated to map(e, bag_pack("key1", prop1, "key2", prop2)).

These changes are aimed at simplifying and standardizing the way graph queries are written, making them more readable and maintainable. We encourage you to update your existing queries to align with these new guidelines.

Starting in May 2025, the system will no longer support dot notation for variable length edges and will generate errors for non-compliant queries. Queries using deprecated variable edge properties with binary operators or scalar functions will not execute.

Users should review and update their graph-match constraints and projections to adhere to the new guidelines. Using the recommended functions will prevent disruptions and ensure consistent query practices.

Thank you for your support during this transition.

New ADX Dashboards Customization Features: More Control, Better Usability, and Improved Performance

Michal_Bar — Thu, 13 Feb 2025 12:22:58 GMT

We’re introducing new dashboard customization features to enhance control, usability, and performance. From managing data series visibility to improving navigation and map behavior, these updates help create a clearer, more efficient dashboard experience.

Legend Number Configuration
Adjustable Panel Width
Crosshair Tooltip Number Configuration
Map Centering Configuration

Legend Number Configuration for Dashboards

To enhance readability and performance, dashboard authors can now configure the number of data series displayed on load when multiple series are expected in a chart.

Additional series remain accessible via the legend and can be rendered as needed.

For example, imagine a chart designed to display energy consumption over time for a fleet of cars. The dashboard author expects a large number of data series—one for each vehicle. To make the chart easier to interpret and improve dashboard performance, they can now set a limit on how many series are rendered initially. Users can still explore the full dataset by selecting additional series from the legend.

Crosshair Tooltip Number Configuration

We’re introducing a new setting under Display options that allows dashboard authors to control the number of data points displayed in a chart’s crosshair tooltip.

Depending on the expected number of data series in a chart and the specific use case, dashboard owners can now set a limit on how many data points appear in the tooltip. This helps improve readability and prevents overcrowding when dealing with a large number of series.

With this update, users can tailor the tooltip experience to focus on the most relevant insights while keeping charts clear and easy to interpret.

Note:
This tile-level setting may be overridden by the general ADX web setting, "Show all series in chart tooltip."

Adjustable Panel Width for Editors and Viewers

We’re introducing a highly requested improvement: the ability to manually adjust the width of the pages pane in both edit and view modes.

For dashboards with multiple pages—especially those with long names—users can now resize the panel by dragging to expand or collapse it, making navigation easier and improving usability. This flexibility ensures a more comfortable viewing experience, allowing users to see more of their page names at a glance without truncation.

Map Centering Configuration for Dashboard Tiles

Introducing a new setting to Map visualizations in Dashboards, giving users more control over how maps behave during data refreshes.

With the new auto center setting, displayed on top of the map visualization, users can choose whether the map resets its zoom and center position upon refresh or maintains their manually adjusted view:

Auto center OFF: The zoom level and position set by the user will persist across data refreshes, preventing unwanted zoom-in/out changes. Users can still manually reset the view using the Center button.
Auto center ON: The map will automatically adjust its zoom and center position with each data refresh, ensuring the view is always recalibrated based on the latest data.

This feature helps prevent disruptions in analysis, particularly for users who prefer a fixed view while monitoring live data updates.

Azure Data Explorer Web UI team is looking forward for your feedback in KustoWebExpFeedback@service.microsoft.com

You’re also welcome to add more ideas and vote for them here - Ideas

Retirement of Virtual Network Injection for Azure Data Explorer Extended to May 1st 2025

cosh23 — Fri, 31 Jan 2025 15:14:21 GMT

We have some important news for Azure Data Explorer users! The retirement date for Virtual Network Injection (VNet Injection) has been extended to May 1st, 2025. This extension provides users with additional time to transition their workloads and adapt their configurations to the new network capabilities of Private Endpoints.

This decision was made to ensure a smooth and seamless transition for all Azure Data Explorer users, allowing ample time to integrate and align with the updated network architecture. The Azure team remains committed to delivering top-notch service and support during this period.

Documentation: How to migrate from virtual network injection.

Please provide feedback on your migration.

Synapse Data Explorer (SDX) to Eventhouse Migration Capability (Preview)

Anshul_Sharma — Mon, 28 Apr 2025 17:39:31 GMT

Synapse Data Explorer (SDX), part of Azure Synapse Analytics, is an enterprise analytics service that enables you to explore, analyze, and visualize large volumes of data using the familiar Kusto Query Language (KQL). SDX has been in public preview since 2019.

The evolution of Synapse Data Explorer

The next generation of SDX offering is evolving to become Eventhouse, part of Real-Time Intelligence in Microsoft Fabric. Eventhouse offers the same powerful features and capabilities as SDX, but with enhanced scalability, performance, and security. Eventhouse is built on the same technology as SDX, and is compatible with all the applications, SDKs, integrations, and tools that work with SDX.

For existing customers considering a move to Fabric, we are excited to offer a seamless migration capability. You can now migrate your Data Explorer pools from Synapse workspace to Eventhouse effortlessly.

To initiate the migration of your SDX cluster to Eventhouse, simply follow the instructions. http://aka.ms/sdx.migrate

Query Acceleration for Delta External Tables (Preview)

Anshul_Sharma — Tue, 26 Nov 2024 11:11:48 GMT

An external table is a schema entity that references data stored external to a Kusto database. Queries run over external tables can be less performant than on data that is ingested due to various factors such as network calls to fetch data from storage, the absence of indexes, and more. Query acceleration allows specifying a policy on top of external delta tables. This policy defines a number of days to cache data for high-performance queries.

Query Acceleration policy allows customers to set a policy on top of external delta tables to define the number of days to cache. Behind the scenes, Kusto continuously indexes and caches the data for that period, allowing customers to run performant queries on top.

QAP is supported by Azure Data Explorer (ADX) over ADLSgen2/blob storage and Eventhouse over OneLake/ADLSgen2/blob storage.

Query Acceleration policy

We are introducing a new policy to enable acceleration for delta external tables:

Syntax

.alter external table <TableName> policy query_acceleration 'Policy'

Where:

<TableName> is the name of a Delta Parquet external table.
<Policy> is a string literal holding a JSON property bag with the following properties:
- IsEnabled : Boolean, required.
  - If true, query acceleration is enabled.
- Hot: TimeSpan, last 'N' days of data to cache.

Steps to enable Query Acceleration

Create a delta external table as described in this document:

.create-or-alter external table <TableName> kind=delta ( h@'https://storageaccount.blob.core.windows.net/container;<credentials> )

Set a query acceleration policy

.alter external table <TableName> policy query_acceleration ```{ "IsEnabled": true, "Hot": "36500d" }```

Query the table.

external_table('TableName')

Note: Indexing and caching might take some time depending on the volume of data and cluster size. For monitoring the progress, see Monitoring command

Costs/Billing

Enabling Query Acceleration does come with some additional costs. The accelerated data will be ingested in Kusto and count towards the SSD storage, similar to native Kusto tables. You can control the amount of data to accelerate by configuring number of days to cache.

Conclusion

Query Acceleration is a powerful feature designed to enhance your data querying capabilities on PetaBytes of data. By understanding when and how to use this feature, you can significantly improve the efficiency and speed of your data operations - whether you are dealing with large datasets, complex queries, or real-time analytics, Query Acceleration provides the performance boost you need to stay ahead.

Get started with Azure Data Explorer.
Get started with Eventhouse in Microsoft Fabric.

Exploring the New Kraph Features: Unlocking Powerful Patterns and Operations

cosh23 — Mon, 11 Nov 2024 12:47:28 GMT

Kusto Graph Semantics have always been a powerful tool for representing and analyzing complex data structures. With the recent release, we are pleased to introduce a suite of enhancements designed to simplify and enrich your data analysis experience. In this blog post, we delve into the new features including the star pattern, default node id, graph-shortest-path, and graph-mark-components.

The Star Pattern

One of the most exciting additions is the "star pattern," which allows users to express nonlinear patterns using multiple comma-delimited sequences. This feature is particularly useful for describing connections where different sequences share one or more variable names of a node. For instance, consider a scenario where a node 'n' is at the center of a star, connected to nodes a, b, c, and d. The following pattern can be used:

(a)--(n)--(b),(c)--(n)--(d).

This feature in now generally available (Learn more).

Defining the Default Node ID

Creating a graph from a tabular expression of edges has never been easier with the new approach for defining the default node id. This feature ensures that the node identifier is readily available for the constraints section of the subsequent graph-match operator. By setting a default node id, you streamline the process of graph creation and enhance the precision of your data queries.

This feature is now generally available (Learn more).

Graph-Shortest-Path

Finding the shortest path between nodes is a fundamental operation in graph analysis, and the graph-shortest-path operator makes this task more efficient than ever. This operator identifies the shortest paths between a set of source nodes and a set of target nodes within a graph, returning a table with the results. Whether you're navigating social networks, optimizing logistical routes, or exploring intricate data relationships, this feature is indispensable for uncovering the most direct connections.

This feature is now in public preview (Learn more).

Graph-Mark-Components

The graph-mark-components operator is designed to find all connected components of a graph and mark each node with a unique component identifier. This feature is crucial for identifying and distinguishing different clusters within your data. By marking each node with a component identifier, you can easily analyse the structure and connectivity of your graph, leading to deeper insights and more informed decisions.

This feature is now in public preview (Learn more).

Conclusion

The latest graph features offer robust tools for enhancing your data analysis capabilities. From the intuitive star pattern to the precise definition of default node ids, and from the efficiency of graph-shortest-path to the clarity of graph-mark-components, these enhancements empower you to delve deeper into your graphs and extract meaningful insights. Embrace these new features and unlock the full potential of your data with ease and precision.

Stay tuned for more updates and tutorials on how to leverage these powerful graph features to their fullest extent.

Country and Region Information in current_principal_details

cosh23 — Mon, 21 Oct 2024 12:19:08 GMT

Kusto has introduced a new feature that allows users to access information about the country of a user and their tenant region or country as provided by Microsoft Entra ID through the current_principal_details() function. This addition provides enhanced granularity and control in data security and accessibility.

For the function to provide this information, it is essential to understand the authentication (AuthN) and authorization (AuthZ) flow for a query in Kusto.

It begins with the client application requesting access to the Kusto service. The client uses the Microsoft Authentication Library (MSAL) to acquire an access token from Microsoft Entra ID, which serves as proof of the client’s identity. This access token is included in the authorization header of the request. Upon receiving the request, Kusto validates the access token to ensure it is issued by a trusted authority and is still valid. Next, Kusto checks the roles assigned to the authenticated principal to determine if they have the necessary permissions to execute the query. If the principal is authorized, the query is executed; otherwise, access is denied. In the case of current_principal_details(), the function extracts information from optional claims in the token to enrich the result about the identity. The newly added properties are:

Country – based on the optional claim “ctry” (standard two-letter country/region code)
TenantCountry – based on the optional claim “tenant_ctry” (standard two-letter country/region code configured by a tenant admin)
TenantRegion – based on the optional claim “tenant_region_scope” (standard two-letter region code of the resource tenant)

The following Kusto Query Language (KQL) statement prints the information of the Entra ID user Alice:

print details=current_principal_details()

The result of the function provides detailed information about the authenticated user, Alice.

{ "Country": "DE", "TenantCountry": "US", "TenantRegion": "WW", "UserPrincipalName": "alice@contoso.com", "Type": "aaduser", "IdentityProvider": "https://sts.windows.net", "DisplayName": "Alice (upn: alice@contoso.com)", "Authority": "<tenantId>", "ObjectId": "<objectId>", "Mfa": "True", "FQN": "aaduser=<objectId;tenantId " }

With the integration of location information, users are now able to formulate advanced Row Level Security (RLS) policies. These policies can control access to specific rows based on the data provided by Entra ID tokens. This capability is particularly advantageous for organizations operating across multiple countries or regions, as it ensures that sensitive data is accessible only to authorized individuals within specified locations.

The ContosoSales table provides a straightforward yet illustrative dataset that includes sales information segmented by country. The table comprises two columns: Country and Product, with corresponding Amount of sales. For instance, it shows that 10 units of Espresso were sold in Germany (DE) and 5 units in the United States (US). This data can be used to implement and test Row Level Security policies based on geographical location, ensuring that access to sales data is restricted according to the specified country codes.

Country	Product	Amount
DE	Espresso	10
US	Espresso	5

The following function can be used as a predicate in Row Level Security policy:

.create-or-alter function RLSForContoso(TableName: string) { table(TableName) | where Country == current_principal_details()["Country"] }

A user with the Country property set to "DE" in Entra ID will get the following result when querying the ContosoSales table:

Country	Product	Amount
DE	Espresso	10

Please note that the information provided by Entra ID is based on static properties configured in the user's profile. Therefore, it does not necessarily represent the user's actual location at the time the query is executed. For example, a user with the Country attribute set to "DE" might not be physically located in Germany when the query runs.

This new capability not only bolsters data security but also enhances compliance with regional data protection regulations. By leveraging the properties from Microsoft Entra ID, enterprises can enforce their data governance policies more effectively and with greater precision.

The introduction of Country/Region-based filtering in Kusto RLS policies underscores Microsoft's commitment to providing robust, secure, and versatile data management solutions. As organizations navigate the complexities of data privacy and security, this feature offers a critical tool for maintaining control over their data landscape.

Stay tuned for more updates and detailed guides on how to implement and make the most out of this exciting new feature!

Performing ETL in Real-Time Intelligence with Microsoft Fabric

gurkamal — Tue, 15 Oct 2024 16:14:02 GMT

Introduction

In today’s data-driven world, the ability to act upon data as soon as its generated is crucial for businesses to make informed decisions quickly. Organizations seek to harness the power of up-to-the-minute data to drive their operations, marketing strategies, and customer interactions.

This becomes challenging in the world of real-time data where it is not always possible to do all the transformations while the data is being streamed. Therefore, you must come up with a flow that does not impact the data stream and is also quick.

This is where Microsoft Fabric comes into play. Fabric offers a comprehensive suite of services including Data Engineering, Data Factory, Data Science, Real-Time Intelligence, Data Warehouse, and Databases. But today, we are going to focus on Real-Time Intelligence.

Use-Cases

This set up can be used in scenarios where data transformation is needed to be used in downstream processing/analytical workload. As example of this would be to enable OneLake availability in KQL table and use that data to be accessed by other Fabric engines like Notebooks, Lakehouse etc. for training ML models/analytics.

Another example let's say you have a timestamp column in your streaming data and you would like to change its format based on your standard. You can use the update policy to transform the timestamp data format and store it.

Overview

Fabric Real-Time Intelligence supports KQL database as its datastore which is designed to handle real-time data streams efficiently. After ingestion, you can use Kusto Query Language (KQL) to query the data in the database.

KQL Table is a Fabric item which is part of the KQL Database. Both these entities are housed within an Eventhouse. An Eventhouse is a workspace of databases, which might be shared across a certain project. It allows you to manage multiple databases at once, sharing capacity and resources to optimize performance and cost. Eventhouses provide unified monitoring and management across all databases and per database.

Figure 1: Hierarchy of Fabric items in an Eventhouse

Update policies are automated processes activated when new data is added to a table. They automatically transform the incoming data with a query and save the result in a destination table, removing the need for manual orchestration. A single table can have multiple update policies for various transformations, saving data to different tables simultaneously. These target tables can have distinct schemas, retention policies, and other configurations from the source table.

Scope

In this blog, we have a scenario where we will be doing data enrichment on the data that lands in the KQL table. In this case, we will be dropping the columns we don’t need but you can also do other transformations supported in KQL on the data.

Here we have a real-time stream pushing data to a KQL table. Once loaded into the source table, we will use an update policy which will drop columns not needed and push the data of interest to the destination table from the source table.

Prerequisites

A Microsoft account or a Microsoft Entra user identity. An Azure subscription isn't required.
Fabric Capacity. If you don't have one, you can sign-up for Fabric Trial Capacity.
A KQL database in Real-Time Intelligence in Microsoft Fabric.

Creating sample data stream

In the Real-Time Intelligence experience, create a new event stream.
Under source, add new source and select sample data.
Continue configuring the stream. I am using the Bicycles sample data stream in this blog.
Select Direct ingestion as the Data Ingestion Mode for destination.
Select your workspace and KQL database you have created as a prerequisite to this exercise for the destination.
You should be seeing a pop-up to configure the database details and continue to configure the table where you need to land the data from the stream.

Configuring KQL Table with Update Policy

Open the Eventhouse page in Fabric. There you should now be able to preview the data that is being ingested from the sample data stream.
Create a new destination table. I used the following KQL to create the new table (destination):
.create table RTITableNew ( BikepointID: string,Street: string, Neighbourhood: string, No_Bikes: int, No_Empty_Docks: int )
Under the Database tab, click on new and select Table Update Policy.
You can edit the existing policy format or paste the one below that I used:
NOTE: RTITable is source and RTITableNew is the destination table.
.alter table RTITable policy update ```[ { "IsEnabled": true, "Source": "RTITable", "Query": "RTITable | project BikepointID=BikepointID, Street=Street, Neighbourhood=Neighbourhood, No_Bikes=No_Bikes, No_Empty_Docks=No_Empty_Docks ", "IsTransactional": true, "PropagateIngestionProperties": false, "ManagedIdentity": null } ]```
The above policy drops the Longitude and Latitude columns and stores the rest of the columns in the destination table. You can do more transformations as per your requirements, but the workflow remains the same.
After running the above command, your destination table will start populating with the new data as soon as the source table gets data. To review the policy on the destination table, you can run the following command:
.show table <table-name> policy update

Conclusion

To summarize, we took a real-time data stream, stored the data in a KQL database and then performed data enrichment on the data and stored in a destination table. This flow caters the scenarios where you want to perform processing on the data once its ingested from the stream.

Time Weighted Average and Value in Azure Data Explorer

adieldar — Sun, 29 Sep 2024 16:02:00 GMT

Azure Data Explorer (ADX) supports time series aggregation at scale, either by the summarize operator that keeps the aggregated data in tabular format or by the make-series operator that transforms it to a set of dynamic arrays. There are multiple aggregation functions, out of them avg() is one of the most popular. ADX calculates it by grouping the samples into fixed time bins and applying simple average of all samples inside each time bin, regardless of their specific location inside the bin. This is the standard time bin aggregation as done by SQL and other databases. However, there are scenarios where simple average doesn’t accurately represent the time bin value. For example, IoT devices sending data commonly emits metric values in an asynchronous way, only upon change, to conserve bandwidth. In that case we need to calculate Time Weighted Average (TWA), taking into consideration the exact timestamp and duration of each value inside the time bin. ADX doesn’t have native aggregation functions to calculate time weighted average, still we have just added few User Defined Functions, part of the Functions Library, supporting it:

time_weighted_val_fl() - Calculates the time weighted value of a metric using linear interpolation.
time_weighted_avg_fl() - Calculates the time weighted average of a metric using fill forward interpolation.
time_weighted_avg2_fl() - Calculates the time weighted average of a metric using linear interpolation.

Here is a query comparing the original & interpolated values, standard average by the summarize operator, twa using fill forward and twa using linear interpolation:

let tbl = datatable(ts:datetime, val:real, key:string) [ datetime(2021-04-26 00:00), 100, 'D1', datetime(2021-04-26 00:45), 300, 'D1', datetime(2021-04-26 01:15), 200, 'D1', ]; let stime=datetime(2021-04-26 00:00); let etime=datetime(2021-04-26 01:15); let dt = 1h; // tbl | where ts between (stime..etime) | summarize val=avg(val) by bin(ts, dt), key | project-rename _ts=ts, _key=key | extend orig_val=0 | extend _key = strcat(_key, '-SUMMARIZE'), orig_val=0 | union (tbl | invoke time_weighted_val_fl('ts', 'val', 'key', stime, etime, dt) | project-rename val = _twa_val | extend _key = strcat(_key, '-SAMPLES')) | union (tbl | invoke time_weighted_avg_fl('ts', 'val', 'key', stime, etime, dt) | project-rename val = tw_avg | extend _key = strcat(_key, '-TWA-FF'), orig_val=0) | union (tbl | invoke time_weighted_avg2_fl('ts', 'val', 'key', stime, etime, dt) | project-rename val = tw_avg | extend _key = strcat(_key, '-TWA-LI'), orig_val=0) | order by _key asc, _ts asc // use anomalychart just to show original data points as bold dots | render anomalychart with (anomalycolumns=orig_val, title='Time Wighted Average, Fill Forward & Linear interpolation')

Explaining the results:

	2021-04-26 00:00	2021-04-26 00:00
Interpolated value	100	(300+200)/2=250
Average by summarize	(100+300)/2=200	200
Fill forward TWA	(45m100 + 15m300)/60m = 150	(15m300 + 45m200)/60m = 225
Linear interpolation TWA	45m(100+300)/2 + 15m(300+250)/2)/60m = 218.75	15m(250+200)/2 + 45m200)/60m = 206.25

Note that all functions work on multiple time series, partitioned by supplied key.

You are welcome to try these functions and share your feedback!

New Custom and Managed Python images in Azure Data Explorer

adieldar — Thu, 19 Sep 2024 12:45:42 GMT

Azure Data Explorer (ADX) supports running Python code embedded in KQL query using the python() plugin . The plugin runtime is hosted in a sandbox, an isolated and secured environment hosted on ADX existing compute nodes. This sandbox contains the language engine as well as common mathematical and scientific packages. The plugin extends KQL native functionalities with a huge archive of OSS packages, enabling ADX users to run advanced algorithms, such as machine learning, statistical tests, time series analysis and many more, as part of the KQL query.

We are constantly working to improve the Python plugin capabilities, and today we introduce new managed Python images as well as the option to fully customize the Python image to include your required Python packages:

Managed images are Python environment that are built and maintained by the Kusto team, containing specific Python engine and a set of packages. We are adding today Python 3.11.7 and Python 3.11.7 DL (containing torch & tensorflow), both images contain up to date Python engine 3.11.7 and packages (you can review the full contents of these images by clicking the respective links).
Custom images let you create your specific Python images in case you need additional packages or different versions of the Python engine and/or packages. You can create a custom image from scratch by specifying the Python engine and supplying a requirements text file containing full list of packages, or by selecting a base existing image and supplying a minimal requirements file containing only the additional packages to install on top of the base image.

Defining a custom Python image from ADX portal

For further information see Create a custom image - Azure Data Explorer | Microsoft Learn and Python plugin packages - Kusto | Microsoft Learn

You are welcome to try it and share your feedback!

Advanced Time Series Anomaly Detector in Fabric

adieldar — Thu, 29 Aug 2024 10:31:03 GMT

Introduction

Anomaly Detector, one of Azure AI services, enables you to monitor and detect anomalies in your time series data. This service is based on advanced algorithms, SR-CNN for univariate analysis and MTAD-GAT for multivariate analysis and is being retired by October 2026. In this blog post we will lay out a migration strategy to Microsoft Fabric, allowing you to detect identical anomalies, using the same algorithms as the old service, and even more. Here are a few of the benefits of the strategy that we are about to lay out for you:

Easier management of the trained models’ lifecycle using Fabric ML.
No need to upload your data to external storage account, just stream your data to Fabric Eventhouse with OneLake availability and you can use it for training and scoring.
You can use your data by any Fabric engine (KQL DB, Fabric ML Notebook, PBI and more)
The algorithms are open sourced and published by the new time-series-anomaly-detector · PyPI package, thus you can review and tweak them as needed.

Time Series Anomaly Detection in Fabric RTI

There are few options for time series anomaly detection in Fabric RTI (Real Time Intelligence):

For univariate analysis, KQL contains the native function series_decompose_anomalies() that can perform anomaly detection on thousands of time series in seconds. For further info on using this function take a look at Time series anomaly detection & forecasting in Azure Data Explorer.
For multivariate analysis, there are few KQL library functions leveraging known multivariate analysis algorithms in scikit-learn , taking advantage of ADX capability to run inline Python as part of the KQL query. For further info see Multivariate Anomaly Detection in Azure Data Explorer - Microsoft Community Hub.
For both univariate and multivariate analysis you can now use the new workflow, which is based on the time-series-anomaly-detector package, as described below.

Using time-series-anomaly-detector in Fabric

In the following example we shall

Upload stocks change table to Fabric
Train the multivariate anomaly detection model in a Python notebook using Spark engine
Predict anomalies by applying the trained model to new data using Eventhouse (Kusto) engine

Note that for the univariate model there is no need to train the model in a separate step (as the training is fast and done internally) and we can just predict.

Below we briefly present the steps, see Multivariate anomaly detection - Microsoft Fabric | Microsoft Learn for the detailed tutorial.

Creating the environments

Create a Workspace
Create Eventhouse – to store the incoming streaming data
- Enable OneLake availability – so the older data that was ingested to the Eventhouse can be seamlessly accessed by the Spark Notebook for training the anomaly detection model
- Enable KQL Python plugin – to be used for real time predictions of anomalies on the new streaming data. Select 3.11.7 DL image that contains the time-series-anomaly-detector package
Create a Spark environment that includes the time-series-anomaly-detector package

Training & storing the Anomaly Detection model

Upload the stocks data to the Eventhouse
Create a notebook to train the model

Load the data from the Eventhouse using the OneLake path:

onelake_uri = "OneLakeTableURI" # Replace with your OneLake table URI abfss_uri = convert_onelake_to_abfss(onelake_uri) df = spark.read.format('delta').load(abfss_uri) df = df.toPandas().set_index('Date')

View the data:

import plotly.graph_objects as go fig = go.Figure() fig.add_trace(go.Scatter(x=df.index, y=df['AAPL'], mode='lines', name='AAPL')) fig.add_trace(go.Scatter(x=df.index, y=df['AMZN'], mode='lines', name='AMZN')) fig.add_trace(go.Scatter(x=df.index, y=df['GOOG'], mode='lines', name='GOOG')) fig.add_trace(go.Scatter(x=df.index, y=df['MSFT'], mode='lines', name='MSFT')) fig.add_trace(go.Scatter(x=df.index, y=df['SPY'], mode='lines', name='SPY')) fig.update_layout( title='Stock Prices change', xaxis_title='Date', yaxis_title='Change %', legend_title='Tickers' ) fig.show()

Prepare the data for training:

features_cols = ['AAPL', 'AMZN', 'GOOG', 'MSFT', 'SPY'] cutoff_date = pd.to_datetime('2023-01-01') train_df = df[df.Date < cutoff_date]

Train the model:

import mlflow from anomaly_detector import MultivariateAnomalyDetector model = MultivariateAnomalyDetector() sliding_window = 200 param s = {"sliding_window": sliding_window} model.fit(train_df, params=params)

Save the model in Fabric ML model registry

with mlflow.start_run(): mlflow.log_params(params) mlflow.set_tag("Training Info", "MVAD on 5 Stocks Dataset") model_info = mlflow.pyfunc.log_model( python_model=model, artifact_path="mvad_artifacts", registered_model_name="mvad_5_stocks_model", )

Extract the mode path (to be used by the Eventhouse for the prediction):

mi = mlflow.search_registered_models(filter_string="name='mvad_5_stocks_model'")[0] model_abfss = mi.latest_versions[0].source print(model_abfss)

Create a Query set and attached the Eventhouse to it
- Run the ‘.create-or-alter function’ query to define predict_fabric_mvad_fl() stored function:

.create-or-alter function with (folder = "Packages\\ML", docstring = "Predict MVAD model in Microsoft Fabric") predict_fabric_mvad_fl(samples:(*), features_cols:dynamic, artifacts_uri:string, trim_result:bool=false) { let s = artifacts_uri; let artifacts = bag_pack('MLmodel', strcat(s, '/MLmodel;impersonate'), 'conda.yaml', strcat(s, '/conda.yaml;impersonate'), 'requirements.txt', strcat(s, '/requirements.txt;impersonate'), 'python_env.yaml', strcat(s, '/python_env.yaml;impersonate'), 'python_model.pkl', strcat(s, '/python_model.pkl;impersonate')); let kwargs = bag_pack('features_cols', features_cols, 'trim_result', trim_result); let code = ```if 1: import os import shutil import mlflow model_dir = 'C:/Temp/mvad_model' model_data_dir = model_dir + '/data' os.mkdir(model_dir) shutil.move('C:/Temp/MLmodel', model_dir) shutil.move('C:/Temp/conda.yaml', model_dir) shutil.move('C:/Temp/requirements.txt', model_dir) shutil.move('C:/Temp/python_env.yaml', model_dir) shutil.move('C:/Temp/python_model.pkl', model_dir) features_cols = kargs["features_cols"] trim_result = kargs["trim_result"] test_data = df[features_cols] model = mlflow.pyfunc.load_model(model_dir) predictions = model.predict(test_data) predict_result = pd.DataFrame(predictions) samples_offset = len(df) - len(predict_result) # this model doesn't output predictions for the first sliding_window-1 samples if trim_result: # trim the prefix samples result = df[samples_offset:] result.iloc[:,-4:] = predict_result.iloc[:, 1:] # no need to copy 1st column which is the timestamp index else: result = df # output all samples result.iloc[samples_offset:,-4:] = predict_result.iloc[:, 1:] ```; samples | evaluate python(typeof(*), code, kwargs, external_artifacts=artifacts) }

Run the prediction query that will detect multivariate anomalies on the 5 stocks, based on the trained model, and render it as anomalychart. Note that the anomalous points are rendered on the first stock (AAPL), though they represent multivariate anomalies, i.e. anomalies of the vector of the 5 stocks in the specific date.

let cutoff_date=datetime(2023-01-01); let num_predictions=toscalar(demo_stocks_change | where Date >= cutoff_date | count); // number of latest points to predict let sliding_window=200; // should match the window that was set for model training let prefix_score_len = sliding_window/2+min_of(sliding_window/2, 200)-1; let num_samples = prefix_score_len + num_predictions; demo_stocks_change | top num_samples by Date desc | order by Date asc | extend is_anomaly=bool(false), score=real(null), severity=real(null), interpretation=dynamic(null) | invoke predict_fabric_mvad_fl(pack_array('AAPL', 'AMZN', 'GOOG', 'MSFT', 'SPY'), // NOTE: Update artifacts_uri to model path artifacts_uri='enter your model URI here', trim_result=true) | summarize Date=make_list(Date), AAPL=make_list(AAPL), AMZN=make_list(AMZN), GOOG=make_list(GOOG), MSFT=make_list(MSFT), SPY=make_list(SPY), anomaly=make_list(toint(is_anomaly)) | render anomalychart with(anomalycolumns=anomaly, title='Stock Price Changest in % with Anomalies')

Summary

The addition of the time-series-anomaly-detector package to Fabric makes it the top platform for univariate & multivariate time series anomaly detection. Choose the anomaly detection method that best fits your scenario – from native KQL function for univariate analysis at scale, through standard multivariate analysis techniques and up to the best of breed time series anomaly detection algorithms implemented in the time-series-anomaly-detector package. For more information see the overview and tutorial.

Visualizing Data as Graphs with Fabric and KQL

BrianSherwin — Tue, 20 Aug 2024 19:15:48 GMT

Introduction

For quite a while, I have been extremely interested in data visualization. Over the last few years, I have been focused on ways to visualize graph databases (regardless of where the data comes from Using force directed graphs to highlight the similarities or “connected communities” in data is incredibly powerful. The purpose of this post is to highlight the recent work that the Kusto.Explorer team has done to visualize graphs in Azure Data Explorer database with data coming from a Fabric KQL Database.

Note: The Kusto.Explorer application used to visualize the graph is currently only supported on Windows.

Background

Azure Data Explorer (ADX) is Microsoft’s fully managed, high-performance analytics engine specializing in near real time queries on high volumes of data. It is extremely useful for log analytics, time-series and Internet of Things type scenarios. ADX is like traditional relational database models in that it organizes the data into tables with strongly typed schemas.

In September 2023, the ADX team introduced extensions to the query language (KQL) that enabled graph semantics on top of the tabular data. These extensions enabled users to contextualize their data and its relationships as a graph structure of nodes and edges. Graphs are often an easier way to present and query complex or networked relationships. These are normally difficult to query because they require recursive joins on standard tables. Examples of common graphs include social networks (friends of friends), product recommendations (similar users also bought product x), connected assets (assembly line) or a knowledge graph.

Fast forward to February 2024, Microsoft Fabric introduced Eventhouse as a workload in a Fabric workspace. This brings forward the power of KQL and Real-Time analytics to the Fabric ecosystem.

So now, I have a large amount of data in Fabric Eventhouse that I want to visualize with a force directed graph…

Let’s get started!

Pre-Requisites

If you want to follow along, you will need a Microsoft Fabric account (Get started with Fabric for Free).

Next, for this post, I used an open dataset from the Bureau of Transportation Statistics. The following files were used:

Aviation Support Tables – Master Coordinate data
- When you download this file, you can choose the fields to be included in it. For this example, I only used AirportID, Airport, AirportName, AirportCityName and AirportStateCode.
- This Airport data will be loaded directly to a table in KQL.
- This file does not necessarily need to be unzipped.
Airline Service Quality Performance 234 (On-Time performance data)
- For this blog, I only used the “April 2024” file from this link.
- This data will be accessed using a Lakehouse shortcut.
- Unzip this file to a local folder and change the extension from “.asc” to “.psv” because this is a pipe-separated file.

In order to use these downloaded files, I uploaded them to the “Files” section of the Lakehouse in my Fabric Workspace. If you do not have a Lakehouse in your workspace, first, navigate to your workspace and select “New” -> “More Options” and choose “Lakehouse” from the Data Engineering workloads. Give your new Lakehouse a name and click “Create”.

Once you have a Lakehouse, you can upload the files by clicking on the Lakehouse to bring up the Lakehouse Explorer. First, in the Lakehouse Explorer, click the three dots next to “Files” and select “New subfolder” and create a folder for “Flights”. Next, click the three dots next to the “Flights” sub-folder and select “Upload” from the drop-down menu and choose the on-time performance file. Confirm that the file is uploaded to files by refreshing the page.

Next, an Eventhouse will be used to host the KQL Cluster where you will ingest the data for analysis. If you do not have an Eventhouse in your workspace, select “New” -> “More Options” and choose “Eventhouse” from “Real-Time Intelligence” workloads. Give your new Eventhouse a name and click “Create”.

Finally, we will use the Kusto.Explorer application (available only for Windows) to visualize the graph. This is a one-click deployment application, so it is possible that it will run an application update when you start it up.

Ingest Data to KQL Database

When the Eventhouse was created, a default KQL database with the same name was created. To get data into the database, click the three dots next to the database name, select “Get Data” -> “Local File”. In the dialog box that pops up, in the “Select or create a destination table”, click the “New table” and give the table a name, in this case it will be “airports”. Once you have a valid table name, the dialog will update to drag or browse for the file to load.

Note: You can upload files in a compressed file format if it is smaller than 1GB.

Click “Next” to inspect the data for import. For the airports data, you will need to change the “Format” to CSV and enable the option for “First row is column header”.

Click “Finish” to load the file to the KQL table.

The airport data should now be loaded into the table, and you can query the table to view the results.

Here is a sample of query to verify that data was loaded:

airports | take 100;

For the On-Time performance data, we will not ingest it into KQL. Instead, we will create a shortcut to the files in the Lakehouse storage.

Back in the KQL Database explorer, at the top, click on the “+ New -> OneLake shortcut” menu item.

In the dialog that comes up, choose “Microsoft OneLake” and in the “Select a data source type”, choose the Lakehouse where the data was uploaded earlier, and click “Next”

Once the tree view of the OneLake populates the Tables and Files, open the files, and select the subfolder that was created when uploading the On-Time data, and click “Create” to complete the shortcut creation.

Once the shortcut is created, you can view that data by clicking the “Explore your data” and running the following query to validate your data.

external_table(‘flights’) | count;

Note: When accessing the shortcut data, use the “external_table” and the name of the shortcut that was created. You cannot change the shortcut name.

Query and Visualize with Kusto.Explorer

Now that the data is connected to an Eventhouse database, we want to start to do analytics on this data. Fabric does have a way to run KQL Queries directly, but the expectation is that the results of the query will be a table. The only way to show the graph visualization is to use the Kusto.Explorer.

To connect to the KQL database, you need to get the URI of the cluster from Fabric. Navigating to the KQL Database in Fabric, there is a panel that includes the “Database details”.

Using the “Copy URI” to the right of the Query URI will copy the cluster URI to the clipboard.

In the Kusto.Explorer application, right click the “Connections” and select “Add Connection”

In the popup, paste the Query URI into the “Cluster connection” textbox replacing the text that is there. You can optionally give the connection an alias rather than using the URI. Finally, I chose to use the AAD for security. You can choose whatever is appropriate for your client access.

At this point, we can open a “New Tab” (Home menu) and type in the query like what we used above.

let nodes = airports; let edges = external_table('flights') | project origin = Column7, dest = Column8, flight = strcat(Column1, Column2), carrier = Column1; edges | make-graph origin --> dest with nodes on AIRPORT

Note: You may need to modify the table names (airports, flights) depending on the shortcut or table name you used when loading the data. These values are case-sensitive.

The points of interest in our graph will be the airports (nodes) and the connections (edges) will be the individual flights that were delayed. I am using the “make-graph” extension in KQL to make a graph of edges from origin to destination using the three-character airport code as link.

Visualize with “make-graph”

When this query is run, if the last line of the query is “make-graph”, Kusto.Explorer will automatically pop up a new window titled “Chart” to view the data. In the image below, I chose to change the visualization to a dark theme and then colored the edges based on the “carrier” column of the flight data.

Note: I have zoomed in on the cluster of interest.

If I drag a few of the nodes around, I can start to see there are some nodes (airports) with a lot of orange connections. If I click on an orange link, I quickly learn the orange lines are Delta Flights and the three nodes I pulled out in the image below are Atlanta, Minneapolis, and Detroit.

Conclusion

I started with tables of text-based data and ended with a nice “network” visualization of my flights data. The power of graph visualization to see the relationships between my data rather than just reading tables is invaluable.

Next, I am excited to start to explore visualizations of the data for supply chains and product recommendations.

ADX Web UI updates - July 2023

Michal_Bar — Wed, 14 Aug 2024 13:51:32 GMT

Welcome to the July 2024 update. We are excited to announce new features and improvements in ADX web UI.

Continue reading to learn more about:

Copy query with syntax coloring and KQL IntelliSense improvements
Ad-hoc visual data exploration
Dashboards real time refresh rate
Enhanced data interaction for dashboard tiles
Resize and move dashboard tiles using keyboard only

Copy query with syntax coloring and KQL IntelliSense improvements

We’ve enhanced the experience of sharing queries with your team by fixing a long-standing bug that impacted syntax colorization when copying and pasting.

Whether you use Ctrl+C/Ctrl+V or copy the query directly from the UI, the syntax colorization now persists when pasting into Outlook.

In Outlook, make sure to select “Keep source formatting” option when pasting

This improvement makes the query easier to read and understand, ensuring smoother collaboration and communication.

Additionally, we’re pleased to introduce several incremental improvements for KQL writers, focusing on enhanced Intellisense support.

The Intellisense list now provides relevant suggestions without the need to add a space, and it filters results based on user input, excluding function parameter names for more accurate matches. Additionally, quotes and brackets are automatically closed, streamlining the writing process.

Completion items now prioritize displaying columns at the top, and the Intellisense list is sorted alphabetically for easier navigation.

Introducing ad-hoc visual Data Exploration feature

Our new "Data Exploration" feature allows you to dive deeper into the data on any dashboard, extending your exploration beyond the displayed tiles to uncover new insights. This user-friendly, form-like interface lets you add filters, create aggregations, and switch visualization types without writing queries. Now, you can explore data ad-hoc, leveraging existing tiles to start your journey and expand your data view.

Documentation: Explore data in dashboard tiles (preview) - Azure Data Explorer | Microsoft Learn

Dashboards real time refresh rate

We are excited to announce an enhancement to our dashboard auto refresh feature, now supporting continuous and 10 seconds refresh rates, in addition to the existing options.

This upgrade, addressing a popular customer request, allows both editors and viewers to set near real-time and real-time data updates, ensuring your dashboards display the most current information with minimal delay. Experience faster data refresh and make more timely decisions with our improved dashboard capabilities.

As the dashboard author you can enable the Auto refresh setting and set a minimum time interval, to prevent users from setting an auto refresh interval smaller than the provided value.
Note that the Continuous option should be used with caution. The data is refreshed every second or after the previous refresh completes if it takes more than 1 second.

Enhanced Data Interaction for Dashboard Tiles

We are excited to announce new capabilities that enhance your interaction with data presented visually in dashboard tiles, particularly when multiple data series are involved.

You can interact with the data by selecting specific items from the legend using the mouse, using Ctrl to add or remove selections, or holding Shift to select a range.

The Search button helps you quickly filter items, while the Invert button allows you to reverse your selections.

Navigate through your selections with ease using the Up and Down arrows to refine your data view.

Note that users with edit rights on a dashboard, can customize the legend location in their tiles, improving readability and data interpretation.

Resize dashboard tiles using keyboard only

We are excited to introduce a new accessibility feature that allows users to resize dashboard tiles using only the keyboard.

By pressing the Tab key, you can focus on a tile, and then use the arrow keys to move it.

To resize, hold the Shift key and use the arrow keys: right to increase width, left to decrease width, down to increase height, and up to decrease height.

This functionality mirrors the ease of moving and resizing with a mouse, enhancing the accessibility and usability of ADX web UI.

Azure Data Explorer Web UI team is looking forward for your feedback in KustoWebExpFeedback@service.microsoft.com

You’re also welcome to add more ideas and vote for them here - https://aka.ms/adx.ideas

Read more:

ADX Web May updates – ADX Web updates – May 2024 - Microsoft Community Hub

Store images in Kusto and visualize them with Power BI or Azure Data Explorer Dashboards

Hauke — Mon, 12 Aug 2024 06:45:28 GMT

How to Visualize Images Stored in Kusto

Introduction

Kusto is a fast and scalable database designed to ingest, store, and analyze large volumes of structured and semi-structured data. For non-structured data like images, Azure Storage is typically the best choice. Databases can reference image data on storage via a URL, meaning images are not directly stored in Kusto. However, there are scenarios where storing image data in Kusto is beneficial. In this blog post, we will explore when it makes sense to store images in Kusto, how to store them, and how to visualize this data using Azure Data Explorer dashboards or Power BI.

Why store images in Kusto?

Although Kusto doesn’t support binary data types, there are still compelling reasons to store images in Azure Data Explorer. For dashboards and reports that require images, visualization tools might not support secure access to external storage. By leveraging identities and network segregation via managed private endpoints, storing all data in one location simplifies both access and security. However, it’s important to note that Kusto is not the best technology for storing large-scale images.

How to store images in Kusto

Kusto does not support binary data types, so images must be encoded in base64. This encoding converts the data into a non-human-readable string of 64 English characters. When storing an image in Kusto using base64, it is saved as a string.The default size limit for a string in Kusto is 1 MB (see Kusto Documentation for string datatype.) By default, all columns in Kusto are indexed. For columns storing images, you should disable indexing and may need to increase the default size limit. Below is an example of creating an image table, disabling indexing, and increasing the string size limit to 2 MB using the the BigObject encoding type:

// create image table
.create table image (file_name:string, img_original_base64 : string )

// This policy disables the index of the image column and overrides MaxValueSize property in the encoding Policy to 2 MB:
.alter column image.img_original_base64 policy encoding type='BigObject'

The maximum size for a string in Kusto is 32 MB. For more details, refer to the documentation on the encoding policy.

Ingest images to Kusto

You can use all available ingestion methods for the Kusto database, depending on the deployment (PaaS or SaaS). Ensure that the image data is converted to a binary string and encoded to base64, as described in the previous section. You can find a Python example in the Gist referenced at the end of this article.

Display images in Azure Data Explorer Dashboards

Once you've ingested image data into a Kusto table, you might want to visualize it using Azure Data Explorer Dashboards. Markdown visuals are an effective way to display images. Typically, images are displayed from a storage location using the following markdown pattern:

![alt text](path/to/image.png)

For images stored in Kusto, the process is similar. Instead of linking to a storage location, you use the field containing the base64-encoded string of the image. Here's how you can do it:

| extend image=strcat("![image](data&colon;image/png;base64,", img_original_base64, ")" )

This method embeds the image directly into the dashboard using the base64-encoded string from your data. If you have multiple images to display you can make use of a function generating a markdown from a Kusto query. The function logic has been shared on stackoverflow by Daniel Dror:

.create-or-alter function with (folder = "Gold layer", docstring = "function converts a table to markdown", skipvalidation = "true") table_to_markdown(t:(*)) {
let schema = t | getschema;
let headers = schema | project ColumnName | summarize make_list(ColumnName) | extend String = strcat('| ', strcat_array(list_ColumnName, ' | '), ' |') | project String, Order=1;
let upper_divider = schema | project ColumnName, Sep = '---' | summarize Cols=make_list(Sep) | extend String = strcat('| ', strcat_array(Cols, ' | '), ' |') | project String, Order=2;
let data = t | extend Cols=pack_array(*) | extend String = strcat('| ', strcat_array(Cols, ' | '), ' |') | project String, Order=3;
headers 
| union upper_divider
| union data
| order by Order asc 
| summarize Rows=make_list(String) 
| project array_strcat(Rows, '\r\n')
}

With invoking this function, you can easily display a table of images in Azure Data Explorer dashboards. The following query is used in combination with a markdown visual:

image
| project file_name, img_original_base64
| extend ingestion_time=ingestion_time() 
| summarize arg_max(ingestion_time, *) by file_name// remove duplicates
| extend image=strcat("![image](data&colon;image/png;base64,", img_original_base64, ")" )
| project file_name, image 
| order by file_name desc 
| invoke table_to_markdown()

This is an example how your data can be visualized using the markdown visualization:

Display images in Power BI

Power BI allows the integration of images from a database, a process that is well-documented in a Guy in a Cube YouTube video, which is referenced at the end of this article. By default, Power BI supports image URLs, but what if you want to display images stored as strings? Given Power BI's limitation of a 32k string size, a creative workaround is necessary. This involves splitting the strings and then reconstructing them using DAX logic, a technique thoroughly explained in the aforementioned Guy in a Cube video.

To handle large image strings that exceed Power BI's capacity, a split string function in Kusto can be employed. This function divides the image string representation into multiple rows, which is essential for visualization tools that have string size restrictions. Here's how the function looks

//helper function that splits large image string representation into several rows
//this is needed for visualization tools with limitation on string sizes
.create-or-alter function with (folder = "Gold layer", docstring="split image string representation to several rows if PowerBI string size limitation is hit", skipvalidation = "true") image_report ()
{
let max_length=32766; //maximum PowerBI string length
image
| project file_name, img_original_base64
| extend ingestion_time=ingestion_time() 
| summarize arg_max(ingestion_time, *) by file_name // remove duplicates
| extend parts = range(0, strlen(img_original_base64) - 1, max_length)
| mv-expand parts // rows needed for each substring (1 if length < max_length)
| extend img_original_base64_part = substring(img_original_base64, toint(parts), max_length), order=toint(parts)/max_length
| project file_name, img_original_base64_part, order
}

Following the split, DAX logic is used to concatenate the substrings back into the final image:

image = IF (HASONEVALUE(Images[file_name]), "data&colon;image/png;base64, " &CONCATENATEX(Images, 'Images'[img_original_base64_part],,Images[order],ASC) )

This approach ensures that even with Power BI's string size limitations, images can be effectively displayed by leveraging Kusto's split string function and DAX's concatenation capabilities.

Conclusion

The integration of images into Kusto and their visualization through Power BI or Azure Data Explorer Dashboards offers a unique approach to managing and displaying non-structured data. While Kusto is primarily designed for structured and semi-structured data, it can accommodate images through base64 encoding, albeit with some limitations due to the absence of binary data types. This method is particularly useful for dashboards and reports that require secure access to images without relying on external storage solutions.

The process involves encoding images into a base64 string, ingesting them into Kusto, and then utilizing visualization tools like Power BI to display the images. This approach ensures that all data, including images, can be securely accessed and managed in one centralized location, simplifying both access and security protocols.

However, it's crucial to recognize that Kusto is not optimized for storing large-scale images, and this method should be reserved for scenarios where the benefits outweigh the limitations. By following the guidelines and techniques outlined in this blog post, users can effectively store and visualize images within Kusto, enhancing their data analysis and reporting capabilities in a secure and efficient manner.

References

Ad-Hoc Visual Data Exploration Feature

Michal_Bar — Mon, 12 Aug 2024 18:25:16 GMT

Ad-Hoc Data Exploration Feature

We are excited to introduce the new Data Exploration feature, designed to enhance your ability to delve deeper into the data presented on any Dashboard.

If the information you're seeking isn't readily available on the dashboard, this feature allows you to extend your exploration beyond the data displayed in the tiles, potentially uncovering new insights.

Directly from a dashboard, you can refine your exploration using a user-friendly, form-like interface. This intuitive and dynamic experience is tailored for insights explorers seeking insights based on high volumes of data in near real time.

You can add filters, create aggregations, and switch visualization types without writing queries to easily uncover insights.

With this new feature, you are no longer bound by the limitations of pre-defined dashboards, nor are you required to master KQL (Kusto Query Language). As independent explorers, you have the freedom for ad-hoc exploration, leveraging existing tiles to kickstart your journey.

Learn more about this feature Explore data in dashboard tiles (preview) - Azure Data Explorer | Microsoft Learn

Azure Data Explorer Web UI team is looking forward to your feedback in KustoWebExpFeedback@service.microsoft.com

You’re also welcome to add more ideas and vote for them here - https://aka.ms/adx.ideas

Deprecation of Virtual Network Injection for Azure Data Explorer

cosh23 — Mon, 22 Jul 2024 18:36:11 GMT

We are announcing the deprecation of the feature of Virtual Network Injection for Azure Data Explorer. This feature allows customers to inject their Azure Data Explorer cluster into their own virtual network and control the inbound and outbound network traffic. However, this feature has limitations and challenges, such as:

Customers face a lot of maintenance work, because of things like updating firewall lists of FQDNs or using public IP addresses in a restricted and secured environment.
Customers are responsible for ensuring that the intra-cluster communication is working.
It requires a dedicated subnet for each cluster, which can lead to subnet exhaustion and increased management overhead.
It does not support cross-region or cross-subscription scenarios, which can limit the scalability and flexibility of the data platform.

Required actions

As a result, we are deprecating this feature and recommending customers to move as soon as possible to a private endpoint based network security architecture. A private endpoint is a network interface that connects you privately and securely to a service powered by Azure Private Link. Private endpoint uses a private IP address from your virtual network, effectively bringing the service into your virtual network. With private endpoints, you can:

Connect securely to Azure Data Explorer from your virtual network or from on-premises networks via VPN or ExpressRoute.
Access Azure Data Explorer from different regions or subscriptions without any public internet exposure.
Use all the Azure Data Explorer features without any limitations or trade-offs.
Reduce the network complexity and management overhead by using a single subnet for multiple clusters and services.

To help you with the migration, we have created a migration process (close to zero downtime).

We understand that this migration may require effort and coordination from your side, and we are here to support you along the way. We have scheduled office hours to answer your questions and provide guidance on the migration process. You can register for the office hours using this form. You can also reach out to us via email at ADXVnetDeprecation@microsoft.com.

Timelines

Please note the following important dates and actions regarding the migration:

Effective immediately, no new customers will be able to create virtual network injected clusters. Existing customers can continue to use their clusters until the migration deadline.
Starting from February 1, 2025, all running virtual network injected clusters will be stopped. Customers who have not migrated by then will not be able to start them until they complete the migration process.
To avoid any disruption, we strongly recommend that you migrate your clusters as soon as possible. You can follow the migration steps outlined in this document. If you encounter any issues or need assistance, please contact us at ADXVnetMigration@microsoft.com or join our office hours.

We appreciate your understanding and cooperation as we deprecate the Virtual Network Injection feature and move to a more secure, scalable, and feature-rich network security architecture for Azure Data Explorer.