在 Power BI 中使用 SQL 查询直观显示 Azure 数据资源管理器中的数据Visualize data from Azure Data Explorer using a SQL query in Power BI

Azure 数据资源管理器是一项快速且高度可缩放的数据探索服务,适用于日志和遥测数据。Azure Data Explorer is a fast and highly scalable data exploration service for log and telemetry data. Power BI 是一种业务分析解决方案,可以用来可视化数据,并在组织内共享结果。Power BI is a business analytics solution that lets you visualize your data and share the results across your organization.

Azure 数据资源管理器提供三个可以在 Power BI 中连接到数据的选项:使用内置连接器、从 Azure 数据资源管理器导入查询,或者使用 SQL 查询。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 a SQL query. 本文介绍如何使用 SQL 查询获取数据并在 Power BI 报表中直观显示这些数据。This article shows you how to use a SQL query to get data and visualize it in a Power BI report.

如果没有 Azure 订阅,可在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

先决条件Prerequisites

要完成本文,需要以下各项:You need the following to complete this article:

从 Azure 数据资源管理器获取数据Get data from Azure Data Explorer

首先连接到 Azure 数据资源管理器帮助群集,然后从 StormEvents 表引入一部分数据。First, you connect to the Azure Data Explorer help cluster, then you bring in a subset of the data from the StormEvents table. StormEvents 示例数据集包含国家环境信息中心中与天气相关的数据。The StormEvents sample data set contains weather-related data from the National Centers for Environmental Information.

通常对 Azure 数据资源管理器使用本机查询语言,但它也支持这里将要用到的 SQL 查询。You typically use the native query language with Azure Data Explorer, but it also supports SQL queries, which you'll use here. Azure 数据资源管理器将 SQL 查询转换成本机查询。Azure Data Explorer translates the SQL query into a native query for you.

  1. 在 Power BI Desktop 的“主页”选项卡上选择“获取数据”,然后选择“更多” 。In Power BI Desktop, on the Home tab, select Get Data then More.

    获取数据

  2. 搜索“Azure SQL 数据库”, 选择“Azure SQL 数据库”,然后选择“连接”。 Search for Azure SQL Database, select Azure SQL Database then Connect.

    搜索和获取数据

  3. 在“SQL Server 数据库”屏幕的窗体中填充以下信息。 On the SQL Server database screen, fill out the form with the following information.

    数据库、表、查询选项

    设置Setting Value 字段说明Field description
    服务器Server help.kusto.chinacloudapi.cnhelp.kusto.chinacloudapi.cn 帮助群集的 URL(没有 https:// )。The URL for the help cluster (without https://). 其他群集的 URL 采用 <ClusterName>.<区域>.kusto.chinacloudapi.cn 格式。For other clusters, the URL is in the form <ClusterName>.<Region>.kusto.chinacloudapi.cn.
    数据库Database 示例Samples 托管在要连接到的群集上的示例数据库。The sample database that is hosted on the cluster you're connecting to.
    数据连接模式Data connectivity mode 导入Import 确定 Power BI 是导入数据还是直接连接到数据源。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 语句SQL statement 复制此表下面的查询Copy the query below this table 一种 SQL 语句,通过 Azure 数据资源管理器转换成本机查询。The SQL statement that Azure Data Explorer translates into a native query.
    其他选项Other options 保留为默认值Leave as default values 选项不应用到 Azure 数据资源管理器群集。Options don't apply to Azure Data Explorer clusters.
    SELECT TOP 1000 *
    FROM StormEvents
    ORDER BY DamageCrops DESC
    
  4. 如果还没有连接到帮助群集,请登录。If you don't already have a connection to the help cluster, sign in. 使用 Microsoft 帐户登录,然后选择“连接”。 Sign in with a Microsoft account, then select Connect.

    登录

  5. 在“help.kusto.chinacloudapi.cn: 示例” 屏幕上,选择“加载”。 On the help.kusto.chinacloudapi.cn: Samples screen, select Load.

    加载数据

    此表在 Power BI 主窗口的报表视图中打开,方便你在其中根据示例数据来创建报表。The table opens in the main Power BI window, in report view, where you can create reports based on the sample data.

在报表中将数据可视化Visualize data in a report

Power BI Desktop 中有了数据以后,即可创建基于该数据的报表。Now that you have data in Power BI Desktop, you can create reports based on that data. 将创建一个简单的包含柱状图的报表,以便按州显示作物损坏情况。You'll create a simple report with a column chart that shows crop damage by state.

  1. 在 Power BI 主窗口左侧,选择报表视图。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. 在“字段” 列表中,选择“DamageCrops” 和“州” 。In the FIELDS list, select DamageCrops and State.

    选择字段

    现在已有一张图表,显示表中最前面 1000 行对应的作物损坏情况。You now have a chart that shows the damage to crops for the top 1000 rows in the table.

    作物损坏情况(按州)

  4. 保存报表。Save the report.

清理资源Clean up resources

如果不再需要为本文创建的报表,请删除 Power BI Desktop (.pbix) 文件。If you no longer need the report you created for this article, delete the Power BI Desktop (.pbix) file.

后续步骤Next steps

使用 Power BI 的 Azure 数据资源管理器连接器直观显示数据Visualize data using the Azure Data Explorer connector for Power BI