通过

多对多关系指导

  • 项目

本文面向使用 Power BI Desktop 的数据建模者。 它描述了三种不同的多对多建模方案。 它还提供指南，帮助您如何在模型中为它们进行成功设计。

注意

本文未介绍模型关系简介。 如果不熟悉关系、其属性或配置方式，建议先阅读 Power BI Desktop 文章中的 模型关系。

了解星型架构设计也很重要。 有关详细信息，请参阅 了解星型架构的重要性以及它在 Power BI 中的作用。

有三种不同的多对多方案。 可根据需要应用这些场景：

关联多对多维度

经典多对多方案涉及两个实体，例如银行客户和银行账户。 假设客户可以有多个帐户，帐户可以有多个客户。 如果某个帐户有多个客户，这些客户通常会被称为“联合帐户持有人”。

对这些实体进行建模是简单的。 一个维度表存储帐户，另一个维度表存储客户。 与维度表的特征一样，每个表中都有一个唯一标识符（ID）列。 若要为两个表之间的关系建模，需要第三个表。 此表通常称为“桥接表”。 此示例的目的是为每个客户帐户关联存储一行。 有趣的是，当此表仅包含标识列时，它称为 无事实的数据表。

下面是三个模型表的简单关系图。

关系图显示了三个模型表。设计在下一段落中进行介绍。

第一个表命名为 Account，其中包含两列：AccountID 和 Account。 第二个表命名为 AccountCustomer，其中包含两列：AccountID 和 CustomerID。 第三个表命名为 Customer，它包含两列：CustomerID 和 Customer。 任何这些表之间都不存在关系。

可添加两个一对多关系来关联这些表。 下面是相关表的更新模型关系图。 添加了一个名为 Transaction 的事实数据表。 它记录帐户交易。 桥接表和所有标识符列已被隐藏。

关系图显示了一个包含四个表的模型。已添加一对多关系以关联所有表。

为了帮助说明关系筛选器的传播方式，已修改模型关系图以显示表行。

显示模型表及其行的图表。以下段落描述了这四个表的行信息。

下面的项目符号列表中显示了四个表的行详细信息：

  • Account 表有两行：
    • AccountID1 适用于 Account-01
    • AccountID2 适用于 Account-02
  • Customer 表有两行：
    • CustomerID 91 代表 Customer-91
    • CustomerID 92 代表 Customer-92
  • AccountCustomer 表有三行：
    • AccountID1 与 91 CustomerID91 相关联
    • AccountID1 与 CustomerID92 相关联
    • AccountID 2 与 CustomerID92 关联
  • Transaction 表有三行：
    • Date2019 年 1 月 1 日，AccountID1，Amount100
    • Date2019 年 2 月 2 日，AccountID2，Amount200
    • Date2019 年 3 月 3 日，AccountID1，Amount-25

让我们看看查询模型时会发生什么情况。

在下图中，有两个表视觉对象，汇总了 Transaction 表的 Amount 列。 第一个视觉对象按帐户分组，因此 Amount 列的总和表示“帐户余额”。 第二个视觉对象按客户分组，因此 Amount 列的总和表示“客户余额”。

图表显示两个表格视觉对象并排排列。以下段落介绍了视觉对象的内容。

第一个表视觉对象（帐户余额）有两列：Account 和 Amount。 它显示以下结果：

  • 帐户-01 余额为 75。
  • Account-02 余额为 200。
  • 总计为 275。

第二个表视觉对象（客户余额）有两列：Customer 和 Amount。 它显示以下结果：

  • Customer-91 余额为 275。
  • Customer-92 余额为 275。
  • 总计为 275。

快速浏览表格中的各行和“账户余额”图表，可以确认每个账户和总金额的结果是正确的。 这是因为对每个帐户进行分组都会使筛选器传播到该帐户的 Transaction 表。

但是，“客户余额”视觉对象似乎不太正确。 此视觉对象中的每个客户都有与总余额相同的余额。 仅当每个客户都是每个帐户的联合帐户持有人时，此结果才是正确的。 在此示例中，情况并非如此。 存在一个问题，它与筛选器的传播相关。 筛选器没有一直流向 Transaction 表。

如果遵循从 Customer 表到 Transaction 表的关系筛选器方向，则可以确定 Account 和 AccountCustomer 表之间的关系正按错误的方向传播。 此关系的筛选器方向必须设置为 Both。

关系图显示了模型已更新。它现在双向筛选。

关系图显示了并排放置的两个相同报表视觉对象。第一个视觉对象未发生更改，而第二个视觉对象则发生了更改。

与预期一样，帐户余额视觉对象没有变化。

但是，“客户余额”视觉对象现在显示以下结果：

  • Customer-91 余额为 75。
  • Customer-92 余额为 275。
  • 总计为 275。

“客户余额”视觉对象现显示正确的结果。 请按照过滤器说明自行操作，并查看客户余额的计算方式。 另请注意，直观合计是针对所有客户而言。

不熟悉模型关系的人可能会得出结论，结果不正确。 他们可能会问：为什么 Customer-91 和 Customer-92 的总余额不等于350（75 + 275）？

他们问题的答案在于理解这种多对多关系。 每个客户余额都可表示多个帐户余额的相加，因此客户余额不可累加。

关于关联多对多维度的指导

如果维度表之间存在多对多关系，请按照以下指南操作：

  • 将每个多对多关联的实体添加为模型表，确保其具有一个 ID 列。
  • 添加桥接表以存储关联的实体。
  • 在三个表之间创建一对多关系。
  • 配置一个双向关系以允许筛选器继续传播到事实表。
  • 如果存在缺失 ID 值是不合适的，请禁用 Is Nullable 属性 - 确定缺失值的来源时，数据刷新将失败。
  • 隐藏桥接表（除非它包含报表所需的其他列或度量值）。
  • 隐藏不适用于报告的任何 ID 列（例如，列存储代理键值时）。
  • 如果需要显示 ID 列，请确保它位于关系的“一”侧（始终隐藏“多”侧列）。 这是因为应用于“一”侧的筛选器可提高筛选器性能。
  • 若要避免混淆或误解，请向报表用户传达说明- 可以使用文本框或 视觉对象标头工具提示添加说明。

建议不要将多对多维度表直接关联。 此设计方法需要使用多对多基数设置关系。 从概念上讲，它可以实现，但它意味着相关列可能包含重复值。 但这是一种广为接受的设计做法，即，使维度表包含一个 ID 列。 维度表应始终使用 ID 列作为关系的“一”侧。

关联多对多事实

另一种不同的多对多场景类型涉及关联两个事实表。 两个事实数据表可以直接相关。 此设计技术对于快速简单的数据浏览非常有用。 但是，很明显，我们通常不建议采用这种设计方法。 我们将在本部分的后面部分解释原因。

让我们考虑一个涉及两个事实数据表的示例：Order 和 Fulfillment。 Order 表每个订单行包含一行，Fulfillment 表可以包含每个订单行的零行或多行。 Order 表中的行表示销售订单。 Fulfillment 表中的行表示已发货的订单项。 多对多关系将每个表中的 OrderID 列关联，筛选器仅从 Order 表传播（这意味着 Order 表筛选 Fulfillment 表）。

图示显示包含两个表的模型：订单和履约。

关系基数设置为 Many-to-many，以支持在两个表中存储重复 OrderID 列值。 在 Order 表中，重复的 ID 值可能存在，因为订单可以有多个行。 在 Fulfillment 表中，可能存在重复的 ID 值，因为订单可能有多行，并且许多发货可履行订单行。

现在，让我们看一下表行。 在 Fulfillment 表中，请注意，多个发货可履行订单行。 （缺少订单行意味着尚未履行该订单。）

显示模型表格行的示意图。以下段落中描述了这两个表格的行详细信息。

下面的项目符号列表描述了两个表的行的详细信息：

  • Order 表有五行：
    • OrderDate2019 年 1 月 1 日，OrderID1，OrderLine1，ProductIDProd-A，OrderQuantity5，Sales50
    • OrderDate2019 年 1 月 1 日， OrderID1， OrderLine2， ProductIDProd-B， OrderQuantity10， Sales80
    • OrderDate2019 年 2 月 2 日， OrderID2， OrderLine1， ProductIDProd-B， OrderQuantity5， Sales40
    • OrderDate2019 年 2 月 2 日， OrderID2， OrderLine2， ProductIDProd-C， OrderQuantity1， Sales20
    • OrderDate2019 年 3 月 3 日，OrderID3，OrderLine1，ProductIDProd-C，OrderQuantity5，Sales100
  • Fulfillment 表有四行：
    • FulfillmentDate2019 年 1 月 1 日， FulfillmentID50， OrderID1， OrderLine1， FulfillmentQuantity2
    • FulfillmentDate2019 年 2 月 2 日， FulfillmentID51， OrderID2， OrderLine1， FulfillmentQuantity5
    • FulfillmentDate2019 年 2 月 2 日， FulfillmentID52， OrderID1， OrderLine1， FulfillmentQuantity3
    • FulfillmentDate2019 年 1 月 1 日， FulfillmentID53， OrderID1， OrderLine2， FulfillmentQuantity10

让我们看看查询模型时会发生什么情况。 下面的表视觉对象按 Order 表 OrderID 列比较了订单和履行数量。

关系图显示了一个包含三列的表视觉对象：OrderID、OrderQuantity 和 FulfillmentQuantity。

图像准确地显示了结果。 但是，模型的有用性有限，因为只能按 Order 表 OrderID 列进行筛选或分组。

关于关联多对多事实的指导

通常，建议不要使用多对多基数直接关联两个事实表。 主要原因是模型不会在报表可视化的筛选或分组方式上提供灵活性。 在此示例中，视觉对象只能按 Order 表 OrderID 列进行筛选或分组。 另一个原因与数据的质量有关。 如果数据存在完整性问题，则可能是在查询期间由于多对多基数和有限关系的性质而省略了一些行。

建议实现星型架构设计，而不是直接关联事实表。 这意味着要添加维度表。 然后，这些维度表通过一对多关系与事实数据表相连。 此设计方法非常可靠，因为它有效地提供了灵活的报告选项。 它允许你通过使用任何维度表列进行筛选或分组，并汇总任何相关事实数据表的列。

让我们考虑一个更好的解决方案。

关系图显示了一个包含六个表的模型：OrderLine、OrderDate、Order、Fulfillment、Product 和 FulfillmentDate。

请注意以下设计更改：

  • 模型现在有四个额外的表：OrderLine、OrderDate、Product和 FulfillmentDate。
  • 四个额外的表都是维度表，其中一对多关系将它们与事实表相关联。
  • OrderLine 表包含 OrderLineID 列，该列存储 OrderID 值乘以 100，加上 OrderLine 列值-每个订单行的 ID。
  • Order 和 Fulfillment 表现分别包含一个 OrderLineID 列，并且它们都不再包含 OrderID 和 OrderLine 列。
  • Fulfillment 表现包含 OrderDate 和 ProductID 列。
  • FulfillmentDate 表仅与 Fulfillment 表有关系。
  • 所有 ID 列都处于隐藏状态。

花时间采用星型架构设计具有以下优势：

  • 报表视觉对象可对维度表中的任何可见列进行筛选或分组。
  • 报表视觉对象可汇总事实表中的任何可见列。
  • 应用于 OrderLine、OrderDate 或 Product 表的筛选器传播到这两个事实表。
  • 所有关系都是一对多，每个关系都是常规关系。 不会屏蔽数据完整性问题。 有关关系评估的详细信息，请参阅 Power BI Desktop中的 模型关系。

关联更高粒度的事实

此多对多方案与本文中所述的其他两种方案大相径庭。

让我们考虑一个涉及四个表的示例：Date、Sales、Product和 Target。 Date 表和 Product 表是维度表，它们各自通过一对多关系与 Sales 事实数据表相连。 到目前为止，它代表了一个很好的星型架构设计。 但是，Target 表尚未与其他表相关。

显示由日期、销售、产品和目标四个表组成的模型图表。

Target 表包含三列：Category、TargetQuantity和 TargetYear。 表行显示了年份和产品类别的粒度。 换句话说，每个产品类别每年都会设置用于衡量销售业绩的目标。

显示“销售和目标事实数据表”的 关系图显示了 Sales 和 Target 事实表。Target 事实表有三列：TargetYear、Category 和 TargetQuantity。

由于 Target 表将数据存储在比维度表更高的级别，因此无法创建一对多关系。 只有一种关系是这样的。 让我们探讨 Target 表如何与维度表相关联。

关联更高粒度时间段

Date 和 Target 表之间的关系应该是一对多关系。 这是因为 TargetYear 列值是日期。 在此示例中，每个 TargetYear 列存储目标年份的第一个日期。

提示

当以较高的时间粒度存储事实数据时，请将列数据类型设置为“日期”（如果使用日期键，则设置为“整数”）。 在列中，存储一个值来表示时间段的第一天。 例如，一年周期记录为一年 1 月 1 日，月期间记录为当月的第一天。

但是，必须注意确保月份或日期级别筛选器产生有意义的结果。 如果没有任何特殊的计算逻辑，报表视觉对象可能会报告目标日期实际上是每年的第一天。 其他所有日期（一月除外的所有月份）都将目标数量汇总为 BLANK。

以下矩阵视觉对象显示了报表用户从一年向下钻取到其月份时会发生的情况。 视觉对象汇总了 TargetQuantity 列。 （矩阵行已启用显示不含数据的项选项。）

关系图显示了一个矩阵视觉对象，显示 2020 年的目标数量为 270。它按日期生成的值不正确。

为了避免此行为，建议使用度量值控制事实数据的汇总。 可控制汇总的一种方法是在查询较低级别时间段时返回 BLANK。 另一种方法（用一些复杂的 DAX 定义）是在较低级别的时间段内分配值。

请考虑以下度量值定义，该定义使用了 ISFILTERED DAX 函数。 仅当未筛选 Date 和 Month 列时，它才会返回一个值。

Target Quantity =
IF(
    NOT ISFILTERED('Date'[Date])
        && NOT ISFILTERED('Date'[Month]),
    SUM(Target[TargetQuantity])
)

以下矩阵视觉对象使用 Target Quantity 度量值。 它显示所有每月目标数量均为 BLANK。

显示两个矩阵视觉对象的 关系图显示了两个矩阵视觉对象。第一个视觉对象显示 2020 年第一个月的目标为 270，而第二个视觉对象为空白。

关联更高粒度（非日期）

将维度表中的非日期列与事实数据表（其粒度高于维度表）相关时，需要采用不同的设计方法。

Category 列（来自 Product 表和 Target 表）包含重复值。 因此，一对多关系没有“一”侧。 在这种情况下，需要创建多对多关系。 关系应以单一方向（从维度表到事实表）传播筛选器。

关系图显示了 Target 和 Product 表的模型。多对多关系将这两个表相关联。

现在，让我们看一下表行。

关系图显示了包含 Target 表和 Product 表的模型。多对多关系将两个 Category 列相关联。

在 Target 表中，有四行：每个目标年份的两行（2019 年和 2020 年），以及两个类别（服装和配件）。 在 Product 表中，有三种产品。 两个属于服装类别，一个属于配件类别。 其中一种服装颜色为绿色，其余两种为蓝色。

按 Product 表中的 Category 列进行分组的视觉对象生成以下结果。 然而，此视觉对象生成的结果正确。 现在，我们来看看当 Product 表中的 Color 列用于对目标数量进行分组时会发生什么情况。

显示两个表视觉对象的 关系图显示了两个表视觉对象。第一个视觉对象按“类别”分组，第二个视觉对象按“颜色”分组。第二个视觉对象生成的结果不正确。

该视觉对象生成的数据错误。 这里发生了什么？

对 Product 表的 Color 列进行筛选，得到了两行。 其中一行用于“服装”类别，另一行是“附件”类别。 这两个类别值作为筛选器传播到 Target 表。 换句话说，因为两个类别的产品使用了蓝色，所以将使用这些类别筛选目标。

为避免此行为，如前所述，建议使用度量值控制事实数据的汇总。

请考虑以下度量值定义。 请注意，类别级别下的所有 Product 表列都会针对筛选器进行测试。

Target Quantity =
IF(
    NOT ISFILTERED('Product'[ProductID])
        && NOT ISFILTERED('Product'[Product])
        && NOT ISFILTERED('Product'[Color]),
    SUM(Target[TargetQuantity])
)

以下表视觉对象使用 Target Quantity 度量值。 它显示所有颜色目标数量均为 BLANK。

显示两个表视觉对象的 关系图显示了两个表视觉对象。第一个视觉对象按“类别”分组，第二个视觉对象按“颜色”分组。第二个视觉对象生成了正确的空白结果。

最终的模型设计如下所示。

关系图显示了包含 Date 和 Target 表的模型以一对多关系关联。

关于关联更高粒度事实的指导

如果需要将维度表与事实表相关联，并且事实表存储的行比维度表行的粒度高，请按照此指南进行操作：

  • 对于更高粒度的事实日期
    • 在事实数据表中，存储时间段的第一个日期。
    • 在日期表和事实表之间创建一对多关系。
  • 对于其他更高粒度的事实
    • 在维度表和事实表之间创建多对多关系。
  • 对于这两种类型
    • 使用度量计算逻辑来控制数据汇总——当使用较低级别的维度列进行筛选或分组时，结果返回 BLANK。
    • 隐藏可汇总事实数据表列 ， 确保只能使用度量值来汇总事实数据表。

相关内容

有关本文的详细信息，请查看以下资源：