使用 BI 兼容模式查询度量视图

BI 兼容模式允许你从外部 BI 工具查询 Unity 目录指标视图 。 启用后，Azure Databricks重写 BI 工具生成的查询以正确评估指标视图度量值。

本页介绍如何启用 BI 兼容性模式、工作原理、支持的方案和已知限制。

BI 兼容模式是从 BI 工具查询指标视图的几种方法之一。 有关所有方法的概述（包括使用 MEASURE() 函数的自定义 SQL），请参阅 将指标视图与外部 BI 工具配合使用。

Requirements

• Databricks SQL 仓库或运行 Databricks Runtime 18.0 或更高版本的群集。
• 支持使用直通 SQL 或 DirectQuery 连接到 Azure Databricks 的 BI 工具。
• 能够在 BI 工具中运行会话级 SQL 配置（例如，通过初始 SQL 脚本或启动命令）。

启用 BI 兼容性模式

通过在会话开始时运行以下 SQL 配置命令来启用 BI 兼容模式。 该命令取决于你所连接的计算资源：

• 在 Databricks SQL 仓库中：

  SET metric_view_bi_compatibility_mode = true;
  

• 在群集上：

  SET spark.databricks.sql.metricView.bi.compatibilityMode.enabled = true;
  

设置配置的方式取决于 BI 工具。 例如，在 Tableau 中，可以在连接对话框中使用 “初始 SQL ”字段。

设置的BI 兼容模式仅适用于当前会话。 每个新连接都必须再次设置配置。

DirectQuery 模式

BI 兼容模式要求查询在 Azure Databricks SQL 引擎上运行。 如果 BI 工具同时提供导入和直接查询模式，请使用直接查询（或实时连接），以便查询传递到可应用重写机制的Azure Databricks。

BI 兼容性模式的工作原理

指标视图在 BI 工具中呈现为常规表。 启用 BI 兼容模式后，Azure Databricks重写 BI 工具生成的查询以正确查询指标视图。

BI 兼容性模式自动处理两种类型的查询：

• 聚合查询：当 BI 工具生成对度量值使用标准聚合函数（例如 SUM）的查询时，BI 兼容模式会重写这些聚合，以遵守指标视图中的度量值定义。 始终使用 SUM 作为度量值列的聚合类型。 SQL 引擎始终应用正确的基础度量逻辑。
• 数据预览和架构发现：当 BI 工具请求非聚合数据（例如列预览或数据示例）时，度量列返回 null 值而不是错误。 维度列通常返回其值。

支持的方案

启用 BI 兼容模式时，以下 BI 工具功能适用于大多数 BI 工具。

维度与度量值

指标视图包含两种类型的列：度量值和维度。 了解生成报表时的差异非常重要。

• 度量值：度量值的聚合逻辑在指标视图中定义（例如， SUM(price * quantity) 或 COUNT(DISTINCT customer_id)）。 在 BI 工具中，度量列的聚合应始终设置为 SUM。 SQL 引擎会自动应用正确的度量逻辑。 如果需要其他聚合，请修改指标视图中的度量值定义。 不要更改 BI 工具端的聚合。
• 维度：维度的行为类似于常规表列。 可以对维度应用任何标准的 BI 操作，包括聚合、分组、筛选、排序和分箱。 如果数值字段充当维度（而不是度量值），则所有标准聚合类型都在该字段上正常工作。

最佳做法

• 始终在数据集中包含单个指标视图。 指标视图是你的语义定义的一种表示。
• 创建文件夹以组织维度列（例如，为日期维度中的每个维度列创建一个“日期”文件夹）。
• 重命名维度，为其赋予用户友好的名称。
• 将数值维度列更改为非聚合汇总类型。
• 为每个度量列使用SUM() 创建包装度量，并隐藏原始度量列（例如，Total Sales = SUM('Store Sales'[total_sales])）。
• 将度量值组织到专用文件夹中。
• 仅在可视化中使用封装指标。

局限性

BI 兼容性模式对 BI 工具生成和处理查询的方式有有限的控制。 以下限制适用。

聚合度量值时仅使用 SUM

始终将聚合类型设置为 SUM 对于度量值列。 所有聚合函数（SUM、、COUNT、MINMAX）都重写为基础度量值定义，因此它们都返回相同的结果。 选择其他聚合类型可能会导致意外行为：

• AVG显示1.0是因为某些 BI 工具在内部计算AVGSUM / COUNT，并且两者都返回相同的度量值。
• 计数（Distinct）、标准偏差、方差和中值生成与重写机制不兼容的查询模式，并生成错误或错误结果。

如果需要其他聚合，请修改指标视图中的度量值定义。 指标视图定义中完全支持所有聚合类型。

非累加性度量值的总计

某些 BI 工具通过在客户端重新聚合每组值而不是发出单独的查询来计算总计。 这会为累加性度量值（例如，SUM(revenue)）生成正确的结果，因为在本地重新聚合会给出正确的答案。

但是，对于非累加性度量值（例如， SUM(revenue) / COUNT(DISTINCT customer)或涉及 DISTINCT的任何比率、百分比或表达式），总计可能会显示不正确的值，因为求和预先分组的比率在数学上与计算整个数据集的比率不相等。

度量列中的定量切片器

度量值列上的定量（范围）切片器可能无法按预期工作。 某些 BI 工具可能会查询 MIN 度量值和 MAX 度量值以确定滑块范围，但两者都重写为相同的基础度量值，将范围折叠为单个点。 对度量值的筛选器仍然有效。 只有范围切片器受到影响。

度量值不能用作分类值或维度值

如果使用度量值列作为分类值（例如轴、图例或切片器），查询将失败并返回以下错误：

[METRIC_VIEW_MEASURE_IN_GROUP_BY] '<measure>' measure columns cannot be used in GROUP BY clause or as categorical values. We recommend wrapping them with an aggregate function such as SUM() for the expected behavior. SQLSTATE: 42K0E

多个 BI 工具功能取决于将度量值列视为离散维度，因此它们会产生相同的错误。 例如，在 Tableau 中：

• 基于度量值列构建的直方图（例如，通过 显示方式 创建）无法生成，因为它会对该度量值进行分箱。
• 从度量值列创建的分箱看似创建成功，但将其添加到可视化对象中时，会因前述错误而失败。

若要在这些功能中使用度量值，请在指标视图定义中定义等效维度或度量值。

包含多个度量值的计算字段

引用单个度量值的计算字段正常工作。 某些 BI 工具先提取聚合结果，然后在客户端执行计算（例如，将收入分入低、中、高类别）。

但是，将多个度量列组合在单个聚合（例如，SUM(m1 + m2)）中的表达式不会被 BI 兼容模式重写，这可能导致错误或意外结果。

需要联接的功能

指标视图不能与其他表或另一个指标视图联接。 联接指标视图的任何查询都失败，并出现以下错误：

[METRIC_VIEW_JOIN_NOT_SUPPORTED] The metric view is not allowed to use joins.

这影响的不仅仅是显式联接。 某些 BI 工具功能自动生成联接到子查询或临时表的 SQL，因此即使没有添加联接，它们也会失败并出现相同的错误。 在 Tableau 中，以下功能可以编译为联接，并且可能无法与 BI 兼容模式一起使用。 若要使用其中每一项，请在指标视图定义中对相应的等效逻辑进行建模：

• 数据源中定义的联接和关系。 在指标视图定义本身中对连接关系进行建模。
• 前 N 个筛选器、条件筛选器、集和条件集，Tableau 作为子查询实现，该子查询将联接回主查询。 在指标视图中将排名或筛选的值建模为单独的维度。
• 详细信息级别（LOD）表达式（FIXED，INCLUDEEXCLUDE），Tableau 通常使用联接到主查询的单独子查询进行计算。 给定表达式是否成功取决于 Tableau 生成的查询。 可以将许多 LOD 表达式建模为指标视图中的度量值。

具体化度量视图

BI 兼容模式支持查询使用物化的指标视图。 查询具体化指标视图时，查询优化器会自动将查询路由到合适的具体化，以提高性能，或者在没有合适的具体化可用时回退到源数据。 此路由是透明的，不会更改生成报表的方式。

请记住以下注意事项：

• 数据新鲜度：查询结果反映的可能是最近一次物化刷新中的数据，而非最新的源数据。 在默认 relaxed 查询重写模式下，优化器会使用物化结果，且不会检查其是否为最新状态。
• 刷新行为：物化结果会按照指标视图中定义的计划进行刷新。 还可以通过运行 REFRESH MATERIALIZED VIEW来触发手动刷新。 物化必须先完成刷新，优化器才能使用它进行查询重写。

有关具体化的工作原理的详细信息，包括刷新计划和查询重写，请参阅 指标视图的具体化。

其他资源

• 将指标视图与外部 BI 工具配合使用
• Unity 目录语义
• 创建指标视图
• 指标视图的高级技术