PowerPivot for Excel
PowerPivot for Excel は、SQL Server 2008 R2 から提供されたデータ分析ツールです。
PowerPivot for Excel では、多くの新機能が提供されました。その主なものは、以下のとおり です。
KPI(Key Performance Indicator:重要業績評価指標)への対応
書式(Format)設定の永続化により、書式がピボットテーブルへ反映可能に
並べ替え列のサポートにより、別の列で並べ替えが可能に
ダイアグラム ビューによるグラフィカルなリレーションシップ管理のサポート
階層のサポートにより、階層関係のあるフィールド(たとえば、大分類→中分類→小分類
→商品のような関係)をワンクリックで配置可能に
フィールド一覧に表示されるフィールドの表示順序の変更が可能に
パースペクティブのサポートにより、不要な列/メジャーを簡単に非表示に可能
ドリルスルーのサポート
BLOB データ(varbinary(max))のサポート
DAX(Data Analysis Expressions)関数の大幅強化。PATH や PATHITEM 関数によ って親子階層への対応も可能
以下の画面は、ダイアグラム ビューでリレーションシップを設定しているときの様子です。
階層の作成 が可能 リレーションシップを グラフィカルに編集可能
KPI や 計算メジャー
並べ替え列も 設定可能
88
以下の画面は、KPI をグラフィカルに設定しているときの様子です。
このように、SQL Server 2012 では PowerPivot がバージョン アップして、さらに本格的なデ ータ分析レポートが簡単に作成できるようになりました。
また、前述の Analysis Services テーブル モデル(Tabular Model)を利用した場合には、「ロ ールを利用した行レベル セキュリティ」や「パーティショニング」も利用できるようになるので、
セキュリティの強化やビッグデータ対応、性能向上を実現することが可能になりました。
Let's Try: KPI の設定
それでは、実際に KPI(Key Performance Indicator:重要業績評価指標)機能を試してみまし ょう。ここでは、次のように、目標金額に対して、受注金額がどれだけ達成できたのかをグラフィ カルなアイコンで表示してみましょう。
1. まずは、サンプル スクリプトの「NorthJ.xlsx」ファイルを Excel 2010 で開きます。
2. 次に、サンプル スクリプトの「TargetVal.xlsx」ファイルを Excel 2010 で開きます。こ
KPIをグラフィカ ルに設定
KPIを利用して、目標に対する 達成の度合いをグラフィカルな
アイコンで表示
89
のファイルには、次のように区分ごとの売上の目標金額が格納されています。
データを確認したら、シート名(Sheet1)を右クリックして、[移動またはコピー]をクリ ックします。[シートの移動またはコピー]ダイアログが表示されたら、[移動先ブック名]で
「NorthJ.xlsx」を選択、[挿入先]で「(末尾へ移動)」を選択、[コピーを作成する]をチェ ックして、[OK]ボタンをクリックします。
これでこのシートを丸ごと「NorthJ.xlsx」ファイルへコピーできます。
3. コピーが完了したら、「NorthJ.xlsx」ファイルへ移動して、コピーしたシートを開きます。
区分コードから目標金額までのデータを上画面のようにドラッグして選択し、リボンの
[PowerPivot]タブから「リンク テーブルの作成」をクリックします。
4. これにより、PowerPivot ウィンドウが表示されて、リンク テーブルが作成されていること を確認できます。
1 2
3
4 5
4
2006年の 区分ごとの 目標金額
1 選択
2 3
90
リンク テーブルの名前を「目標金額」など分かりやすい名前へ変更して、「目標金額」列をク リックし、リボンの[Σ オート SUM]メニューの「Σ 合計」をクリックします。
5. 次のように目標金額の合計が作成されたら、名前を「目標金額計」へ変更します。
6. 次に、「区分コード」列を右クリックして、[リレーションシップの作成]をクリックします。
リンク テーブルが作成 される。テーブル名を
「目標金額」へ変更する
1
「目標金額」列 をクリック
2 3
合計が追加 される
1
「目標金額計」
に変更する
2
91
[リレーションシップの作成]ダイアログでは、[関連する参照テーブル]で「商品区分」、[関 連する参照列]で「区分コード」を選択して、[作成]ボタンをクリックします。これで、商 品区分テーブルの区分コード列と、目標金額の区分コード列を関連付けることができます。
7. 次に、「受注金額」の合計値を追加します。次のように「受注金額」列内の任意の行をクリッ クして、[Σ オート SUM]メニューの[Σ 合計]をクリックします。
これによって、「受注金額」列の下部ウィンドウに受注金額の合計値が追加されます。名前は
「受注金額の合計 2」へ設定されていますが、これは自動的に内部作成されている受注金額の 合計(前述の Note で紹介した「通貨」データ型に対して自動作成される合計値)に対して
「受注金額の合計」という名前が設定されているためです。
8. 次に、追加された「受注金額の合計 2」を選択します。
1
2
3
1
2 3
4
92
選択後、リボンの[KPI の作成]ボタンをクリックします。これで、KPI を作成できるよう になります(KPI を作成するには、「受注金額の合計 2」のように[Σ オート SUM]メニュ ーから手動作成した集計値が必要になります)。
9. [主要業績評価指標(KPI)]ダイアログが表示されたら、次のように[対象の値の定義]で
[メジャー]に「目標金額計」を選択します。
[対象のしきい値の定義]では、「80%」と「100%」へ設定して、目標金額に対して、80%
未満なら「赤」、80~100%の間なら「黄色」、100%以上なら「緑」で状態を表示するように 設定します。以上で、KPI の設定が完了です。
10. PowerPivot ウィンドウへ戻ったら、リボンの[ピボット テーブル]メニューから[ピボッ 1
2
1
3 2
任意のアイコン スタイルを選択
4
93
ト テーブル]をクリックして、新しいピボット テーブルを作成します。
[ピボット テーブルの作成]ダイアログでは、[新しいワークシート]を選択して、[OK]ボ タンをクリックします。
11. ピボット テーブルには、次のように[行ラベル]へ「区分名」、[Σ値]へ「目標金額計」、[水 平スライサー]へ「年」を配置します。
今回の目標金額は、2006 年向けのものなので、水平スライサーに配置した「年」で「2006」
をクリックしておきます。
12. 次に、「受注明細」テーブルを展開して、[受注金額の合計2]を展開します。
2 1
3
3
1 2
4
2006年の 区分ごとの 目標金額を表示
94
KPI を設定すると、「値」、「状態」、「対象」が追加されるので、「値」と「状態」を[Σ値]へ 配置します。これで、受注金額の値と状態(目標金額に対する達成度合い)をピボット テー ブル内へ表示できるようになります。目標金額に対して、80%未満なら「赤」、80~100%の 間なら「黄色」、100%以上なら「緑」で表示されていることを確認できます。
なお、「対象」を[Σ値]へ配置した場合は、KPI では[対象の値の定義]で「目標金額」へ 設定しているので、目標金額がピボット テーブルへ表示されるようになります。
このように、KPI を利用すると、目標値に対する達成の度合いをグラフィカルなアイコンで 表示できるようになるので、大変便利です。
ダイアグラム ビューの表示、階層の作成
次に、SQL Server 2012 の PowerPivot からの新機能である「ダイアグラム ビュー」と「階層」
を試してみましょう。
1. まずは、「NorthJ.xlsx」ファイルを引き続き利用して、PowerPivot ウィンドウを開きます。
1
値と状態
目標金額より100%以上なら緑 80~100%未満なら黄色 80%未満なら赤で表示される
1
2
3
4
95
PowerPivot ウィンドウでは、リボンの[ピボット テーブル]メニューの[ピボット テーブ ル]をクリックして、新しいピボット テーブルを作成します。[ピボット テーブルの作成]
ダイアログでは、[新しいワークシート]を選択して、[OK]ボタンをクリックします。
2. ピボット テーブルへは、次のように[行ラベル]へ「区分名」、[列ラベル]へ「年」、[Σ 値]
へ「受注金額の合計」(受注金額をチェックすると、自動作成された受注金額の合計が追加さ れます)、を配置します。
3. 次に、[行ラベル]へ「月」を追加します。
年を展開すると、月ごとの受注金額が表示されることを確認できます。このように、年と月の ように一緒に利用する(階層関係があってドリルダウンして利用する)ことが多いフィールド に対しては、「階層」を作成しておくことで、それを配置するだけで両方参照できるようにな
3
1 2
区分ごと、年ごとの 受注金額を表示
1
年を展開すると 月ごとの受注金額
が表示される
2
96 るので大変便利です。
4. 階層を作成するには、まず次のようにリボンの[ダイアグラム ビュー]ボタンをクリックし て、ダイアグラム ビューを表示します。
ダイアグラム ビューは、SQL Server 2012 の PowerPivot for Excel から搭載された新機 能で、リレーションシップをグラフィカルに表示/設定することができます。
5. 次に、「受注」テーブルの「年」と「月」を Ctrl キーを押しながら選択し、次のように右ク リックして、[階層の作成]をクリックします。
階層の名前は、任意に設定(画面は年階層と入力)します。これで階層の作成が完了です。
6. 作成後は、リボンの[データ ビュー]ボタンをクリックして、データ ビュー(既定で表示さ れるテーブル形式のビュー)へ戻しておきます。
1
ダイアグラム ビュー が表示される
ズームの調整
リレーションシップ をグラフィカルに設
定可能
↓
1
2 3
97
7. 次に、Excel 側へ戻ると、[フィールドの一覧]ウィンドウにデータが変更された主旨の警告 が表示されているので、[最新の情報に更新]をクリックします。
8. これにより、「受注」テーブルの下に、作成した階層(年階層)が表示されるようになります。
9. 次に、[列ラベル]へ配置している「年」と「月」をそれぞれクリックして、次のように[フ ィールドの削除]で削除します。
10. [列ラベル]が空になったら、今度は階層(年階層)を[列ラベル]へ配置します。
1
1
1
1