Use Azure Data Explorer data in Power BI

  • Article

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

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:

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.

  1. Launch Power BI Desktop.

  2. On the Home tab, select Get Data > More.

    Screenshot of the Home tab in Power BI Desktop, showing the drop-down menu of the Home tab entry titled Get data with the More option highlighted.

  3. Search for Azure Data Explorer, select Azure Data Explorer (Kusto), and then select Connect.

    Screenshot of the Get Data window, showing Azure Data Explorer in the search bar with the connect option highlighted.

  4. 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.chinacloudapi.cn. https://help.chinaeast2.kusto.chinacloudapi.cn
    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

  5. 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.

    Screenshot of Navigator screen, showing that the StormEvents table is selected. The Load button is highlighted.

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

Next step

Create reports and dashboards in Power BI