資料模型中資料表之間的關聯

您的瀏覽器不支援視訊。請安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。
影片:Power View 和 PowerPivot 中的關聯

在不同資料表中的資料間建立關聯,強化資料分析功能。關聯是指兩個資料表之間的連線,根據每個資料表中的某個資料行而定。要入解關聯為什麼能對您有所助益,請想像您需要在業務上追蹤客戶訂單的資料。您可以使用結構如下的單一資料表追蹤所有資料:

客戶識別碼

姓名

電子郵件

折扣率

訂單識別碼

訂單日期

產品

數量

1

羅書成

chris.ashton@contoso.com

.05

256

2010-01-07

輕薄型數位相機

11

1

羅書成

chris.ashton@contoso.com

.05

255

2010-01-03

SLR 相機

15

2

康邁可

michal.jaworski@contoso.com

.10

254

2010-01-03

經濟型攝影機

27

這個方法會有效,但需要儲存大量重複的資料,例如每筆訂單的客戶電子郵件地址。儲存空間成本低廉,但如果電子郵件地址變更,您必須確實更新該客戶資料中的每個資料列。此問題的解決方案之一,是將資料分割成多個資料表,並定義這些資料表之間的關係。這是 SQL Server 等關聯式資料庫中使用的方法。例如,您匯入的資料庫可能會使用三個 相關資料表來表示訂單資料:

客戶

[客戶識別碼]

姓名

電子郵件

1

羅書成

chris.ashton@contoso.com

2

康邁可

michal.jaworski@contoso.com

客戶折扣

[客戶識別碼]

折扣率

1

.05

2

.10

訂單

[客戶識別碼]

訂單識別碼

訂單日期

產品

數量

1

256

2010-01-07

輕薄型數位相機

11

1

255

2010-01-03

SLR 相機

15

2

254

2010-01-03

經濟型攝影機

27

關聯性存在於您明確建立的資料模型中,或者當您同時匯入多個資料表時 Excel 代表您建立的資料模型中。您也可以使用 Power Pivot 增益集來建立或管理此模型。如需詳細資料,請參閱在 Excel 中建立資料模型

如果您使用 Power Pivot 增益集從相同資料庫匯入資料表,Power Pivot 即可根據 [方括號] 中的資料行來偵測資料表之間的關聯性,而且可以在幕後建立的資料模型中重現這些關聯性。如需詳細資訊,請參閱本文中的自動偵測和推斷關聯。如果從多個來源匯入資料表,則可依照建立兩個表格之間的關聯一文所述,手動建立關聯性。

頁面頂端

本文內容

資料行與索引鍵

關聯的類型

關聯與效能

資料表之間的多個關聯

資料表關聯的需求

在資料表關聯中不支援的項目

複合索引鍵和查閱資料行

多對多關聯

自我聯結與迴圈

在 Power Pivot 中自動偵測和推斷關聯

自動偵測命名集

推斷關聯

資料行與索引鍵

關聯是根據每個資料表中含有相同資料的資料行而定。例如,[客戶] 和 [訂單] 資料表可以相互關聯,因為它們都包含儲存客戶識別碼的資料行。範例中的資料行名稱相同,但這不是必要條件。其中一個資料行名稱可以是 [客戶識別碼],而另一個資料行名稱可以是 [客戶編號],只要 [訂單] 資料表中所有資料列都包含同時儲存於 [客戶] 資料表中的識別碼即可。

在關聯式資料庫中,有數種類型的索引鍵,它們通常是包含特殊內容的資料行。入解每個索引鍵的用途,可以協助您管理多重資料表的資料模型,此模型可為樞紐分析表、樞紐分析圖或 Power View 報表提供資料。

以下索引鍵對於達成我們的目的最有用處:

  • 主索引鍵︰可唯一識別資料表中某一個資料列,例如 [客戶] 資料表中的 [客戶識別碼]。

  • 其他索引鍵 (或候選索引鍵):主索引鍵以外的唯一資料行。例如,[員工] 資料表可能會儲存員工識別碼和身分證號碼,這兩者的內容都是唯一的。

  • 外部索引鍵:參照到另一個資料表中唯一資料行的資料行,例如 [訂單] 資料表中的 [客戶識別碼],此資料行會參照到 [客戶] 資料表中的 [客戶識別碼]。

在資料模型中,主索引鍵或其他索引鍵會稱為關聯資料行。如果資料表同時具有主索引鍵和其他索引鍵,您可以使用其中一個做為資料表關聯的基礎。外部索引鍵稱為來源資料行,或直接稱為資料行。在我們的範例中,會於 [訂單] 資料表中的 [客戶識別碼] (資料行) 和 [客戶] 資料表中的 [客戶識別碼] (查閱資料行) 之間定義關聯。如果您從關聯式資料庫匯入資料,依預設 Excel 會從其中一個資料表中選擇外部索引鍵,並從另一個資料表選擇對應的主索引鍵。不過,您可以使用任何具有唯一值的資料行做為查閱資料行。

關聯的類型

[客戶] 與 [訂單] 之間的關聯是一對多關聯。每位客戶可以有多筆訂單,但一筆訂單不能有多個客戶。其他關聯類型有一對一和多對多。定義每個客戶單一折扣率的 [客戶折扣] 資料表,與 [客戶] 資料表之間存在一對一關聯。

下表顯示三個資料表之間的關聯:

關聯

類型

查閱資料行

資料行

客戶-客戶折扣

一對一

客戶.客戶識別碼

客戶折扣.客戶識別碼

客戶-訂單

一對多

客戶.客戶識別碼

訂單.客戶識別碼

附註:  資料模型中不支援多對多關聯。多對多關聯的範例之一是產品和客戶之間的直接關係,客戶可以購買許多產品,且相同產品可以供許多客戶購買。

關聯與效能

建立任何關聯之後,Excel 通常必須重新計算使用來自新建立關聯中資料表之資料行的公式。視資料量與關聯複雜度而定,處理可能需要一些時間。如需詳細資料,請參閱重新計算公式

資料表之間的多個關聯

資料模型中的兩個資料表之間可以具有多個關聯。為建立精確的計算,Excel 需要從某個資料表通往另一個資料表的單一路徑。因此,每組資料表之間一次只能存在一項作用中的關聯。其他關聯為非作用中狀態,但您可以在公式和查詢中指定非作用中的關聯。在圖表檢視中,作用中的關聯會以實線表示,而非作用中的關聯會以虛線表示。例如,在 AdventureWorksDW2012 中,DimDate 資料表包含與 FactInternetSales 資料表中三個不同資料行有關的 DateKey 資料行:OrderDate、DueDate 和 ShipDate。如果 DateKey 和 OrderDate 之間的關聯為作用中關聯,則除非另行指定,否則該關聯會是公式中的預設關聯。

頁面頂端

資料表關聯的需求

當符合以下條件時可以建立關聯:

準則

描述

每個資料表的唯一識別碼

每個資料表必須具有可唯一識別該資料表中每個資料列的單一資料行。此資料行通常稱為主索引鍵。

唯一查閱資料行

查閱資料行中的資料值必須是唯一的。換句話說,資料行不能包含重複的值。在資料模型中,Null 和空白字串相當於空白,這是獨特的資料值。這表示,您無法在查閱欄中有多個 Null。

相容的資料類型

來源資料行和查閱資料行中的資料類型必須相容。如需資料類型的詳細資訊,請參閱資料模型中支援的資料類型。

在資料表關聯中不支援的項目

在資料模型中,如果索引鍵是複合索引鍵,則您無法建立資料表關聯。您也只能建立一對一和一對多關聯。不支援其他關聯類型。

複合索引鍵和查閱資料行

複合索引鍵是由一個以上資料行所組成。資料模型無法使用複合索引鍵。資料表必須永遠擁有剛好一個可唯一識別資料表中每一列的資料行。如果您匯入的資料表現存以複合索引鍵為基礎的關聯性,Power Pivot 中的 [資料表匯入精靈] 將會忽略該關聯性,因為它無法在模型中建立。

若兩個資料表都有多個資料行定義主索引鍵與外部索引鍵,而您要建立這兩個資料表之間的關聯性,在建立關聯性之前,您就必須先合併這些值,以建立單一索引鍵資料行。您可以在匯入資料之前執行這項作業,也可以使用 Power Pivot 增益集在資料模型中透過建立計算結果欄來執行。

多對多關聯

資料模型不能具有多對多關聯。您無法只在模型中新增聯合資料表 。不過,您可以使用 DAX 函數建立多對多關聯模型。

自我聯結與迴圈

資料模型中不允許使用自我聯結。自我聯結是指資料表及其本身之間的遞迴關係。自我聯結通常是用來定義父/子階層。例如,您可以將 [員工] 資料表聯結至其本身,產生顯示業務管理鏈的階層。

Excel 不允許在 活頁簿中的關聯間建立迴圈。換句話說,下列關聯組合是禁止的。

  • 資料表 1 的資料行 a  至 資料表 2 的資料行 f    

  • 資料表 2 的資料行 f  至 資料表 3 的資料行 n    

  • 資料表 3 的資料行 n  至 資料表 1 的資料行 a    

如果您嘗試建立的關聯會建立迴圈,則會產生錯誤。

頁面頂端

在 Power Pivot 中自動偵測和推斷關聯

使用 Power Pivot 增益集匯入資料的其中一個優點就是 Power Pivot 有時可以偵測關聯,並在它於 Excel 中建立的資料模型內建立新的關聯。

當您匯入多個資料表時,Power Pivot 會自動偵測資料表之間的任何現有關聯性。而且,當您建立樞紐分析表時,Power Pivot 也會分析資料表中的資料。它會偵測尚未定義的可能關聯性,而且會建議應包含在這些關聯性中的適當資料行。 

偵測演算法會使用資料行之值和中繼資料的相關統計資料,來推斷關聯的機率。

  • 所有相關資料行中的資料類型應該相容。自動偵測僅支援整數及文字資料類型。如需資料類型的詳細資訊,請參閱資料模型中支援的資料類型。

  • 要成功偵測關聯,查閱資料行中的唯一索引鍵數目必須大於「多」端資料表中的值。也就是說,關聯中「多」端的索引鍵資料行不能包含任何查閱資料表索引鍵資料行中不存在的值。例如,假設您有一個資料表,其中列出產品及產品識別碼 (查閱資料表);另有一個銷售資料表,其中列出每項產品的銷售 (關聯中的「多」端)。如果您的銷售記錄包含在 [產品] 資料表中沒有對應識別碼的產品識別碼,就無法自動建立關聯,但您可能可以手動加以建立。若要讓 Excel 偵測關聯,您需要先以遺失的產品識別碼更新 [產品] 查閱資料表。

  • 確認「多」端索引鍵資料行的名稱和查閱資料表中的索引鍵資料行名稱非常類似。名稱不需要完全相同。例如,在商業環境中,包含本質相同之資料的資料行,通常有多種名稱變化:Emp ID、EmployeeID、Employee ID、EMP_ID 等。演算法會偵測類似的名稱,並為這些名稱類似或完全相符的資料行指派較高的可能性。因此,要提高建立關聯的可能性,您可以嘗試將匯入資料中的資料行重新命名,使用類似現有資料表中資料行的名稱。如果 Excel 找到多項可能的關聯,則不會建立關聯。

這項資訊可以幫助您入解為何無法偵測到所有關聯,或是中繼資料 (例如欄位名稱和資料類型) 的變更能夠如何提升自動關聯偵測的結果。如需詳細資訊,請參閱關聯疑難排解

命名集的自動偵測

不會自動偵測樞紐分析表中命名集和相關欄位之間的關聯。您可以手動建立這些關聯。如果您想要使用自動關聯偵測,請移除各個命名集,並直接從命名集將個別欄位新增到樞紐分析表。

推斷關聯

在某些情況下,資料表間的關聯會自動鏈結。例如,如果您在以下前兩組資料表之間建立關聯,就會推斷另外兩個資料表之間也有關聯,然後自動建立關聯。

  • 產品和類別 -- 手動建立

  • 產品和子類別 -- 手動建立

  • 產品和子類別 -- 推斷出關聯

若要自動鏈結關聯,關聯必須是單向的,如上所示。例如,如果 [銷售] 和 [產品] 以及 [銷售] 和 [客戶] 之間存在初始關聯,則不會推斷關聯。這是因為 [產品] 和 [客戶] 之間的關聯是多對多關聯。

頁面頂端

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×