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:

  • 關聯性中 [從] 資料行內的資料一律不得為 Null 或「空白」Data in the From column in the relationship is never Null or blank
  • [從] 資料行中的每個值必須對應到 [至] 資料行中For each value in the From column, there is a corresponding value in the To column

在此情況下,[從] 資料行可以是「一對多」關聯性中的「多」,或是「一對一」關聯性中第一個資料表的資料行。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. 此範例會連接到包含 Orders 資料表、Products 資料表和 Depots 資料表的資料來源。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. 例如,在如上所述的 Depots 資料表關聯性案例中,會產生:For example, in the case of the relationship to the Depots table described above, it would result in the following:

  • 顯示 Order Qty 總計的視覺效果顯示值為 40A visual showing the total Order Qty would show a value of 40
  • 顯示 Order Qty by Depot City 總計的視覺效果顯示總值只有 30 ,因為不包含 DepotIDNull 的 Order ID 1。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

深入了解 DirectQueryLearn 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.