使用 Power BI 连接器可视化 Azure Cosmos DB 数据Visualize Azure Cosmos DB data by using the Power BI connector

适用于: SQL API

Power BI 是一种在线服务,你可以使用它创建和共享仪表板和报表。Power BI is an online service where you can create and share dashboards and reports. Power BI Desktop 是一种报表创作工具,可以从各种数据源检索数据。Power BI Desktop is a report authoring tool that enables you to retrieve data from various data sources. Azure Cosmos DB 是可以与 Power BI Desktop 一起使用的一种数据源。Azure Cosmos DB is one of the data source that you can use with Power BI Desktop. 可以使用 Power BI 的 Azure Cosmos DB 连接器将 Power BI Desktop 连接到 Azure Cosmos DB 帐户。You can connect Power BI Desktop to Azure Cosmos DB account with the Azure Cosmos DB connector for Power BI. 将 Azure Cosmos DB 数据导入到 Power BI 之后,可以对其进行转换,创建报表,以及将报表发布到 Power BI。After you import Azure Cosmos DB data to Power BI, you can transform it, create reports, and publish the reports to Power BI.

本文介绍了将 Azure Cosmos DB 帐户连接到 Power BI Desktop 所需执行的步骤。This article describes the steps required to connect Azure Cosmos DB account to Power BI Desktop. 在连接后,可以导航到某个集合,提取数据,将 JSON 数据转换为表格格式,以及将报表发布到 Power BI。After connecting, you navigate to a collection, extract the data, transform the JSON data into tabular format, and publish a report to Power BI.

备注

Azure Cosmos DB 的 Power BI 连接器连接到 Power BI Desktop。The Power BI connector for Azure Cosmos DB connects to Power BI Desktop. 在 Power BI Desktop 中创建的报表可以发布到 PowerBI.com。Reports created in Power BI Desktop can be published to PowerBI.com. 无法从 PowerBI.com 执行 Azure Cosmos DB 数据的直接提取。Direct extraction of Azure Cosmos DB data cannot be performed from PowerBI.com.

备注

目前,仅 Azure Cosmos DB SQL API 和 Gremlin API 帐户支持使用 Power BI 连接器连接 Azure Cosmos DB。Connecting to Azure Cosmos DB with the Power BI connector is currently supported for Azure Cosmos DB SQL API and Gremlin API accounts only.

先决条件Prerequisites

在按照此 Power BI 教程中的说明操作之前,请确保已拥有对以下资源的访问权限:Before following the instructions in this Power BI tutorial, ensure that you have access to the following resources:

要在 PowerBI.com 中共享报表,必须在 PowerBI.com 中拥有帐户。To share your reports in PowerBI.com, you must have an account in PowerBI.com. 若要了解有关 Power BI 和 Power BI Pro 的详细信息,请参阅 https://powerbi.microsoft.com/pricingTo learn more about Power BI and Power BI Pro, see https://powerbi.microsoft.com/pricing.

让我们开始吧Let's get started

本教程假设读者是一位研究世界各地的火山的地理学家。In this tutorial, let's imagine that you are a geologist studying volcanoes around the world. 火山数据存储在一个 Azure Cosmos DB 帐户中,JSON 文档格式如下所示:The volcano data is stored in an Azure Cosmos DB account and the JSON document format is as follows:

{
    "Volcano Name": "Rainier",
        "Country": "United States",
        "Region": "US-Washington",
        "Location": {
          "type": "Point",
          "coordinates": [
            -121.758,
            46.87
          ]
        },
        "Elevation": 4392,
        "Type": "Stratovolcano",
        "Status": "Dendrochronology",
        "Last Known Eruption": "Last known eruption from 1800-1899, inclusive"
}

你将从该 Azure Cosmos DB 帐户中检索火山数据并在交互式 Power BI 报表中将数据可视化。You will retrieve the volcano data from the Azure Cosmos DB account and visualize data in an interactive Power BI report.

  1. 运行 Power BI Desktop。Run Power BI Desktop.

  2. 可以“获取数据” 、查看“最近使用的源” 或直接从“欢迎”屏幕“打开其他报表” 。You can Get Data, see Recent Sources, or Open Other Reports directly from the welcome screen. 单击右上角的“X”以关闭屏幕。Select the "X" at the top right corner to close the screen. 显示 Power BI Desktop 的“报表” 视图。The Report view of Power BI Desktop is displayed.

    Power BI Desktop 报表视图 - Power BI 连接器

  3. 选择“主页” 功能区,并单击“获取数据” 。Select the Home ribbon, then click on Get Data. 应出现“获取数据” 窗口。The Get Data window should appear.

  4. 单击“Azure”,选择“Azure Cosmos DB (Beta)”,并单击“连接”。 Click on Azure, select Azure Cosmos DB (Beta), and then click Connect.

    Power BI Desktop 获取数据 - Power BI 连接器

  5. 在“预览连接器”页中,单击“继续” 。On the Preview Connector page, click Continue. 此时会显示“Azure Cosmos DB”窗口。 The Azure Cosmos DB window appears.

  6. 如下所示指定想要从其中检索数据的 Azure Cosmos DB 帐户终结点 URL,并单击“确定” 。Specify the Azure Cosmos DB account endpoint URL you would like to retrieve the data from as shown below, and then click OK. 要使用自己的帐户,可以从 Azure 门户的“密钥”边栏选项卡的 URI 框中取得 URL 。To use your own account, you can retrieve the URL from the URI box in the Keys blade of the Azure portal. 可选:你可以提供数据库名称、集合名称,也可以使用导航器来选择数据库和集合以标识数据来源。Optionally you can provide the database name, collection name or use the navigator to select the database and collection to identify where the data comes from.

  7. 如果是首次连接到此终结点,则会提示输入帐户密钥。If you are connecting to this endpoint for the first time, you are prompted for the account key. 若使用自己的帐户,请从 Azure 门户的“只读密钥” 边栏选项卡中的“主密钥” 框中取得密钥。For your own account, retrieve the key from the Primary Key box in the Read-only Keys blade of the Azure portal. 输入相应的密钥,然后单击“连接” 。Enter the appropriate key and then click Connect.

    我们建议在生成报表时使用只读密钥。We recommend that you use the read-only key when building reports. 这可防止主密钥不必要地暴露于潜在的安全风险中。This prevents unnecessary exposure of the primary key to potential security risks. 可以从 Azure 门户的 密钥 边栏选项卡获取只读密钥。The read-only key is available from the Keys blade of the Azure portal.

  8. 帐户成功连接后,将出现“导航器”窗格 。When the account is successfully connected, the Navigator pane appears. “导航器” 会在帐户下显示数据库的列表。The Navigator shows a list of databases under the account.

  9. 单击并展开作为报表数据来源的数据库,并选择 volcanodb(你的数据库名称可能不同)。Click and expand on the database where the data for the report comes from, select volcanodb (your database name can be different).

  10. 现在,选择包含要检索的数据的集合,选择 volcano1(你的集合名称可能不同)。Now, select a collection that contains the data to retrieve, select volcano1 (your collection name can be different).

    预览窗格显示“记录” 项的列表。The Preview pane shows a list of Record items. 文档在 Power BI 中表示为一种“记录” 类型。A Document is represented as a Record type in Power BI. 同样,文档内部的嵌套 JSON 块也是“记录” 。Similarly, a nested JSON block inside a document is also a Record.

    Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 导航器窗口

  11. 单击“编辑”在新窗口中启动查询编辑器,以转换数据 。Click Edit to launch the Query Editor in a new window to transform the data.

平展和转换 JSON 文档Flattening and transforming JSON documents

  1. 切换到“Power BI 查询编辑器”窗口,其中“文档”列位于中心窗格 。Switch to the Power BI Query Editor window, where the Document column in the center pane.

    Power BI Desktop 查询编辑器

  2. 单击“文档” 列标题右侧的扩展器。Click on the expander at the right side of the Document column header. 会显示带有字段列表的上下文菜单。The context menu with a list of fields will appear. 选择报表所需的字段,例如,火山名称、国家/地区、区域、位置、海拔、类型、状态和已知的上次喷发。Select the fields you need for your report, for instance, Volcano Name, Country, Region, Location, Elevation, Type, Status and Last Know Eruption. 取消选中“使用原始列名作为前缀”框,然后单击“确定”。 Uncheck the Use original column name as prefix box, and then click OK.

    Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 扩展文档

  3. 中央窗格显示其中包含所选字段的结果预览。The center pane displays a preview of the result with the fields selected.

    Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 平铺结果

  4. 在我们的示例中,“位置”属性是文档中的一个 GeoJSON 块。In our example, the Location property is a GeoJSON block in a document. 可以看到,“位置”在 Power BI Desktop 中表示为一种“记录” 类型。As you can see, Location is represented as a Record type in Power BI Desktop.

  5. 单击 Document.Location 列标题右侧的扩展器。Click on the expander at the right side of the Document.Location column header. 此时会显示包含类型和坐标字段的上下文菜单。The context menu with type and coordinates fields appear. 让我们选择坐标字段,确保未选中“使用原始列名作为前缀”,然后单击“确定”。 Let's select the coordinates field, ensure Use original column name as prefix is not selected, and click OK.

    Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 位置记录

  6. 中心窗格现在显示一个“列表” 类型的坐标列。The center pane now shows a coordinates column of List type. 如教程的开头所示,本教程中的 GeoJSON 数据是“点”类型,纬度值和经度值记录在坐标数组中。As shown at the beginning of the tutorial, the GeoJSON data in this tutorial is of Point type with Latitude and Longitude values recorded in the coordinates array.

    coordinates[0] 元素表示经度,coordinates[1] 表示纬度。The coordinates[0] element represents Longitude while coordinates[1] represents Latitude.

    Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 坐标列表

  7. 若要平展坐标数组,请创建一个名为 LatLong 的 自定义列To flatten the coordinates array, create a Custom Column called LatLong. 选择“添加列” 功能区并单击“自定义列” 。Select the Add Column ribbon and click on Custom Column. 此时将显示“自定义列” 窗口。The Custom Column window appears.

  8. 为新列提供一个名称,例如 LatLong。Provide a name for the new column, e.g. LatLong.

  9. 接下来,为新列指定自定义公式。Next, specify the custom formula for the new column. 对于我们的示例,我们将如下所示,使用以下公式连接逗号分隔的纬度值和经度值:Text.From([coordinates]{1})&","&Text.From([coordinates]{0})For our example, we will concatenate the Latitude and Longitude values separated by a comma as shown below using the following formula: Text.From([coordinates]{1})&","&Text.From([coordinates]{0}). 单击 “确定”Click OK.

    有关数据分析表达式 (DAX)(包括 DAX 函数)的详细信息,请访问 Power BI Desktop 中的 DAX 基础知识For more information on Data Analysis Expressions (DAX) including DAX functions, please visit DAX Basics in Power BI Desktop.

    Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 添加自定义列

  10. 现在,中央窗格将显示填充了值的新 LatLong 列。Now, the center pane shows the new LatLong columns populated with the values.

    Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 自定义经纬度列

    如果新列中出现错误,请确保“查询设置”下应用的步骤与下图相符:If you receive an Error in the new column, make sure that the applied steps under Query Settings match the following figure:

    应用的步骤应该为“源”、“导航”、“展开的文档”、“展开的文档位置”、“添加自定义”

    如果步骤不同,请删除额外的步骤,并重试添加自定义列。If your steps are different, delete the extra steps and try adding the custom column again.

  11. 单击“关闭并应用” 以保存数据模型。Click Close and Apply to save the data model.

    针对 Azure Cosmos DB Power BI 连接器的 Power BI 教程 - 关闭并应用

生成报表Build the reports

可以在 Power BI Desktop 报表视图中开始创建报表来将数据可视化。Power BI Desktop Report view is where you can start creating reports to visualize data. 可以通过将字段拖放到“报表” 画布中来创建报表。You can create reports by dragging and dropping fields into the Report canvas.

Power BI Desktop 报表视图 - 拖放必填字段

在“报表”视图中,应找到:In the Report view, you should find:

  1. 在“字段” 窗格中,可以看到包含可用于报表的字段的数据模型的列表。The Fields pane, this is where you can see a list of data models with fields you can use for your reports.
  2. “可视化” 窗格。The Visualizations pane. 一个报表可以包含单个或多个可视化效果。A report can contain a single or multiple visualizations. 从“可视化” 窗格中选取符合需求的视觉对象类型。Pick the visual types fitting your needs from the Visualizations pane.
  3. 在“报表” 画布中,可以为报表生成视觉效果。The Report canvas, this is where you build the visuals for your report.
  4. “报表” 页。The Report page. 可以在 Power BI Desktop 中添加多个报表页。You can add multiple report pages in Power BI Desktop.

下面演示创建简单交互地图视图报表的基本步骤。The following shows the basic steps of creating a simple interactive Map view report.

  1. 对于我们的示例,我们创建显示每座火山的位置的地图视图。For our example, we will create a map view showing the location of each volcano. 在“可视化” 窗格中,单击如上屏幕截图中突出显示的“地图”视觉对象类型。In the Visualizations pane, click on the Map visual type as highlighted in the screenshot above. 应会看到绘制在“报表” 画布上的地图视觉对象类型。You should see the Map visual type painted on the Report canvas. “可视化” 窗格还应该显示一组与地图视觉对象类型相关的属性。The Visualization pane should also display a set of properties related to the Map visual type.

  2. 现在,从“字段” 窗格中将经纬度字段拖放到“可视化” 窗格中的“位置” 属性。Now, drag and drop the LatLong field from the Fields pane to the Location property in Visualizations pane.

  3. 接下来,将“火山名称”字段拖放到“图例” 属性。Next, drag and drop the Volcano Name field to the Legend property.

  4. 然后,将“海拔”字段拖放到“大小” 属性。Then, drag and drop the Elevation field to the Size property.

  5. 现在应会看到显示一组表示每座火山位置的气泡的地图视觉对象,气泡的大小与火山的海拔相关联。You should now see the Map visual showing a set of bubbles indicating the location of each volcano with the size of the bubble correlating to the elevation of the volcano.

  6. 此时已创建了基本报表。You now have created a basic report. 可以通过添加更多可视化效果进一步自定义该报表。You can further customize the report by adding more visualizations. 在本例中,我们添加了火山类型切片器以使报表具有交互性。In our case, we added a Volcano Type slicer to make the report interactive.

  7. 在“文件”菜单中,单击“保存” 并将该文件保存为 PowerBITutorial.pbix。On the File menu, click Save and save the file as PowerBITutorial.pbix.

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

要共享报表,必须在 PowerBI.com 中拥有帐户。To share your report, you must have an account in PowerBI.com.

  1. 在 Power BI Desktop 中,单击“主页” 功能区。In the Power BI Desktop, click on the Home ribbon.

  2. 单击“发布”。 Click Publish. 系统会提示输入 PowerBI.com 帐户的用户名和密码。You are be prompted to enter the user name and password for your PowerBI.com account.

  3. 凭据通过身份验证后,报表将发布到选择的目标。Once the credential has been authenticated, the report is published to your destination you selected.

  4. 单击“在 Power BI 中打开‘PowerBITutorial.pbix’” ,以在 Power BI.com 上查看并共享报表。Click Open 'PowerBITutorial.pbix' in Power BI to see and share your report on PowerBI.com.

    已成功发布到 Power BI!在 Power BI 中打开教程

在 PowerBI.com 中创建仪表板Create a dashboard in PowerBI.com

既然有了报表,就将报表分享到 PowerBI.com 吧Now that you have a report, lets share it on PowerBI.com

从 Power BI Desktop 发布报表到 PowerBI.com 时,会在 PowerBI.com 租户中生成一个“报表”和“数据集”。When you publish your report from Power BI Desktop to PowerBI.com, it generates a Report and a Dataset in your PowerBI.com tenant. 例如,将一个名为 PowerBITutorial 的报表发布到 PowerBI.com 后,PowerBITutorial 会出现在 PowerBI.com 的“报表”和“数据集”部分。For example, after you published a report called PowerBITutorial to PowerBI.com, you will see PowerBITutorial in both the Reports and Datasets sections on PowerBI.com.

PowerBI.com 中新“报表”和“数据集”的屏幕截图

若要创建可共享的仪表板,请单击 PowerBI 报表上的“固定活动页”按钮。To create a sharable dashboard, click the Pin Live Page button on your PowerBI.com report.

演示如何将报表固定到 PowerBI.com 的屏幕截图

然后按照从报表固定磁贴中的说明创建新仪表板。Then follow the instructions in Pin a tile from a report to create a new dashboard.

创建仪表板之前也可以临时修改报表。You can also do ad hoc modifications to report before creating a dashboard. 但是,建议使用 Power BI Desktop 执行修改并将报表重新发布到 PowerBI.com。However, it's recommended that you use Power BI Desktop to perform the modifications and republish the report to PowerBI.com.

后续步骤Next steps