Visualize Data from Azure Data Explorer using a SQL Query in Power BI
Jul 14, 2024
Visualize data from Azure Data Explorer using a SQL query in Power BI
In today’s blog post, we’ll guide you through the process of connecting Power BI to an Azure Data using SQL query of data sources. Azure Data Explorer provides three options for connecting to data in Power BI: use the built-in connector, import a query from Azure Data Explorer, or use an SQL query. This blog shows you how to use a SQL query to get data and visualize it in a Power BI report.
Retrieve
data from Azure Data Explorer
First, connect to the Azure Data Explorer help
cluster, then import a subset of data from the Storm Events table. This sample
dataset includes weather-related data from the National Centers for
Environmental Information.
Although Azure Data Explorer typically uses its
native query language, it also supports SQL queries, which you will use here.
Azure Data Explorer will convert the SQL query into a
native query for you. In Power BI Desktop, on the Home tab,
select Get Data then More.
Look up Azure SQL Database, choose Azure SQL
Database, and then establish a connection.
2. Find Azure SQL Database, choose Azure SQL Database, and then click Connect.
3. On the SQL Server database screen,
fill out the form with the following information.
Ref: Microsoft
|
Setting |
Value |
Field description |
|
Server |
help.kusto.windows.net |
The URL for the help cluster (without https://). For other
clusters, the URL is in the form |
|
Database |
Samples |
The sample database that is hosted on the cluster you're connecting
to. |
|
Data connectivity mode |
Import |
Determines whether Power BI imports the data or connects directly to
the data source. You can use either option with this connector. |
|
Command timeout |
Leave blank |
How long the query runs before it throws a timeout error. |
|
SQL statement |
Copy the query below this table |
The SQL statement that Azure Data Explorer translates into a native
query. |
|
Other options |
Leave as default values |
Options don't apply to Azure Data Explorer clusters. |
SELECT TOP 1000 *
FROM StormEvents
ORDER BY DamageCrops DESC
4. If you're not connected to the help cluster yet,
log in. Use a Microsoft account and then
click Connect.
Ref: Microsoft
5. On the help.kusto.windows.net: Samples page, choose Load.
Ref: Microsoft
The table opens in the main Power BI window, in report view, where you can create reports based on the sample data.
Top of
Form
Bottom
of Form
Display
the Data in a Report
Once
data is available in Power BI Desktop, you can generate reports using this
data. Start by crafting a basic report featuring a column chart depicting crop
damage across different states.
1.On the
left side of the main Power BI window, select the report view.
2.In
the VISUALIZATIONS pane, select the clustered column chart.
A blank
chart is added to the canvas.
3.In the FIELDS list, select DamageCrops and State.
You now have a chart that shows the damage to crops
for the top 1000 rows in the table.
4.Save the report
Conclusion:
By
leveraging the capabilities of both platforms, you can seamlessly integrate and
transform vast amounts of data into actionable insights. This process not only
enhances your analytical capabilities but also streamlines your workflow,
enabling more informed decision-making.
For more detailed guidance and in-depth training, visit our training here.
Share:
