Use Azure Data Explorer data in Power BI

Article
10/13/2023

In this article, you'll learn about the different ways in which you can connect Azure Data Explorer as a data source to Power BI. Once connected, you can proceed to build Power BI reports.

Prerequisites

A Microsoft account or a Microsoft Entra user identity. An Azure subscription isn't required.
An Azure Data Explorer cluster and database. Create a cluster and database.
Power BI Desktop.

Connectivity modes

Power BI supports Import and DirectQuery connectivity modes. When building Power BI reports or dashboards, choose your connectivity mode depending on your scenario, scale, and performance requirements. Using Import mode copies your data to Power BI. In contrast, using DirectQuery mode queries your data directly from your Azure Data Explorer cluster.

Use Import mode when:

Your dataset is small and you don't need near real-time data.
You perform aggregation in Kusto.

Use DirectQuery mode when:

Your dataset is large or you need near real-time data.

For more information on connectivity modes, see Import and Direct Query connectivity modes.

Use data in Power BI

You can connect Azure Data Explorer as a data source to Power BI in the following ways:

Starting in Azure Data Explorer web UI and then pasting the data in Power BI Desktop.
Starting directly in Power BI Desktop and then adding the Azure Data Explorer connector.

In the following steps, we'll be using the StormEvents table from our help cluster to demonstrate how to use Azure Data explorer data in Power BI.

Web UI
Connector

In the Azure Data Explorer web UI, on the left menu, select Query, and then select the database with your data.
Create a query and select it. For example, the following query from the Samples > StormEvents table, returns storm events that caused the most crop damage:
```
StormEvents
| sort by DamageCrops desc
| take 1000
```
From the Export menu, select Query to Power BI.
Launch Power BI Desktop.
On the Home tab, select Transform data.
Paste the query in the Navigator pane.
Optionally, you can change the connectivity mode from DirectQuery to Import. In the Query Settings window, under Applied steps, select the settings cog. For more information, see Connectivity modes.
On the Home tab, select Close & Apply.

Launch Power BI Desktop.
On the Home tab, select Get Data > More.
Search for Azure Data Explorer, select Azure Data Explorer (Kusto), and then select Connect.

In the window that appears, fill out the form with the following information.

Screenshot of the Azure Data Explorer(Kusto) connection window showing the help cluster URL, with the DirectQuery option selected.

Setting	Field description	Sample value
Cluster	The URL for the help cluster. For other clusters, the URL is in the form https://<ClusterName>.<Region>.kusto.windows.net.	https://help.kusto.windows.net
Database	A database that is hosted on the cluster you're connecting to. You can optionally select a database in a later step.	Leave blank
Table name	The name of a table in the database, or a query like `StormEvents \| take 1000`. You can optionally select a table name in a later step.	Leave blank
Advanced options	Optionally, you can select options for your queries, such as result set size.	Leave blank
Data connectivity mode	Determines whether Power BI imports the data or connects directly to the data source. You can use either option with this connector. For more information, see Connectivity modes.	DirectQuery

Advanced options

Setting	Field description	Sample value
Limit query result record number	The maximum number of records to return in the result	`1000000`
Limit query result data size	The maximum data size in bytes to return in the result	`100000000`
Disable result set truncation	Enable/disable result truncation by using the notruncation request option	`true`
Additional set statements	Sets request properties for the duration of the query. Request properties control how a query executes and returns results.	`set query_datascope=hotcache`

On the Navigator screen, expand the Samples database, select StormEvents, and then select Load Data.

Optionally, if you want to shape your data first, select Transform data to launch Power Query Editor. For more information, see Shape data.

Tips for using the Azure Data Explorer connector for Power BI to query data.

Next step

Create reports and dashboards in Power BI

Share via