リレーションシップの概要
このトピックでは、PowerPivot for Excel のテーブル間で定義できるリレーションシップについて説明します。このトピックのセクションは次のとおりです。
リレーションシップの概要
リレーションシップの要件
リレーションシップの自動検出と自動推定
このトピックを読み終えると、リレーションシップの概要、リレーションシップを定義する際の要件、および PowerPivot for Excel でリレーションシップを自動検出する方法を理解できるようになります。また、データベース担当者がリレーションシップについて説明する際に使用する用語についてもいくつか説明します。
リレーションシップの概要
リレーションシップは、各テーブル内の 1 つ以上の列 (PowerPivot の場合は、各テーブルの厳密に 1 つの列) に基づく、2 つのデータ テーブル間の接続を表します。リレーションシップが有用である理由を理解するために、業務において顧客注文のデータを追跡する場合を考えます。すべてのデータは、次のような構造を持つ単一のテーブルでも追跡できます。
CustomerID |
Name |
DiscountRate |
OrderID |
OrderDate |
Product |
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 などのリレーショナル データベースで使用されている方法です。たとえば、PowerPivot for Excel にインポートしたデータベースでは、次の 3 つの関連テーブルを使用して注文データを表すことができます。
Customers
[CustomerID] |
Name |
|
|---|---|---|
1 |
Ashton |
chris.ashton@contoso.com |
2 |
Jaworski |
michal.jaworski@contoso.com |
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 |
これらのテーブルを同じデータベースからインポートした場合、PowerPivot ではテーブル間のリレーションシップを角かっこ ([ ]) で囲んで示した列に基づいて検出できるため、PowerPivot ウィンドウでこれらのリレーションシップを再現できます。詳細については、このトピックの「リレーションシップの自動検出と自動推定」を参照してください。複数のソースからテーブルをインポートした場合、「2 つのテーブル間のリレーションシップの作成」の説明に従って、リレーションシップを手動で作成できます。
列およびキー
リレーションシップは、同じデータが格納されている各テーブルの列に基づきます。たとえば、Customers テーブルと Orders テーブルにはどちらにも顧客 ID を格納する列が含まれているため、これらのテーブルは相互に関連付けることができます。この例では列名が同じになっていますが、これは必須条件ではありません。Orders テーブルのすべての行に Customers テーブルにも格納されている ID が含まれていれば、一方の列名を CustomerID にして、もう一方の列名を CustomerNumber にすることもできます。
リレーショナル データベースのキーにはいくつかの種類があります。これらは通常、特殊なプロパティを含む単なる列です。最もよく使用するキーとして、次の 4 種類があります。
主キー: Customers テーブルの CustomerID のように、テーブル内の行を一意に識別します。
代替キー (候補キー): 主キー以外の一意の列。たとえば、従業員 ID と社会保障番号が Employees テーブルに格納されることがありますが、これらは両方とも一意です。
外部キー: 別のテーブル内にある一意の列を参照する列。たとえば、Orders テーブルの CustomerID は、Customers テーブルの CustomerID を参照します。
複合キー: 複数の列で構成されるキー。複合キーは、PowerPivot for Excel ではサポートされません。詳細については、このトピックの「複合キーと参照列」を参照してください。
PowerPivot for Excel では、主キーや代替キーを関連する参照列と呼びます (単に参照列とも呼びます)。テーブルに主キーと代替キーの両方がある場合は、どちらも参照列として使用できます。 外部キーは、ソース列または列と呼ばれます。 上記の例では、Orders テーブルの CustomerID (列) と Customers テーブルの CustomerID (参照列) の間でリレーションシップが定義されます。リレーショナル データベースからデータをインポートすると、PowerPivot for Excel では、あるテーブルの外部キーと、別のテーブルの対応する主キーが既定により選択されます。 ただし、参照列には、一意の値を持つ任意の列を使用できます。
リレーションシップの種類
Customers と Orders のリレーションシップは、一対多リレーションシップと呼ばれます。各顧客は複数の注文を行うことができますが、1 つの注文に複数の顧客を含めることはできません。リレーションシップのその他の種類として、一対一と多対多があります。顧客ごとに単一の割引率を定義する CustomerDiscounts テーブルは、Customers テーブルと一対一のリレーションシップを持ちます。多対多リレーションシップの例としては、Products と Customers の間の直接的なリレーションシップがあります。このリレーションシップでは、1 人の顧客が複数の製品を購入することができ、同じ製品を複数の顧客が購入することができます。PowerPivot for Excel のユーザー インターフェイスでは、多対多リレーションシップはサポートされていません。 詳細については、このトピックの「多対多リレーションシップ」を参照してください。
次の表は、3 つのテーブル間のリレーションシップを示しています。
リレーションシップ |
種類 |
参照列 |
列 |
|---|---|---|---|
Customers-CustomerDiscounts |
一対一 |
Customers.CustomerID |
CustomerDiscounts.CustomerID |
Customers-Orders |
一対多 |
Customers.CustomerID |
Orders.CustomerID |
リレーションシップとパフォーマンス
通常、リレーションシップが作成された後、PowerPivot for Excel は、新しく作成されたリレーションシップに関連するテーブルから列を使用するすべての数式を再計算する必要があります。データの量およびリレーションシップの複雑さによっては、この処理に時間がかかることがあります。詳細については、「数式の再計算」を参照してください。
リレーションシップの要件
PowerPivot for Excel には、リレーションシップの作成時に従う必要がある要件がいくつかあります。
テーブル間に 1 つのリレーションシップ
複数のリレーションシップを作成すると、テーブル間の依存関係があいまいになる可能性があります。正確な計算を作成するには、テーブル間のパスを 1 つにする必要があります。そのため、テーブルの各ペアの間に作成できるリレーションシップは 1 つだけです。たとえば、AdventureWorks DW 2008 の DimDate テーブルには、FactInternetSales テーブルの 3 つの異なる列 (OrderDate、DueDate、および ShipDate) に関連付けられた DateKey 列が含まれています。これらのテーブルをインポートしようとした場合、最初のリレーションシップは正常に作成されますが、同じ列を使用する後続のリレーションシップに関して次のエラーが出力されます。
* リレーションシップ: table[column 1]-> table[column 2] - 状態: エラー - 理由: <table 1> と <table 2> の間のリレーションシップを作成できません。2 つのテーブル間に作成できる直接的または間接的なリレーションシップは 1 つだけです。
2 つのテーブルの間に複数のリレーションシップが存在している場合は、参照列を含むテーブルのコピーを複数インポートし、テーブルの各ペアの間にリレーションシップを 1 つ作成します。
ソース列ごとに 1 つのリレーションシップ
ソース列は、複数のリレーションシップに参加できません。既に 1 つのリレーションシップである列をソース列として使用している場合、別のテーブルの別の関連参照列に関連付けるためにその列を使用するには、その列のコピーを作成して新しいリレーションシップに使用します。
計算列で DAX 数式を使用すると、値がまったく同じ列のコピーを簡単に作成できます。詳細については、「計算列の作成」を参照してください。
テーブルごとに一意の識別子
各テーブルには、各行を一意に識別する列を 1 つ含める必要があります。この列は、通常、主キーと呼ばれます。
一意の参照列
参照列のデータ値は一意である必要があります。つまり、参照列に重複値を格納することはできません。PowerPivot for Excel の場合、null と空の文字列は、明確なデータ値である空白と同じものとして扱われます。つまり、参照列には複数の null を格納できません。
互換性のあるデータ型
ソース列と参照列のデータ型には互換性がある必要があります。データ型の詳細については、「PowerPivot ブックでサポートされるデータ型」を参照してください。
複合キーと参照列
PowerPivot ブックでは複合キーを使用できません。テーブルの各行を一意に識別する列は常に 1 つである必要があります。複合キーに基づく既存のリレーションシップを持つテーブルをインポートしようとしても、PowerPivot ではこのリレーションシップを作成できないため、テーブルのインポート ウィザードではこのリレーションシップが無視されます。
PowerPivot で 2 つのテーブル間にリレーションシップを作成するときに、主キーと外部キーを定義する列が複数ある場合は、リレーションシップを作成する前に、それらの列の値を組み合わせて単一キー列を作成する必要があります。これはデータをインポートする前に行うことができます。また、PowerPivot で計算列を作成することによっても行うことができます。
多対多リレーションシップ
PowerPivot for Excel は多対多リレーションシップをサポートしていないため、PowerPivot 内に交差テーブルを単純に追加することはできません。ただし、DAX 関数を使用して、多対多リレーションシップをモデル化することができます。
自己結合とループ
PowerPivot のテーブルでは、自己結合は許可されません。自己結合は、テーブルとそれ自体との間の再帰的なリレーションシップです。自己結合は、親子階層を定義するためによく使用されます。たとえば、Employees テーブルの自己結合を行うと、業務の管理チェーンを示す階層を生成できます。
PowerPivot for Excel では、ブック内のリレーションシップの間にループを作成することはできません。つまり、以下に示すリレーションシップのセットは禁止されています。
テーブル 1 の列 a から テーブル 2 の列 f
テーブル 2 の列 f から テーブル 3 の列 n
テーブル 3 の列 n から テーブル 1 の列 a
結果的にループが作成されるリレーションシップを作成しようとすると、エラーが生成されます。
リレーションシップの自動検出と自動推定
データを PowerPivot ウィンドウにインポートすると、テーブルのインポート ウィザードはテーブル間の既存のリレーションシップを自動的にすべて検出します。また、ピボットテーブルを作成すると、PowerPivot for Excel によってテーブル内のデータが分析されます。これにより、定義されていない潜在的なリレーションシップが検出され、そのリレーションシップに含める適切な列が提示されます。
検出アルゴリズムでは、列の値およびメタデータに関する統計データを使用して、リレーションシップの確率を推定します。
関連するすべての列のデータ型には互換性がある必要があります。自動検出では、整数型とテキスト データ型のみがサポートされます。データ型の詳細については、「PowerPivot ブックでサポートされるデータ型」を参照してください。
リレーションシップが正常に検出されるようにするには、参照列内の一意のキーの数が、"多" の側のテーブル内の値より大きくなるようにする必要があります。つまり、リレーションシップの "多" の側のキー列には、参照テーブルのキー列にない値を含めることができません。たとえば、ID が指定された製品を一覧表示するテーブル (参照テーブル) と、製品ごとの売上を一覧表示する sales テーブル (リレーションシップの "多" の側) があるとします。売上レコードに製品の ID が含まれていても、対応する ID が Product テーブルに含まれていない場合、リレーションシップは自動的に作成されません。ただし、手動で作成できる場合があります。PowerPivot for Excel によってリレーションシップが検出されるようにするには、まず、不足している製品の ID で Product 参照テーブルを更新する必要があります。
"多" の側のキー列の名前は、参照テーブルのキー列の名前と類似するようにします。名前をまったく同じにする必要はありません。たとえば、ビジネスの場面では、Emp ID、EmployeeID、Employee ID、EMP_ID などの名前の列に本質的に同じデータを格納していることがあります。このアルゴリズムは類似した名前を検出し、類似した名前または完全に一致する名前を持つ列に高い確率を割り当てます。したがって、インポートするデータの列名を、既存のテーブル内の列に類似した名前に変更することによって、リレーションシップが作成される確率を高めることができます。PowerPivot for Excel によって可能なリレーションシップが複数検出される場合は、リレーションシップが作成されません。
以下の情報は、一部のリレーションシップが検出されない理由や、フィールド名、データ型などのメタデータを変更することでリレーションシップの自動検出結果がどのように改善されるかを理解するうえで役立ちます。詳細については、「リレーションシップのトラブルシューティング」を参照してください。
名前付きセットの自動検出
ピボットテーブル内の名前付きセットと関連フィールドの間のリレーションシップは自動的に検出されません。これらのリレーションシップは手動で作成できます。自動リレーションシップ検出を使用するには、各名前付きセットを削除し、各フィールドを名前付きセットからピボットテーブルに直接追加します。
リレーションシップの推定
テーブル間のリレーションシップは、自動的に連結される場合もあります。たとえば、次に示す最初の 2 セットのテーブルの間にリレーションシップを作成すると、他の 2 つのテーブルとの間にリレーションシップが存在すると推定され、自動的にリレーションシップが確立されます。
Products と Category: 手動で作成
Category と SubCategory: 手動で作成
Products と SubCategory: リレーションシップの推定
リレーションシップを自動的に連結するには、上記のように、リレーションシップを一方向にする必要があります。たとえば最初に、Sales と Products および Sales と Customers の間にリレーションシップが存在している場合は、リレーションシップは推定されません。これは、Products と Customers の間のリレーションシップが多対多リレーションシップであるためです。