使用 Power BI 和 OData 查询连接数据

项目
03/20/2024

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

若要将数据拉取到 Power BI 中，建议使用开放数据协议（OData）查询。 OData 是 ISO/IEC 批准的 OASIS 标准，用于定义生成和使用 REST API 的最佳做法。有关详细信息，请参阅 OData 文档。

若要快速开始，检查使用 OData 查询的示例报表概述。有关其他方法的信息，请参阅 Power BI 集成概述。

Power BI 可以运行 OData 查询，该查询可将筛选或聚合的数据集返回到 Power BI。 OData 查询具有两个优点：

所有筛选都是服务器端完成的。仅返回所需的数据，这会导致刷新时间缩短。
可以预聚合数据服务器端。 OData 查询可以执行聚合，例如工作项汇总和生成失败率。聚合是服务器端完成的，并且仅将聚合值返回到 Power BI。借助预聚合，可以跨大型数据集执行聚合，而无需将所有详细信息数据拉取到 Power BI 中。

在本文中，学习如何：

编写和测试 OData 查询。
从 Power BI 运行 OData 查询。

先决条件

若要查看 Analytics 数据并查询服务，你需要是具有基本访问权限或更高访问权限的项目的成员。默认情况下，向所有项目成员授予查询 Analytics 和定义 Analytics 视图的权限。
若要了解有关服务和功能启用以及常规数据跟踪活动的其他先决条件，请参阅访问 Analytics 的权限和先决条件。

使用 Visual Studio Code 编写和测试 OData 查询

编写和测试 OData 的最简单方法是将 Visual Studio Code 与 OData 扩展配合使用。 Visual Studio Code 是 Windows、Mac 和 Linux 上可用的免费代码编辑器。 OData 扩展提供语法突出显示和其他可用于编写和测试查询的函数。

安装 Visual Studio Code 和 OData 扩展

安装 Visual Studio Code。
打开 Visual Studio Code，选择“扩展”，然后搜索 odata。在结果列表中，选择 vscode-odata，然后安装它。
例如 filename.odata，在 Visual Studio Code 中创建和保存 OData 文件。根据需要为其命名，但必须具有 .odata 扩展才能启用 OData 扩展功能。

编写 OData 查询

编写 OData 查询。有关示例查询，请参阅使用 OData 查询的示例报告概述。

以下查询返回特定区域路径下的前 10 个工作项。

将 {organization}、{project} 和 {area path} 替换为你自己的值。

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
    $select=WorkItemId,Title,WorkItemType,State,CreatedDate
    &$filter=startswith(Area/AreaPath,'{area path}')
    &$orderby=CreatedDate desc
    &$top=10

若要跨项目查询，请完全省略 /{project} 。

有关详细信息，请参阅 OData 查询快速参考。

在 Visual Studio Code 中编写查询后，应会看到语法突出显示。

显示 Visual Studio Code OData 扩展的屏幕截图，其中突出显示了语法。

测试 OData 查询

若要测试 OData 查询，请将光标置于查询文本中的任意位置，然后选择“查看>命令面板”。
在搜索框中，输入 odata 以显示所有 OData 命令。
选择 OData：打开。此操作将多行查询合并为一行 URL，并在默认浏览器中打开它。
- OData 查询结果集采用 JSON 格式。若要查看结果，请安装浏览器的 JSON 格式化程序扩展。 Chrome 和 Microsoft Edge 都有多个选项。
- 如果查询出错，Analytics 服务将返回 JSON 格式的错误。例如，此错误指出查询选择了不存在的字段。

验证查询是否正常工作后，可以从 Power BI 运行它。

从 Power BI 运行 OData 查询

若要从 Power BI 运行 OData 查询，请按照下一部分中的步骤进行操作。

将多行 OData 查询合并为单行查询

在 Power BI 中使用查询之前，必须将多行 OData 查询转换为单行查询。执行此操作的最简单方法是将 Visual Studio Code 与 OData 扩展配合使用，并使用 OData：Combine 命令。

注意

在 filename.odata 文件中，你可能希望先创建多行查询文本的副本，然后运行 OData：合并副本。首先执行此步骤，因为无法将单行查询转换回可读的多行查询。

在 Visual Studio Code 中，将查询放置在查询文本中的任意位置，然后选择“查看>命令面板”。 在搜索框中，输入 odata。然后在结果列表中，选择 “OData：合并”。

多行查询将转换为单行查询。
复制整个行，以便在下一部分使用。

从 Power BI 运行查询

选择“获取数据>OData 源”。 有关详细信息，请参阅使用 OData 查询创建 Power BI 报表。
在 OData 源 窗口中的 URL 框中，粘贴在上一部分中复制的 OData 查询，然后选择“ 确定”。

Power BI 显示预览页。

指定查询选项

在预览页上选择“编辑”以打开Power Query 编辑器。
选择功能区上的高级编辑器。
水平滚动以查看[Implementation="2.0"]“查询”窗格中的参数。
替换为 [Implementation="2.0"] 以下字符串：

[Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]
注意

若要防止限制错误，请执行以下操作：
- 指示 Power BI 引用 OData v4。
- 指示 Analytics 服务省略 null 的任何值，从而提高查询性能。 Power Query 尝试通过为每个遇到 null 值生成另一个查询来解析 null 值作为错误。此操作可能会导致成千上万的查询，这些查询会快速超过使用阈值，超过用户帐户的限制。
Power BI 需要执行以下操作才能成功对 Azure DevOps Analytics 服务运行 OData 查询。
选择“确定”关闭高级编辑器并返回到 Power BI Power Query 编辑器。可以使用Power Query 编辑器执行以下可选操作：
- 将 Query1 查询重命名为更具体的内容。
- 将列转换为特定类型。 Power BI 自动检测类型，但可能需要将列转换为特定数据类型。
- 添加计算列。
- 删除列。
- 将列展开到特定字段中。

使用数据创建报表

选择“ 关闭并应用 ”以保存设置并将数据拉取到 Power BI 中。数据刷新后，可以像在 Power BI 中一样创建报表。

使用 Power BI 和 OData 查询连接数据

先决条件