チュートリアル: 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 データなど、Excel のワークシートには表示されていない場合でも、 S_Teams、W_Teams、 および スポーツがあります。
   
   

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

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

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

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

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

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

4. すべての列を調べると、Hosts には一意の値を持つ列がないことがわかります。 そのため、計算列と Data Analysis Expressions (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つは、タイトルの Add 列を含む空の列を選択することです。
   
   
   

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

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

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

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

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

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

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

   = YEAR([Edition])
   

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

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

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

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

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

注多くの値が Medals table [editionid] フィールドに繰り返し表示されます。 オリンピックの各バージョン ([Editionid] 値で表される) の間に、多くの medals が授与されたため、これは問題ありません。 Medalsテーブルでは、それぞれがそれぞれ獲得されたメダルになります。 Medalsテーブルの各レコードの一意の識別子と、指定された主キーは、[種目フィールドです。

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

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

では、作成した計算列を使って、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、市区町村、[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