Use Azure Data Explorer data in Power BI
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.
- A Microsoft account or an Azure Active Directory user identity. An Azure subscription isn't required.
- An Azure Data Explorer cluster and database. You can create a free cluster or create a full cluster. To decide which is best for you, check the feature comparison.
- Power BI Desktop.
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 data set is small and you don't need near real-time data.
- You perform aggregation in Kusto.
Use DirectQuery mode when:
- Your data set 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.
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.