KQL: Microsoft Fabric’s Real-Time analytics language

Learn to run basic KQL commands to analyze your real-time data.

An Eventhouse is a solution designed to manage and analyze large volumes of real-time data. It supports real-time ingestion, processing, and analytics, making it ideal for scenarios where timely insights are critical. Eventhouse excels in handling structured, semi-structured, and unstructured data, focusing on time-based streaming events. Key features include:

• Real-Time Analytics: Efficient processing of data streams for near real-time insights.
• Scalable Infrastructure: Manages growing data volumes while optimizing performance and costs.
• Flexible Data Ingestion: Supports multiple sources (e.g., Eventstream, SDKs, Kafka, Logstash) and data formats, with automatic indexing and partitioning by ingestion time.
• Unified Management: Provides centralized monitoring and resource-sharing for multiple databases, ensuring optimal use and cost-efficiency.

While real-time data is ideal for maximizing the capabilities of Eventhouse, this blog will focus on loading data directly into Eventhouse to help you learn the basics of KQL (Kusto Query Language).

But you can watch this video on setting up real-time data using Raspberry Pi simulators.

Microsoft Fabric is essential for the activities in this blog, it could be a trial or purchased Fabric capacity. A workspace with assigned fabric capacity is also required.

Open the Workspace from the option on the Left Pane. In My case the Workspace is 01-GA-Fabric

Click on New Item on the Left Top-> New Item Menu will open on the right. Scroll down and Click on Eventhouse in “Track data”

On the “New Eventhouse” popup, give a name and click on create

The creation of a new Eventhouse is currently in progress.

The creation of the first Eventhouse is followed by the message below.

A new Eventhouse has been created with a KQL database.

The following entities are created along with the Eventhouse: a KQL Database and a KQL Queryset.

It’s time to load some data. Click on the ellipsis icon (three dots), select ‘Get Data,’ and then click on ‘Local File.’

In the ‘Get Data’ popup, click on ‘New Table’ in the configuration step.

Enter a table name, and then browse for the file to load the data.

Use the file dialog to select the file, and the upload process will begin. You can download the file from GitHub or Dropbox.

After the file is loaded, click on ‘Next’ to proceed.

On the ‘Inspect’ screen, select ‘First row is column header’ and then click ‘Finish’.

The table and its mappings have been successfully created.

The data has been loaded. Click on ‘Close’ to finish.

You will reach the home screen of the KQL Database.

Click on queryset

Exploring Data in Eventhouse with KQL

To get started with Eventhouse and Kusto Query Language (KQL), here’s a quick and straightforward command to help you explore your dataset. In this example, we use a table named Sales within your Eventhouse database.

Operator: getschema

Sales
| getschema

Explanation:

1. Sales: The name of your table in the Eventhouse database. Replace Sales with your specific table name.
2. getschema: Retrieves the schema of a given table, i.e. the Sales table, showing all columns with their names, data types, and other metadata. This is an excellent starting point for understanding the structure of your data.

Operator: take

Sales
| take 100

Explanation:

1. Sales: This refers to the name of your table.
2. |** (Pipe operator)**: The pipe operator is used to pass the output of one operation as input to the next operation.
3. take 100: This retrieves the first 100 rows from the given table. It’s especially useful for previewing a subset of your data without processing the entire table.

Operator: project

To retrieve specific columns from a table in KQL, you can use the project. Here's the command to select specific columns, such as order_id, order_date, and sales, from the Sales table:

Sales
| project order_id, order_date, sales

Explanation:

1. Sales: The table you are querying.
2. | (Pipe operator): Passes the output of the given table to the next operation.
3. project: Select only the specified columns from the table. Replace order_id, order_date, and sales with the columns you want to include.
4. But run this with a limit if the data is very large

Sales
| project order_id, order_date, sales
| take 100

Operator: count

The count operator in KQL is used to return the total number of rows in a table. Here's the command and its explanation:

Sales
| count

Explanation:

1. Sales: Specifies the table you are querying.
2. | count: Returns the total number of rows in the given table as a single numeric value.

Operator: Where

The where operator in KQL is used to filter rows based on a condition. Here's the command and its explanation:

Sales
| where order_date > datetime(2017-01-01)

Explanation:

1. Sales: Specifies the table you are querying.
2. | where: Filters the rows in the table based on a specified condition.
3. order_date > datetime(2017-01-01): Filters rows where the order_date is later than January 1, 2017. Replace the date with your desired value.

Operator: And

To apply multiple filters in KQL, you can combine conditions using logical operators like and. Here's your command along with an explanation:

Sales
| where region == "East" and category == "Watches" and sales > 100

Explanation:

1. Sales: Specifies the table you are querying.
2. | where: Filters rows in the table based on the specified conditions.
3. region == "East": Includes only rows where the region is equal to "East".
4. and category == "Watches": Adds filter for rows where the category column is "Watches".
5. and sales > 100: Further filters rows to include only those where the sales column is greater than 100.

Operator: OR

Use the or operator in KQL to include rows where a condition matches multiple values. Here’s how you can modify the query to include both "East" and "West" for the region column

Sales
| where (region == "East" or region == "West") and category == "Watches" and sales > 100

Explanation:

1. region == "East" or region == "West": Filters rows where the region is either "East" or "West".
2. and category == "Watches": Adds filter for rows where the category is "Watches".
3. and sales > 100: Further filters rows to include only those where the sales column is greater than 100.

Compare Columns

The KQL query filters the Sales table to retrieve only the records where the delivery_date is later than the requested_date, indicating delayed deliveries.

Sales
| where delivery_date > requested_date
| project order_id, order_no, requested_date, delivery_date, customer_id
| take 100

Explanation:

1. Sales: Specifies the table being queried.

2. | where delivery_date > requested_date: Filters rows where the delivery_date is later than the requested_date.

3. | project order_id, order_no, requested_date, delivery_date, customer_id: Selects specific columns (order_id, order_no, requested_date, delivery_date, and customer_id) for display.

4. | take 100: Limits the output to the first 100 rows of the filtered and projected data.

Operator: Summarize, by

Summarizing sales and quantity metrics by category.

Sales
| summarize TotalSales = sum(sales), TotalQty = sum(qty) by category

Explanation:

1. Sales: Specifies the table you are querying.
2. | summarize: Aggregates data based on the specified calculations.

• TotalSales = sum(sales): Calculates the total sales for each category.
• TotalQty = sum(qty): Calculates the total quantity for each category.

3. by category: Groups the results by the category column.

Operator: Extend

This command computes the total discount and total gross margin grouped by brand. It also introduces a calculated column, GrossMargin, to determine profitability at the row level.

// Calculate Discounts and Gross Margins
Sales
| extend GrossMargin = gross_sales - (qty * unit_cost)
| summarize TotalDiscount = sum(discount), TotalGrossMargin = sum(GrossMargin) by brand

Explanation:

1. Sales: Specifies the table being queried.
2. | extend GrossMargin = gross_sales - (qty * unit_cost):

• Adds a new calculated column, GrossMargin, by subtracting the total cost (qty * unit_cost) from gross_sales.

3. | summarize: Aggregates the data with the specified calculations.

• TotalDiscount = sum(discount): Calculates the total discount for each brand.
• TotalGrossMargin = sum(GrossMargin): Calculates the total gross margin for each brand.

4. by brand: Groups the results by the brand column.

Operator: Top, by

This command calculates the top 5 items with the highest total sales.

//Top N Items by Sales
Sales
| summarize TotalSales = sum(sales) by item_id, item_no
| top 5 by TotalSales desc

Explanation:

1. Sales: Specifies the table being queried.
2. | summarize TotalSales = sum(sales) by item_id, item_no:

• Aggregates the total sales (sum(sales)) grouped by item_id and item_no.

3. | top 5 by TotalSales desc:

• Retrieves the top 5 rows with the highest TotalSales, sorted in descending order.

Calculating Percentage Contribution of Sales by Category

This KQL query calculates the percentage contribution of sales for each category relative to the total sales in the Sales table

// Percentage Contribution
let TotalSales = toscalar(Sales | summarize sum(sales));
Sales
| summarize CategorySales = sum(sales) by category
| extend PercentageContribution = round(100.0 * CategorySales / TotalSales, 2)

Explanation:

1. let TotalSales = toscalar(Sales | summarize sum(sales));: This calculates the total sales across the entire Sales table and stores the result in the variable TotalSales. The toscalar function ensures that the result is treated as a single scalar value.

2. | summarize CategorySales = sum(sales) by category: Aggregates the total sales (sum(sales)) for each category.

3. | extend PercentageContribution = round(100.0 * CategorySales / TotalSales, 2): Calculates the percentage contribution of each category's sales to the total sales and rounds the result to two decimal places.

Trending Analysis of Daily Sales

This query performs a trending analysis of daily sales over the last 3,000 days.

// Trending Analysis
Sales
| where order_date > ago(3000d)
| summarize DailySales = sum(sales) by bin(order_date, 1d)
| order by order_date asc

Explanation:

1. Sales:

• Refers to the table being queried, which contains sales data.

2. | where order_date > ago(3000d):

• Filters the data to include only records where the order_date is within the last 3,000 days.
• The ago() function calculates a time range relative to the current time.

3. | summarize DailySales = sum(sales) by bin(order_date, 1d):

• Grouping the sales data into daily intervals using the bin() function, which rounds the order_date into 1-day bins.
• Aggregates the total sales (sum(sales)) for each day, resulting in daily sales totals.

4. | order by order_date asc:

• Sorts the results in ascending order of order_date to present the trend over time.

Identify High-Volume Customers Based on Total Quantity in KQL

This KQL query aggregates the Sales data by calculating the total quantity (TotalQty) for each customer_id using the summarize function. It then filters the results to include only customers whose TotalQty exceeds 100, effectively identifying high-volume customers based on the total quantity of items purchased.

// filter on sum 
Sales
| summarize TotalQty = sum(qty) by customer_id
| where TotalQty > 100

Explanation:

1. Sales:

• Refers to the table being queried, which contains sales data.

2. | summarize TotalQty = sum(qty) by customer_id:

• Grouping the data by customer_id and calculates the total quantity (sum(qty)) for each customer.
• Creates a summarized table with customer_id and TotalQty.

3. | where TotalQty > 100:

• Filters the summarized results to include only those customers whose total quantity (TotalQty) exceeds 100.

You can also try these.

// Region wise Analysis
Sales
| summarize TotalSales = sum(sales) by region, order_type
| order by region asc, order_type asc

// Trend with Time Series
Sales
| summarize WeeklySales = sum(sales) by bin(order_date, 7d), state
| order by order_date asc

// By Sub Category
Sales
| summarize TotalSales = sum(sales), AvgDiscount = avg(discount_percent) by sub_category, sub_sub_category
| order by TotalSales desc

// Filter by Payment Method
Sales
| where sales > 500
| summarize TotalSales = sum(sales) by payment_method
| order by TotalSales desc



// Calculate Delivery Time
Sales
| extend DeliveryTime = datetime_diff("day", delivery_date, requested_date)
| summarize AvgDeliveryTime = avg(DeliveryTime) by region

In this blog, we explored the fundamentals of Kusto Query Language (KQL) within Microsoft Fabric, starting from setting up an Eventhouse to running practical queries for schema retrieval, filtering, aggregation, and trend analysis. These commands provide a strong foundation for real-time analytics, helping you unlock insights from your data efficiently.

As you dive deeper into KQL, don’t hesitate to experiment with more advanced commands and scenarios tailored to your business needs. If you have any questions or want to share how you’re using KQL, feel free to comment below or connect with me on social media. Stay tuned for more practical guides on Microsoft Fabric and real-time analytics!

My Medium blogs can be found here if you are interested

Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvements, challenges, and topics so that they can be explored in detail.

If you want to learn Power BI from the ground up — covering everything from basic data modeling to advanced DAX and real-world analytics — be sure to explore my 20-hour Masterclass, packed with more than 150 essential topics to elevate your data analytics and business intelligence skills.

In addition, there are over 750 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.

Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL