教程:在 Power BI 中可视化 Azure 数据资源管理器中的数据Tutorial: Visualize data from Azure Data Explorer 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 数据资源管理器中视觉对象的呈现方式。In this tutorial, you first learn how to render visuals in Azure Data Explorer. 然后使用 Power BI 连接到 Azure 数据资源管理器,生成基于示例数据的报表,并将报表发布到 Power BI 服务。You then connect to Azure Data Explorer with Power BI, build a report based on sample data, and publish the report to the Power BI service.

如果没有 Azure 订阅,请在开始前创建一个试用订阅If you don't have an Azure subscription, create a Trial Subscription before you begin. 如果没有注册 Power BI Pro,请在开始之前注册免费试用版If you're not signed up for Power BI Pro, sign up for a free trial before you begin.

本教程介绍如何执行下列操作:In this tutorial, you learn how to:

  • 在 Azure 数据资源管理器中呈现视觉对象Render visuals in Azure Data Explorer
  • 连接到 Power BI Desktop 中的 Azure 数据资源管理器Connect to Azure Data Explorer in Power BI Desktop
  • 使用 Power BI Desktop 中的数据Work with data in Power BI Desktop
  • 使用视觉对象创建报表Create a report with visuals
  • 发布和共享报表Publish and share the report


除了 Azure 和 Power BI 订阅,需要以下条件才能完成本教程:In addition to Azure and Power BI subscriptions, you need the following to complete this tutorial:

在 Azure 数据资源管理器中呈现视觉对象Render visuals in Azure Data Explorer

在跳转到 Power BI 之前,让我们看看如何在 Azure 数据资源管理器中呈现视觉对象。Before jumping into Power BI, let's look at how to render visuals in Azure Data Explorer. 这非常适用于一些快速分析。This is great for some quick analysis.

  1. 登录到 https://dataexplorer.azure.cnSign in to https://dataexplorer.azure.cn.

  2. 在左侧窗格中,选择包含 StormEvents 示例数据的测试数据库。In the left pane, select the test database that contains the StormEvents sample data.

  3. 将以下查询粘贴到右侧窗口,然后选择“运行”。Paste the following query into the right window, and select Run.

    | summarize event_count=count() by State
    | where event_count > 1800
    | project State, event_count
    | sort by event_count
    | render columnchart

    此查询会根据状态统计天气事件。This query counts weather events by state. 然后,它将呈现为具有超过 1800 个天气事件的所有状态柱形图。It then renders a column chart for all states that have more than 1800 weather events.


  4. 将以下查询粘贴到右侧窗口,然后选择“运行”。Paste the following query into the right window, and select Run.

    | where State == "WASHINGTON" and StartTime >= datetime(2007-07-01) and StartTime <= datetime(2007-07-31)
    | summarize StormCount = count() by EventType
    | render piechart

    此查询统计了华盛顿州 7 月份的天气事件。This query counts weather events by type for the month of July in the state of Washington. 然后,它将呈现显示每个事件类型百分比的饼图。It then renders a pie chart showing the percentage of each event type.


现在可以看下 Power BI,但在 Azure 数据资源管理器中,可以通过视觉对象执行更多操作。It's now time to look at Power BI, but there's a lot more you can do with visuals in Azure Data Explorer.

连接到 Azure 数据资源管理器Connect to Azure Data Explorer

现在,连接到 Power BI Desktop 中的 Azure 数据资源管理器。Now you connect to Azure Data Explorer in Power BI Desktop.

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


  2. 搜索“Azure 数据资源管理器”,选择“Azure 数据资源管理器”,然后选择“连接” 。Search for Azure Data Explorer, select Azure Data Explorer, then Connect.


  3. 在“预览连接器”屏幕中,选择“继续”。On the Preview connector screen, select Continue.

  4. 在下一屏幕上,输入测试群集和数据库的名称。On the next screen, enter the name of your test cluster and database. 群集应采用 https://<ClusterName>.<Region>.kusto.chinacloudapi.cn 格式。Cluster should be in the form https://<ClusterName>.<Region>.kusto.chinacloudapi.cn. 输入“StormEvents”作为表的名称。Enter StormEvents for the name of the table. 将保留所有其他选项的默认值,然后选择“确定”。Leave all other options with default values, and select OK.


  5. 在数据预览屏幕上,选择“编辑”。On the data preview screen, select Edit.

    表在 Power Query 编辑器中打开,可以在其中编辑行和列,然后导入数据。The table opens in Power Query Editor, where you can edit rows and columns before importing the data.

使用 Power BI Desktop 中的数据Work with data in Power BI Desktop

现在,你已连接到 Azure 数据资源管理器,请在 Power Query 编辑器中编辑数据。Now that you have a connection to Azure Data Explorer, you edit the data in Power Query Editor. 删除“BeginLat”列中具有 NULL 值的行并完全删除“StormSummary”JSON 列。You drop rows with null values in the BeginLat column and drop the StormSummary JSON column entirely. 这些都是简单的操作,但导入数据时,还可以执行复杂转换。These are simple operations, but you can also perform complex transformations when importing data.

  1. 选择“BeginLat”列的箭头,清除“NULL”复选框,然后选择“确定”。Select the arrow for the BeginLat column, clear the null check box, then select OK.


  2. 右键单击“StormSummary”列标题,然后选择“删除”。 Right-click the StormSummary column header, then select Remove.


  3. 在“查询设置”窗格中,将名称从“Query1”更改为“StormEvents”。In the QUERY SETTINGS pane, change the name from Query1 to StormEvents.


  4. 在功能区的“主页”选项卡上,选择“关闭并应用”。On the Home tab of the ribbon, select Close and apply.


    Power Query 应用所做的更改,然后将示例数据导入到“数据模型”。Power Query applies your changes, then imports the sample data into a data model. 接下来的几个步骤演示如何扩充该模型。The next few steps show you how to enrich that model. 同样,这只是一个展示了可能的操作的简单示例。Again, this is just a simple example to give an idea of what's possible.

  5. 在主窗口左侧,选择数据视图。On the left side of the main window, select the data view.


  6. 在功能区的“建模”选项卡上,选择“新列”。On the Modeling tab of the ribbon, select New column.


  7. 在公式栏中输入下面的数据分析表达式 (DAX) 公式,然后按 Enter。Enter the following Data Analysis Expressions (DAX) formula into the formula bar, then press Enter.

    DurationHours = DATEDIFF(StormEvents[StartTime], StormEvents[EndTime], hour)


    此公式创建列“DurationHours”,计算每个天气事件持续的小时数。This formula creates the column DurationHours that calculates how many hours each weather event lasted. 在下节的视觉对象中使用此列。You use this column in a visual in the next section.

  8. 滚动到表的右侧以查看列。Scroll to the right side of the table to see the column.

使用视觉对象创建报表Create a report with visuals

已导入数据并改进了数据模型,现在可以使用视觉对象生成报表。Now that the data is imported and you've improved the data model, it's time to build a report with visuals. 添加基于事件持续时间的柱形图和显示作物损坏的地图。You add a column chart based on event duration and a map that shows crop damage.

  1. 在窗口左侧,选择报表视图。On the left side of the window, select the report view.


  2. 在“可视化”窗格中,选择“簇状柱形图”。In the VISUALIZATIONS pane, select the clustered column chart.


    会向画布添加一个空白图。A blank chart is added to the canvas.


  3. 在“字段”列表中,选择“DurationHours”和“状态”。In the FIELDS list, select DurationHours and State.


    现在已有一张图表显示一年时间里按状态统计的天气事件的总小时数。You now have a chart that shows the total hours of weather events by state over the course of a year.


  4. 单击画布上柱形图之外任意位置。Click anywhere on the canvas outside the column chart.

  5. 在“可视化”窗格中,选择“地图”。In the VISUALIZATIONS pane, select the map.


  6. 在“字段”列表中,选择“CropDamage”和“状态”。In the FIELDS list, select CropDamage and State. 调整地图大小以便可以清楚地看到美国各州。Resize the map so you can see the US states clearly.


    气泡大小表示作物损坏的美元价值。The size of the bubbles represents the dollar value of crop damage. 将鼠标悬停在气泡上方查看详细信息。Mouse over the bubbles to see details.

  7. 移动和调整视觉对象的大小,使报表如下图所示。Move and resize the visuals so you have a report that looks like the following image.


  8. 使用名称“storm-events.pbix”保存报表。Save the report with the name storm-events.pbix.

发布和共享报表Publish and share the report

至此,在 Power BI 中完成的工作已为本地工作,使用的是 Power BI Desktop。Until this point, the work you've done in Power BI has all been local, using Power BI Desktop. 现在将报表发布到 Power BI 服务,可在其中与他人共享。Now you publish the report to the Power BI service where you can share it with others.

  1. 在 Power BI Desktop 功能区的“主页”选项卡上,选择“发布”。In Power BI Desktop, on the Home tab of the ribbon, select Publish.


  2. 如果尚未登录到 Power BI,请完成整个登录过程。If you're not already signed in to Power BI, go through the sign-in process.

  3. 依次选择“我的工作区”、“选择”。Select My workspace, then Select.


  4. 完成发布后,选择“在 Power BI 中打开 storm-events.pbix”。When publishing is finished, select Open storm-events.pbix in Power BI.


    报表在服务中打开,包含在 Power BI Desktop 中定义的相同视觉对象和布局。The report opens in the service, with the same visuals and layout you defined in Power BI Desktop.

  5. 在报表右上角,选择“共享”。In the upper-right corner of the report, select Share.


  6. 在“共享报表”屏幕上,将添加组织中的某个同事,添加注释,然后选择“共享”。In the Share report screen, add a colleague in your organization, add a note, then select Share.


    如果你的同事拥有相应权限,他们可以访问你共享的报表。If your colleague has appropriate permissions, they can access the report that you shared.

清理资源Clean up resources

如果不想保留创建的报表,只需删除“storm-events.pbix”文件。If you don't want to keep the report you created, simply delete the storm-events.pbix file. 如果要删除已发布的报表,请按照下列步骤操作。If you want to remove the report you published, follow these steps.

  1. 在“我的工作区”下,向下滚动到“报表”并查找“storm-events”。Under My workspace, scroll down to REPORTS and find storm-events.

  2. 选择“storm-events”旁边的省略号 (. . .),然后选择“删除”。Select the ellipsis (. . .) next to storm-events, then select REMOVE.


  3. 确认删除。Confirm the removal.

后续步骤Next steps