Power BI Desktop 中的假设引用完整性设置Assume referential integrity settings in Power BI Desktop

当连接到使用 DirectQuery 的数据源时,你可以使用“假设引用完整性”,以对数据源运行更高效的查询。When connecting to a data source using DirectQuery, you can use the Assume Referential Integrity selection to enable running more efficient queries against your data source. 此功能对基础数据有要求,并且仅在使用 DirectQuery 时可用。This feature has a few requirements of the underlying data, and it is only available when using DirectQuery.

设置“假设引用完整性”允许数据源上的查询使用 INNER JOIN 语句而不是 OUTER JOIN 语句,从而提高查询效率。Setting Assume referential integrity enables queries on the data source to use INNER JOIN statements rather than OUTER JOIN, which improves query efficiency.

使用假设引用完整性的要求Requirements for using Assume referential integrity

此设置为高级设置,并且仅在连接到使用 DirectQuery 的数据时才可用。This is an advanced setting, and is only enabled when connecting to data using DirectQuery. 若要使“假设引用完整性”正常工作,必须满足以下要求:The following requirements are necessary for Assume referential integrity to work properly:

  • 关系中 From 列中的数据始终不能为 Null空白Data in the From column in the relationship is never Null or blank
  • From 列中的每个值在 To 列中都有对应的值For each value in the From column, there is a corresponding value in the To column

在此上下文中, From 列是 一对多 关系中的 ,或是 一对一 关系中第一个表中的列。In this context, the From column is the Many in a One-to-Many relationship, or it is the column in the first table in a One-to-One relationship.

使用假设引用完整性的示例Example of using Assume referential integrity

下面的示例演示了在数据连接中使用“假设引用完整性”时,“假设引用完整性”的行为方式。The following example demonstrates how Assume referential integrity behaves when used in data connections. 该示例连接到包含订单表、产品表和仓库表的数据源。The example connects to a data source that includes an Orders table, a Products table, and a Depots table.

  1. 下图显示了 Orders 表和 Products 表,请注意引用完整性存在于 Orders[ProductID]Products[ProductID] 之间。In the following image that shows the Orders table and the Products table, note that referential integrity exists between Orders[ProductID] and Products[ProductID]. Orders 表中的 [ProductID] 列始终不能为 Null ,所有值也会出现在 Products 表中。The [ProductID] column in the Orders table is never Null, and every value also appears in the Products table. 在这种情况下,应设置“假设引用完整性”以获得更高效的查询(使用此设置不会更改视觉对象中显示的值)。As such, Assume Referential Integrity should be set to get more efficient queries (using this setting does not change the values shown in visuals).

  2. 在下一个图像中,请注意 Orders[DepotID]Depots[DepotID] 之间不存在引用完整性,因为某些 OrdersDepotIDNullIn the next image, notice that no referential integirty exists between Orders[DepotID] and Depots[DepotID], because the DepotID is Null for some Orders. 在这种情况下, 应设置“假设引用完整性”。As such, Assume Referential Integrity should not be set.

  3. 最后,在下面的表中,Orders[CustomerID]Customers[CustID] 之间不存在引用完整性;CustomerID 中包含 Customers 表中不存在的值(此例中为 CustX )。Finally, no referential integrity between Orders[CustomerID] and Customers[CustID] in the following tables; the CustomerID contains some values (in this case, CustX) that do not exist in the Customers table. 在这种情况下, 应设置“假设引用完整性”。As such, Assume Referential Integrity should not be set.

设置假设引用完整性Setting Assume referential integrity

若要启用此功能,请选中“假设引用完整性”旁边的复选框(如下图所示)。To enable this feature, select the checkbox next to Assume Referential Integrity as shown in the following image.

选中后,将对数据验证此设置,以确保没有 Null 或不匹配的行。When selected, the setting is validated against the data to ensure there are no Null or mismatched rows. 但是 ,在值的数量非常大的情况下,验证不能保证没有引用完整性问题。However, for cases with a very large number of values, the validation is not a guarantee that there are no referential integrity issues.

此外,验证将在编辑关系时执行,并且 反映数据的任何后续更改。In addition, the validation occurs at the time of editing the relationship, and does not reflect any subsequent changes to the data.

如果错误地设置了假设引用完整性,会发生什么?What happens if you incorrectly set Assume referential integrity?

如果在数据中有引用完整性问题时设置“假设引用完整性”,此设置不会导致错误。If you set Assume Referential Integrity when there are referential integrity issues in the data will not result in errors. 但是,将导致数据明显不一致。However, it will result in apparent inconsistencies in the data. 例如,在上述仓库表的关系的情况下,会导致以下结果:For example, in the case of the relationship to the Depots table described above, it would result in the following:

  • 视觉对象显示总的 订单数量 值为 40A visual showing the total Order Qty would show a value of 40
  • 视觉对象显示总的 按仓库城市的订单数量 值仅为 30 ,因为它不包含订单 ID 1(其 DepotIDNull )。A visual showing the total Order Qty by Depot City would show a total value of only 30, because it would not include Order ID 1, where DepotID is Null.

后续步骤Next steps

了解有关 DirectQuery 的详细信息Learn more about DirectQuery

获取有关 Power BI 中的关系的详细信息Get more information about Relationships in Power BI

了解有关 Power BI Desktop 中的关系视图的详细信息。Learn more about Relationship View in Power BI Desktop.