チュートリアル: Excel、PowerPivot、DAX を使ってデータ モデル リレーションシップを拡張する

このチュートリアルのセクションは次のとおりです。

• Power Pivot のダイアグラム ビューを使ってリレーションシップを追加する

• 計算列を使ってデータ モデルを拡張する

• 階層を作成する

• ピボットテーブルで階層を使用する

• チェックポイントと確認テスト

このチュートリアルの最後にある確認テストで、学習したことを確認できます。

このシリーズでは、オリンピックのメダル、ホスト国、さまざまなオリンピック競技種目について説明するデータを使います。 このシリーズのチュートリアルの内容は,次のとおりです。

1. Excel にデータをインポートし、データ モデルを作成する

2. Excel を使用してデータ モデルリレーションシップを拡張する、 PowerPivot 、DAX

3. マップベースの Power View レポートを作成する

4. インターネット データを取り込んで Power View レポートの既定値を設定する

5. PowerPivot のヘルプ

6. 効果的な Power View レポートを作成する - パート 2

チュートリアルは順番に進めることをお勧めします。

これらのチュートリアルでは、PowerPivot が有効な Excel 2013 を使用します。 Excel 2013 の詳細については、 こちらをクリックしてください。 PowerPivot の有効化のガイダンスについては、ここをクリックしてください。

PowerPivot のダイアグラム ビューを使ってリレーションシップを追加する

このセクションでは、Microsoft Office PowerPivot in Excel 2013 アドインを使ってモデルを拡張します。 Microsoft SQL Server Power Pivot for Excel のダイアグラム ビューを使うと、簡単にリレーションシップを作成できます。 最初に、PowerPivot アドインが有効になっていることを確認する必要があります。

注: Power Pivot in Microsoft Excel 2013 アドインは、Office Professional Plusの一部です。 詳細については、「Power Pivot in Microsoft Excel 2013 アドインを開始する」を参照してください。

PowerPivot アドインを有効にして Excel リボンに PowerPivot を追加する

PowerPivot が有効になると、Excel 2013 に [POWERPIVOT] というリボン タブが表示されます。 PowerPivot を有効にするには、次の手順を実行します。

1. [ファイル]、[オプション]、[アドイン] の順にクリックします。

2. 下のほうの [管理] ボックスで、[COM アドイン]、[実行] の順にクリックします。

3. [Microsoft Office Power Pivot in Microsoft Excel 2013] チェック ボックスをオンにして、[OK] をクリックします。

これで、リボンに [POWERPIVOT] タブが追加されました。

PowerPivot のダイアグラム ビューを使ってリレーションシップを追加する

Excel ブックに、Hosts という名前のテーブルが含まれています。 これは、Hosts のデータをコピーして Excel に貼り付けてインポートし、テーブルとして書式設定したものです。 データ モデルに Hosts テーブルを追加するには、リレーションシップを確立する必要があります。 PowerPivot を使用して、データ モデル内のリレーションシップを視覚的に表し、リレーションシップを作成してみましょう。

1. Excel の [Hosts] タブをクリックして、このシートをアクティブにします。

2. リボン上で、[POWERPIVOT]、[テーブル]、[データ モデルに追加] の順に選びます。 この手順により、Hosts テーブルがデータ モデルに追加されます。 また、PowerPivot アドインが開くので、このアドインを使って残りの手順を実行します。

3. [ PowerPivot ] ウィンドウには、モデル内のすべてのテーブル ( ホストを含む) が表示されます。 いくつかのテーブルをクリックしてください。 PowerPivot では、以下の規範、イベント、Medals データ、S_Teams、W_Teams、スポーツなど、Excel のどのワークシートにも表示されていない場合でも、モデルに含まれるすべてのデータを表示できます。
   
   

4. [PowerPivot] ウィンドウの [表示] セクションの [ダイアグラム ビュー] をクリックします。

5. ダイアグラム内のすべてのオブジェクトを表示できるように、スライド バーを使ってダイアグラムのサイズを変えます。 タイトル バーをドラッグしてテーブルを並べ替え、互いに表示され、横に配置されるようにします。 4 つのテーブルは、残りのテーブル ( ホスト、 イベント、 W_Teams、 S_Teams とは無関係であることに注意してください。
   
   

6. Medals テーブルと Events テーブルの両方に、DisciplineEvent というフィールドがあります。 さらに注意して見てみると、Events テーブルの DisciplineEvent フィールドは、一意で繰り返されない値によって構成されます。

1. Medals テーブルと Events テーブルの間にリレーションシップを作成します。 ダイアグラム ビューで、Events テーブルの DisciplineEvent フィールドから Medals の DisciplineEvent フィールドにドラッグします。 それらの間に線が表示され、リレーションシップが確立されたことを示します。

2. Events と Medals を接続する線をクリックします。 フィールドが強調表示されてリレーションシップが定義されます (次の図を参照してください)。
   
   
   

3. ホストをデータ モデルに接続するには、Hosts テーブル内の各行を一意に識別する値を持つフィールドが必要です。 次に、データ モデルを検索して、同じデータが別のテーブルに存在するかどうかを確認できます。 ダイアグラム ビューを見ると、これを行うことはできません。 [ホスト] が選択されている状態で、[データ ビュー] に戻ります。

4. 列を調べた後、 ホスト には一意の値の列がないことを認識します。 計算列とデータ分析式 (DAX) を使用して作成する必要があります。

データ モデルのデータに、リレーションシップの作成に必要なすべてのフィールドがあり、データをマッシュアップして Power View またはピボットテーブルで視覚化する場合に便利です。 ただし、テーブルは常にそれほど協調的であるとは限らないので、次のセクションでは、テーブル間のリレーションシップを作成するために使用できる DAX を使用して、新しい列を作成する方法について説明します。

計算列を使ってデータ モデルを拡張する

Hosts テーブルとデータ モデルの間のリレーションシップを確立し、それによってデータ モデルを拡張して Hosts テーブルが含まれるようにするには、Hosts にはそれぞれの行を一意に識別するフィールドが必要です。 さらに、そのフィールドはデータ モデルのフィールドに対応する必要があります。 これらの対応するフィールド (各テーブルに 1 つ) は、テーブルのデータを関連付けることができます。

Hosts テーブルにはこのようなフィールドがないため、作成する必要があります。 データ モデルの整合性を維持するために、 PowerPivot を使用して既存のデータを編集または削除することはできません。 ただし、既存のデータに基づく計算フィールドを使って、新しい列を作成できます。

Hosts テーブルを調べて、データ モデルの他のテーブルを調べると、一意のフィールドとして作成して、データ モデルのテーブルと関連付けする良い候補が Hosts にあることがわかります。 リレーションシップを確立するための要件を満たすためには、両方のテーブルに新しい計算列が必要になります。

Hosts では、Edition フィールド (オリンピック イベントが開催された年) と Season フィールド (Summer または Winter) を組み合わせた計算列を作成できます。 Medals テーブルにも Edition フィールドと Season フィールドがあるので、それぞれのテーブルで Edition フィールドと Season フィールドを組み合わせた計算列を作成すると、Hosts と Medals の間にリレーションシップを確立できます。 次の図では、Hosts テーブルで Edition フィールドと Season フィールドが選ばれています。

DAX を使って計算列を作成する

Hosts テーブルから始めましょう。 目標は、Hosts テーブルに計算列を作成し、次に Medals テーブルにも作成し、それらの間のリレーションシップを確立できるようにすることです。

PowerPivot では、Data Analysis Expressions (DAX) を使って集計を作成できます。 DAX は PowerPivot とピボットテーブルのための数式言語で、PowerPivot でリレーショナル データやコンテキスト分析を使用できるように設計されています。 新しい PowerPivot 列、および PowerPivot の [計算] 領域に DAX 数式を作成できます。

1. PowerPivot で、[ホーム]、[表示]、[データ ビュー] の順に選び、ダイアグラム ビューではなくデータ ビューが選択されていることを確認します。

2. PowerPivotで Hosts テーブルを選択します。 既存の列の隣には、列の追加というタイトルの空の 列があります。 PowerPivot は、その列をプレースホルダーとして提供します。 PowerPivotのテーブルに新しい列を追加するには、多くの方法があります。そのうちの 1 つは、タイトルの [列の追加] を持つ空の列を選択することです。
   
   
   

3. 数式バーに、次の DAX 数式を入力します。 CONCATENATE 関数は、2 つ以上のフィールドを 1 つに連結します。 入力を始めると、オートコンプリート機能によって、列やテーブルの完全修飾名を簡単に入力できるほか、使用できる関数が一覧表示されます。 オートコンプリートの候補から選ぶときは、タブを使用します。 また、数式の入力中に列をクリックするだけで、PowerPivot によって数式に列名が設定されます。
   
   =CONCATENATE([Edition],[Season])

4. 数式の作成が終了したら、Enter キーを押してその式を確定します。

5. 集計列内のすべての行に値が入力されます。 テーブルを下にスクロールすると、各行が一意であることがわかります。そのため、 Hosts テーブル内の各行を一意に識別するフィールドが正常に作成されました。 このようなフィールドは主キーと呼ばれます。

6. 計算列の名前を EditionID に変更しましょう。 任意の列をダブルクリックするか、列を右クリックして [列名の変更] を選ぶと、名前を変更できます。 完了すると、PowerPoint の Hosts テーブルは次の図のように表示されます。
   
   

Hosts テーブルの準備はできました。 次に、Hosts で作成した EditionID 列の形式に一致する計算列を Medals に作成して、それらの間にリレーションシップを作成できるようにします。

1. まず、ホストの場合と同様に、 Medals テーブルに新しい列を作成 します。 [ PowerPivotMedals テーブルを選択し、[ デザイン] > [列] > [追加] をクリックします。 [列の追加] が選択されていることに注意してください。 これは、 単に [列の追加] を選択する場合と同じ効果があります。

2. Medals の Edition 列の形式は、Hosts の Edition 列の形式とは異なります。 Edition 列と Season 列を組み合わせるまたは連結して EditionID 列を作成する前に、Edition を正しい形式に変換する中間的なフィールドを作成する必要があります。 テーブルの上の数式バー内で、次の DAX 数式を入力します。

   = YEAR([Edition])
   

3. 数式の作成が終了したら、Enter キーを押します。 入力した数式に基づいて、計算列のすべての行に値が入力されます。 この列を Hosts の Edition 列と比較すると、これらの列の形式が同じことがわかります。

4. [CalculatedColumn1] を右クリックし、[列名の変更] を選んで、列の名前を変更します。 「Year」と入力し、Enter キーを押します。

5. 新しい列を作成すると、PowerPivot によって [列の追加] というプレースホルダー列が別に追加されます。 次に、EditionID という計算列を作成するために、[列の追加] を選びます。 数式バーに次の DAX 数式を入力して、Enter キーを押します。
   
   =CONCATENATE([Year],[Season])

6. [CalculatedColumn1] 列の名前を変更するためにダブルクリックして、「EditionID」と入力します。

7. 列を昇順に並べ替えます。 これで、PowerPivot の Medals テーブルは次の図のようになります。
   
   
   

Medals テーブルの EditionID フィールドで多くの値が繰り返されていることに注意してください。 オリンピックの各エディション (現在は EditionID 値で表される) の間に多くのメダルが授与されたので、それは大丈夫で期待されています。 メダルテーブルでユニークなのは、各メダルが授与されていることです。 Medals テーブル内の各レコードの一意識別子とその指定された主キーは、MedalKey フィールドです。

次は、Hosts と Medals の間にリレーションシップを作成する手順です。

計算列を使ってリレーションシップを作成する

次に、作成した計算列を使用して 、ホスト と メダルの関係を確立しましょう。

1. [PowerPivot] ウィンドウで、リボンの [ホーム]、[表示]、[ダイアグラム ビュー] を選びます。 または、[PowerView] ウィンドウの下にあるボタンで、グリッド ビューとダイアグラム ビューを切り替えます (次の図を参照してください)。
   
   

2. Hosts を展開して、すべてのフィールドを表示します。 ここまで、Hosts テーブルの主キー (一意で値が重複しないフィールド) になる EditionID 列を作成し、Medals テーブルの EditionID を作成して、それらの間にリレーションシップを作成する準備をしました。 その両方を見つけて、リレーションシップを作成する必要があります。 PowerPivot のリボンには [検索] 機能があって、対応するフィールドをデータ モデルで検索できます。 次の図では、[メタデータの検索] ウィンドウの [検索データ] フィールドに「EditionID」と入力されています。
   

3. Hosts テーブルが Medals の隣に来るように配置します。

4. Medals の [EditionID] 列を Hosts の [EditionID] 列にドラッグします。 PowerPivot によって、2 つのテーブルの間に EditionID 列に基づくリレーションシップが作成され、2 つの列の間にリレーションシップがあることを示す線が表示されます。
   
   

このセクションでは、新しい列を作成する新しい手法を学習し、DAX を使って計算列を作成し、その列を使ってテーブル間に新しいリレーションシップを作成しました。 Hosts テーブルはデータ モデルに統合され、そのデータが Sheet1 のピボットテーブルで使用できるようになっています。 また、関連するデータを使って、さらにピボットテーブル、ピボットグラフ、Power View レポート、など多くのものを作成できます。

階層を作成する

ほとんどのデータ モデルには、階層的な性質を持つデータが含まれています。 カレンダー データ、地理データ、製品カテゴリなどがよくある例です。 PowerPivot 内で階層を作成すると、同じフィールドを何度も組み合わせ、順序付けるのではなく、1 つのアイテムをレポート (階層) にドラッグするだけでよいので便利です。

オリンピックのデータも、階層構造になっています。 スポーツ、規範、イベントの観点から、オリンピックの階層を理解しておくと役立ちます。 それぞれの競技に対して、1 つまたは複数の競技団体が関連します (多数の場合もあります)。 それぞれの競技団体に対して、1 つまたは複数の種目があります (この場合も多数の種目がある場合があります)。 次の図に、この階層の例を示します。

このセクションでは、このチュートリアルで使用しているオリンピック データ内に 2 つの階層を作成します。 次に、これらの階層を使用して、ピボットテーブルと後続のチュートリアルの Power View で、階層によってデータの整理が容易になる方法を確認します。

Sport 階層を作成する

1. PowerPivot で、ダイアグラム ビューに切り替えます。 Events テーブルを展開して、すべてのフィールドを一目で確認できるようにします。

2. Ctrl キーを押しながら、[Sport]、[Discipline]、[Event] の各フィールドをクリックします。 3 つのフィールドを選んだまま、右クリックして [階層の作成] を選びます。 親階層ノードである Hierarchy 1 がテーブルの一番下に作成され、選んだ列がこの階層の下に子ノードとしてコピーされます。 Sport が階層の最初に表示され、次に Discipline そして Event が表示されることを確認します。

3. タイトルの Hierarchy1 をダブルクリックして、「SDE」と入力して新しい階層の名前を変更します。 これで、Sport、Discipline、Event が含まれた階層ができました。 Events テーブルは次の図のように表示されます。
   
   

Locations 階層を作成する

1. PowerPivot のダイアグラム ビューのまま [Hosts] テーブルを選んで、テーブルの見出し行の [階層の作成] ボタンをクリックします (次の図を参照してください)。
   
   
   テーブルの下部に空の階層親ノードが表示されます。

2. 新しい階層の名前として「Locations」と入力します。

3. 階層に列を追加する方法は、次のようにいくつかあります。 [Season]、[City]、[NOC_CountryRegion] の各フィールドを階層名 (この場合は Locations) にドラッグして、階層名が強調表示されてからマウスのボタンを離すと、追加できます。

4. [EditionID] を右クリックし、[階層に追加] を選びます。 [Locations] を選びます。

5. 階層の子ノードの順序が正しいことを確認します。 上から下の順序は、シーズン、NOC、City、EditionID です。 子ノードの順序が正しくない場合は、適切な順序になるように階層内でドラッグして並べ替えます。 Hosts テーブルは、次の図のように表示されます。
   

これで、データ モデルに、レポートで使用できる階層が作成されました。 次のセクションでは、これらの階層によってレポート作成がより迅速で一貫した操作になることを学習します。

ピボットテーブルで階層を使用する

Sport の階層と Locations 階層を作成してあるので、これらの階層をピボットテーブルまたは Power View を追加すると、結果 (データのグループ化など) はすぐに得られます。 階層を作成する前に、ピボットテーブルに個別のフィールドを追加して、意図した表示になるようそれらのフィールドを配置する必要があります。

このセクションでは、前のセクションで作成した階層を使って、ピボットテーブルをすばやく仕上げることにします。 次に、階層の個別のフィールドを使って同じピボットテーブル ビューを作成し、階層を使ったものと個別のフィールドを使ったものを比較します。

1. Excel に戻ります。

2. Sheet1 で、[ピボットテーブル フィールド] の [行] 領域からフィールドを削除し、[列] 領域からもすべてのフィールドを削除します。 ピボットテーブルが選択されていることを確認します (非常に小さくなっているので、セル A1 を選んでピボットテーブルが選択されていることを確認します)。 ピボットテーブルに残っているフィールドは、[フィルター] 領域の Medal と、[値] 領域の Count of Medal だけになっています。 このほとんど空のピボットテーブルは、次の図のように表示されます。

   

3. [ピボットテーブル フィールド] 領域で、Events テーブルの [SDE] を [行] 領域にドラッグします。 次に、Hosts テーブルの [Locations] を [列] 領域にドラッグします。 これら 2 つの階層をドラッグするだけで、ピボットテーブルには多くのデータが入力され、そのすべてが前の手順で定義した階層に配置されます。 画面は次のように表示されます。
   

4. そのデータを少しフィルター処理し、イベントの最初の 10 行だけを表示してみましょう。 ピボットテーブルで [行ラベル] の矢印をクリックし、[(すべて選択)] をクリックしてすべての選択を解除し、最初の 10 件の Sport の横のボックスをクリックします。 ピボットテーブルは、次のように表示されます。
   

5. ピボットテーブルでこれらの Sport (SDE 階層の最上位) のいずれかを展開すると、階層の次のレベル (Discipline) の情報を確認できます。 その Discipline に対して下位レベルの階層が存在する場合は、Discipline を展開するとそれに対する Event が表示されます。 Location 階層でも同様で、最上位は Season でピボットテーブルでは Summer または Winter と表示されます。 Sport の Aquatics を展開すると、すべての子 Discipline 要素とそのデータが表示されます。 Aquatics の下の Diving という Discipline を展開すると、その子 Event が表示されます (次の図を参照してください)。 Water Polo でも同様ですが、これに対する Event は 1 つだけです。
   

これら 2 つの階層をドラッグしてピボットテーブルをすばやく作成し、興味深い構造化されたデータの詳細を表示したり、フィルター処理したり、配置したりしました。

次に、階層の利点を得ずに、同じピボットテーブルを作成しましょう。

1. [ピボットテーブル フィールド] 領域で、[列] 領域から [Locations] を削除します。 次に、[行] 領域から [SDE] を削除します。 基本的なピボットテーブルに戻ります。

2. Hosts テーブルから [Season]、[City]、[NOC_CountryRegion]、[EditionID] を [列] 領域にドラッグし、この順番でそのまま (上から下に) 配置します。

3. Events テーブルから、[Sport]、[Discipline]、[Event] を [行] 領域にドラッグし、この順番でそのまま (上から下に) 配置します。

4. ピボットテーブルで、[Row Labels] をフィルター処理して上位 10 件の Sport を表示します。

5. すべての行と列を折りたたんで、Aquatics、Diving、Water Polo の順に展開します。 ワークブックは次のように表示されます。
   

画面は似ていますが、単に 2 つの階層をドラッグするのではなく、7 つの個々の フィールドをピボットテーブルのフィールド 領域にドラッグした点が異なります。 このデータに基づいてピボットテーブルまたは Power View レポートを作成する唯一のユーザーの場合は、階層の作成だけが便利に見える場合があります。 しかし、多くのユーザーがレポートを作成していて、ビューを正しく取得するためにフィールドの適切な順序を把握する必要がある場合、階層はすぐに生産性の向上になり、一貫性が有効になります。

この次のチュートリアルでは、Power View を使って作成したアピール力のあるレポートで、階層とその他のフィールドを使用する方法を学習します。

チェックポイントと確認テスト

これまでの内容を復習する

この時点で、Excel ブックのデータ モデルには、さまざまなソースからのデータが含まれ、既存のフィールドや計算列を使用したりレーションがあります。 また、テーブル内のデータ構造を反映した階層もあり、それによって優れたレポートをいつも同じように、すばやく、簡単に作成できます。

階層を作成することで、データ内に元からある構造を指定して、階層データを迅速にレポートで使用できるということを学習しました。

このシリーズの次のチュートリアルでは、Power View を使って、オリンピックのメダルに関するインパクトのあるレポートを作成します。 また、他の集計や、迅速なレポート作成のためのデータの最適化、他のデータをインポートするなどしてさらに興味深いレポートを作成します。 リンクを次に示します。

チュートリアル 3: マップベースの Power View レポートの作成

確認テスト

学習内容についてどれくらい理解できましたか? チャンスは次のとおりです。 次のクイズでは、このチュートリアルで学習した機能や要件について質問します。 ページの下部に回答が表示されます。 それでは挑戦してください。

質問 1: 次のビューのうち、2 つのテーブル間にリレーションシップを作成できるビューはどれですか?

A: テーブル間のリレーションシップは Power View で作成する。

B: テーブル間のリレーションシップは PowerPivot のデザイン ビューで作成する。

C: テーブル間のリレーションシップは のグリッド ビューで作成する PowerPivot

D: 上記すべて。

<c0>質問 2:</c0> 正誤問題です。 DAX 数式を使って作成した一意の識別子に基づいて、テーブル間のリレーションシップを確立できる。

A: 正しい

B: 正しくない

質問 3: DAX 数式を作成できる場所は、次のうちのどれですか。

A: PowerPivot の [計算] 領域

B: PowerPivot の新しい列

C: Excel 2013 の任意のセル

D: A と B の両方

質問 4: 階層に当てはまるのは次のうちどれですか?

A: 階層を作成すると、含まれているフィールドは個別には使用できなくなる。

B: 階層を作成すると、その階層と含まれているフィールドは、Power View またはピボットテーブルの領域にドラッグするだけでクライアント ツールで使用できるようになる。

C: 階層を作成すると、データ モデル内の元になっているデータは結合されて 1 つのフィールドになる。

D: PowerPivot では階層を作成できない。

クイズの正解

1. 正答 D

2. 正しい答え: A

3. 正答 D

4. 正答 B