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