教學課程:在 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 訂用帳戶,請在開始前建立免費 Azure 帳戶If you don't have an Azure subscription, create a free Azure account 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

必要條件Prerequisites

除了 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.comSign in to https://dataexplorer.azure.com.

  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.

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

    StormEvents
    | 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 資料總管 (Beta)],然後選取 [連線]。Search for Azure Data Explorer, select Azure Data Explorer (Beta), 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.windows.netCluster should be in the form https://<ClusterName>.<Region>.kusto.windows.net. 輸入 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 變更為 StormEventsIn 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] 和 [State]。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] 和 [State]。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