データ モデルのテーブル間のリレーションシップ
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 その他…表示数を減らす
リレーションシップを作成して、さまざまなテーブルを認識することで、データ分析にさらに力を加えます。 リレーションシップは、データを含む 2 つのテーブル間の接続です。各テーブルの 1 つの列がリレーションシップの基礎になります。 リレーションシップの便利な点を示す例として、ここでは仕事での顧客からの注文のデータを追跡しているものとします。 次のような構造を持つ 1 つのテーブル内のすべてのデータを追跡できます。
| CustomerID | Name | DiscountRate | OrderID | OrderDate | Product | Quantity | |
|---|---|---|---|---|---|---|---|
| 1 | Ashton | [email protected] | .05 | 256 | 2010-01-07 | Compact Digital | 11 |
| 1 | Ashton | [email protected] | .05 | 255 | 2010-01-03 | SLR Camera | 15 |
| 2 | Jaworski | [email protected] | .10 | 254 | 2010-01-03 | Budget Movie-Maker | 27 |
この方法は機能しますが、すべての注文の顧客の電子メール アドレスなど、多くの冗長データを格納する必要があります。 ストレージは安価ですが、電子メール アドレスが変更された場合は、その顧客のすべての行を必ず更新する必要があります。 この問題の解決策の 1 つは、データを複数のテーブルに分割し、それらのテーブル間のリレーションシップを定義することです。 これは、SQL Serverなどのリレーショナル データベースで使用されるアプローチです。 たとえば、インポートするデータベースは、次の 3 つの関連テーブルを使用して注文データを表す場合があります。
Customers
| [CustomerID] | Name | |
|---|---|---|
| 1 | Ashton | [email protected] |
| 2 | Jaworski | [email protected] |
CustomerDiscounts
| [CustomerID] | DiscountRate |
|---|---|
| 1 | .05 |
| 2 | .10 |
Orders
| [CustomerID] | OrderID | OrderDate | Product | Quantity |
|---|---|---|---|---|
| 1 | 256 | 2010-01-07 | Compact Digital | 11 |
| 1 | 255 | 2010-01-03 | SLR Camera | 15 |
| 2 | 254 | 2010-01-03 | Budget Movie-Maker | 27 |
リレーションシップは、データ モデル内に存在します。明示的に作成するもの、または複数のテーブルを同時にインポートするときに Excel によって自動的に作成されるリレーションシップです。 モデルの作成や管理は、PowerPivot アドインを使って行うこともできます。 詳しくは、「 target=”_blank” title=”Excel でのデータ モデルの作成” rel=”noopener”>Excel でのデータ モデルの作成」をご覧ください。
PowerPivot アドインを使ってテーブルを同じデータベースからインポートした場合、PowerPivot ではテーブル間のリレーションシップを角かっこ ([ ]) で囲んで示した列に基づいて検出し、自動的に作成されるデータ モデルでこれらのリレーションシップを再現できます。 詳しくは、この記事の「リレーションシップの自動検出と自動推定」をご覧ください。 複数のソースからテーブルをインポートした場合、「 target=”_blank” title=”2 つのテーブル間のリレーションシップの作成” rel=”noopener”>2 つのテーブル間のリレーションシップの作成」の説明に従って、リレーションシップを手動で作成できます。
__
列およびキー
リレーションシップは、各テーブル内にある同じデータを含む列に基づいて作成されるものです。 たとえば、顧客 ID を格納する列が各テーブルに含まれている場合、Customers テーブルを Orders テーブルと関連付けることができます。 この例では列名が同じですが、列名が同じことは必須条件ではありません。 Orders テーブル内のすべての行に Customers テーブルにも保存されている ID が入ってさえいれば、片方が CustomerID で他方が CustomerNumber でもかまいません。
リレーショナル データベースには、いくつかの種類のキーがあります。 キーは通常、特殊なプロパティを持つ列です。 各キーの目的を理解すると、ピボットテーブル、ピボットグラフ、または Power View レポートにデータを提供するマルチテーブル データ モデルを管理するのに役立ちます。
キーには多くの種類がありますが、ここでの目的で最も重要なのは次のとおりです。
主キー: Customers テーブルの CustomerID など、テーブル内の行を一意に識別します。
代替キー (または候補キー): 一意の主キー以外の列。 たとえば、Employees テーブルには従業員 ID と社会保障番号が格納され、どちらも一意です。
外部キー: Orders テーブルの CustomerID など、別のテーブル内の一意の列を参照する列。Customers テーブルの CustomerID を参照します。
データ モデルでは、主キーや代替キーは関係列と呼ばれます。 1 つのテーブルが主キーと代替キーの両方を持っている場合は、そのどちらか 1 つをテーブル リレーションシップの基として使用できます。 外部キーは、 ソース列または単に列と呼ばれます。 この例では、Orders テーブルの CustomerID (列) と Customers テーブル (ルックアップ列) の CustomerID の間でリレーションシップを定義します。 Excel でリレーショナル データベースからデータをインポートすると、既定では、 1 つのテーブルの外部キーと別のテーブルの対応する主キーが選択されます。 とはいえ、一意の値を持つ列であれば、どの列でもルックアップ列に使用できます。
__
リレーションシップの種類
顧客と注文の関係は、1 対多のリレーションシップです。 どの顧客も複数の注文を持つことができますが、1 つの注文が複数の顧客を持つことはできません。 もう 1 つの重要なテーブルリレーションシップは、1 対 1 です。 この例では、顧客ごとに 1 つの割引率を定義する CustomerDiscounts テーブルは、Customers テーブルと 1 対 1 の関係を持ちます。
次の表は、3 つのテーブル (Customers、CustomerDiscounts、Orders) の間のリレーションシップ を示しています 。
| リレーションシップ | 種類 | ルックアップ列 | 列 |
|---|---|---|---|
| Customers-CustomerDiscounts | 一対一 | Customers.CustomerID | CustomerDiscounts.CustomerID |
| Customers-Orders | 一対多 | Customers.CustomerID | Orders.CustomerID |
注: 多対多リレーションシップは、データ モデルではサポートされていません。 多対多の関係の例としては、製品と顧客の直接的な関係があり、顧客は多くの製品を購入でき、同じ製品を多くの顧客が購入できます。
__
リレーションシップとパフォーマンス
リレーションシップが作成されたら、通常、新しく作成されたリレーションシップ内のテーブルの列を使用する数式を再計算する必要があります。 この処理には、データの量とリレーションシップの複雑さに応じて、ある程度の時間がかかります。 詳細については、「 target=”_blank” title=”数式の再計算” rel=”noopener”>数式の再計算」を参照してください。
__
テーブル間の複数のリレーションシップ
データ モデルでは、2 つのテーブル間に複数のリレーションシップを持たせることができます。 正確な計算を作成するには、1 つのテーブルから次のテーブルへの 1 つのパスが必要です。 このため、テーブルの各組み合わせ間で同時にアクティブになるリレーションシップは常に 1 つです。 他のリレーションシップは非アクティブですが、数式とクエリで非アクティブなリレーションシップを指定できます。
ダイアグラム ビューでは、アクティブなリレーションシップは実線であり、非アクティブなリレーションシップは破線です。 たとえば、AdventureWorksDW2012 では、テーブル DimDate には、テーブル FactInternetSales の 3 つの異なる列 (OrderDate、DueDate、****ShipDate) に関連する列 DateKey が含まれています。 DateKey と OrderDate の間のリレーションシップがアクティブである場合、数式では、それがリレーションシップを指定しなかった場合の既定のリレーションシップになります。
__
テーブルのリレーションシップの要件
リレーションシップの作成時の要件を次に示します。
| 抽出条件 | 説明 |
|---|---|
| テーブルごとに一意の識別子 | 各テーブルが、テーブル内の各行を一意に識別する列を 1 つ持っている必要があります。 この列は、通常、主キーと呼ばれます。 |
| 一意の参照列 | ルックアップ列のデータ値は固有であることが必要です。 言い換えると、列内に重複データを含めることはできません。 データ モデルでは、null 値と空の文字列は、個別のデータ値である空白と同等に扱われます。 つまり、ルックアップ列に複数の null 値を指定することはできません。 |
| 互換性のあるデータ型 | ソース列とルックアップ列のデータ型は互換性がとれている必要があります。 データ型の詳細については、「データ target=”_blank” title=”モデルでサポートされるデータ型” rel=”noopener”>モデルでサポートされるデータ型」を参照してください。 |
__
Excel データ モデルでサポートされていないデータベース機能
データ モデルでは、キーが複合キーの場合は、テーブル リレーションシップを作成することはできません。 また、作成できるリレーションシップは一対一と一対多のリレーションシップに限られています。 その他の種類のリレーションシップはサポートされていません。
複合キーとルックアップ列
複合キーは、複数の列で構成されます。 データ モデルでは複合キーを使用できません。テーブルには、テーブル内の各行を一意に識別する列が常に 1 つだけ必要です。 複合キーに基づいて既存のリレーションシップを持つテーブルをインポートする場合、Power Pivot のテーブルインポート ウィザードでは、そのリレーションシップはモデルで作成できないため無視されます。
主キーと外部キーを定義する複数の列を持つ 2 つのテーブル間にリレーションシップを作成するには、リレーションシップを作成する前に、最初に値を結合して 1 つのキー列を作成します。 これを行うには、データをインポートする前に、または Power Pivot アドインを使用してデータ モデルに計算列を作成します。
多対多リレーションシップ
データ モデルに多対多リレーションシップを持たせることはできません。 モデルに交差テーブル を追加することすらできません。 とはいえ、DAX 関数を使って多対多リレーションシップをモデル化することは可能です。
自己結合とループ
自己結合は、データ モデルでは許可されていません。 自己結合とは、テーブルとそのテーブル自身との間の再帰的なリレーションシップです。 自己結合は、親子階層構造を定義するのによく使われます。 たとえば、 Employees テーブルをそのテーブル自体と結合して、ビジネスでのマネジメント チェーンを表す階層構造を作ったりできます。
Excel では、ブック内のリレーションシップ間のループの作成は 許されていません。 つまり、次のような一連のリレーションシップは禁止されています。
テーブル 1 の列 a から テーブル 2 の列 f
テーブル 2 の列 f から テーブル 3 の列 n
テーブル 3 の列 n から テーブル 1 の列 a
結果的にループが作成されるリレーションシップを作成しようとすると、エラーが生成されます。
__
PowerPivot でのリレーションシップの自動検出と自動推定
データのインポートに PowerPivot アドインを使う利点の 1 つは、PowerPivot でリレーションシップが検出され、Excel で作成するデータ モデルに新しいリレーションシップを作成できることです。
複数のテーブルをインポートする場合、PowerPivot によってテーブル間の既存のリレーションシップが自動的にすべて検出されます。 また、ピボットテーブルを作成すると、PowerPivot によってテーブル内のデータが分析されます。 これにより 、定義されていない潜在的なリレーションシップが検出され、そのリレーションシップに含める適切な列が提示されます。
検出アルゴリズムでは、列の値およびメタデータに関する統計データを使用して、リレーションシップの確率を推定します。
データ型は、関連するすべての列の間で互換性がなくてはなりません。 自動検出でサポートされている型は、整数とテキスト データだけです。 データ型について詳しくは、「 target=”_blank” title=”データ モデルの” rel=”noopener”>データ モデルのデータ型」をご覧ください。
リレーションシップがうまく検出されるには、ルックアップ列内の一意のキーの数が、多側のテーブル内の値より多くなくてはなりません。 つまり、リレーションシップの多側のキー列に、ルックアップ テーブルのキー列には含まれていない値が含まれていてはなりません。 たとえば、製品をその ID でリストするテーブル (ルックアップ テーブル) と各製品の販売をリストする Sales テーブル (リレーションシップの多側) があったとします。 どれかの販売レコードに、Products テーブル内に対応する ID が入っていない製品の ID が含まれていたとすると、このリレーションシップは自動的には作成されませんが、手動で作成することは可能です。 Excel でこのリレーションシップが検出されるようにするには、まず Product ルックアップ テーブルを更新して、見つからない製品の ID を含めておく必要があります。
多側のキー列の名前は、ルックアップ テーブル内のキー列の名前とよく似たものにしてください。 名前が厳密に同じである必要はありません。 たとえば、ビジネス設定では、基本的に同じデータを含む列の名前にバリエーションがあります。 Emp ID、EmployeeID、Employee ID、EMP_ID などです。 検出アルゴリズムは、よく似た名前を検出して、よく似た名前やまったく同じ名前を持つ列に、より高い可能性を割り当てます。 このため、インポートするデータの列の名前を既存のテーブル内の列とよく似たものに変更すれば、リレーションシップが自動的に作成される可能性を高めることができます。 リレーションシップの候補が複数見つかった場合、Excel は、リレーションシップを作成しません。
この情報は、一部のリレーションシップが検出されない理由や、フィールド名、データ型などのメタデータを変更することでリレーションシップの自動検出結果がどのように改善されるかを理解するうえで役立ちます。 詳しくは、「 target=”_blank” title=”テーブルのリレーションシップのトラブルシューティング” rel=”noopener”>テーブルのリレーションシップのトラブルシューティング」をご覧ください。
名前付きセットの自動検出
ピボットテーブル内の名前付きセットと関連フィールドの間のリレーションシップは、自動的には検出されません。 こういったリレーションシップは、手動で作成できます。 自動リレーションシップ検出を使いたい場合は、各名前付きセットを削除し、名前付きセットに入っていた個々のフィールドをピボットテーブルに直接追加してください。
リレーションシップの推定
テーブル間のリレーションシップは、自動的に連鎖化される場合もあります。 たとえば、次に示す最初の 2 セットのテーブルの間にリレーションシップを作成すると、他の 2 つのテーブルとの間にリレーションシップが存在すると推定され、自動的にリレーションシップが確立されます。
Products と Category: 手動で作成
Category と SubCategory: 手動で作成
Products と SubCategory: リレーションシップの推定
リレーションシップが自動的に連鎖化されるようにするには、上に示すように、一連のリレーションシップが一方向になっている必要があります。 たとえば、最初のリレーションシップが Sales と Products の間、および Sales と Customers の間にあると、リレーションシップは推定されません。 これは、Products と Customers の間のリレーションシップが多対多リレーションシップになるからです。