セッション ID：T4-304

Microsoft BI を支える Reporting Services & PowerPivot ～ 松本美穂のすぐに使える BI システム構築の技 ～

有限会社エスキューエル・クオリティ 代表取締役／コンサルタント 松本 美穂

3

Speaker

自己紹介 有限会社エスキューエル・クオリティ 2005年に設立。http://www.sqlQuality.com

日本で唯一の SQL Server 専門のコンサルティング会社 人気メニューのパフォーマンス チューニング サービスは 100％ の成果 を上げ、過去すべてのお客様環境で驚異的な性能向上を実現していま す。 主なコンサルティング実績 大手流通系の大規模 DWH／BI システム構築支援 大手アミューズメント企業の BI システム設計支援 大手企業の Analysis Services による「財務諸表分析」システムの設計支援 外資系医療メーカーの Analysis Services による「販売分析」システムのチューニング 1秒あたり 1,000 Batch Request の ASP（アプリケーション サービス プロバイダー） サイトのパフォーマンス チューニング。ピーク時の CPU 利用率 100% を 10% まで軽減 大手インターネット通販システムの夜間バッチ実行時間を 5時間から 1時間半へ短縮 宅配便トラッキング情報の日中バッチ実行時間を 2時間から 5分へ短縮 Java 環境（Tomcat、Seasar2、S2Dao）の SQL Server パフォーマンス チューニング 高負荷テスト（ラッシュ／ストレス テスト）実施のためのテスト アプリの作成支援

4

自習書シリーズのご紹介 （弊社執筆）

全 29本、約 3,200 ページ！ SQL Server 2008 R2 からの新機能 PowerPivot for Excel 2010 セルフ サービス分析 セルフ サービス レポーティング（Report Builder 3.0） マルチ サーバー＆アプリケーション管理 MDS によるマスター データ管理 StreamInsight によるストリーム データの処理 SQL Azure 入門 お勧めコンテンツ SQL Server 2008 R2 対 応へ Update 済み Reporting Services 入門／応用／実践 Analysis Services 入門／応用 Integration Services 入門／応用 インデックスの基礎とメンテナンス etc

5

セッションの目的とゴール

PowerPivot ＆ Reporting Services 構築の技

セッションの目的

PowerPivot や Reporting Services を利用したBI シ ステム構築における、知っておいてほしい構築時の ポイント／考慮事項をご理解いただく

セッションのゴール

PowerPivot や Reporting Services を利用する上で のポイントを理解できるようになる PowerPivot や Reporting Services の基本的な操作 手順については、弊社執筆の自習書シリーズをぜ ひご覧ください！

6

アジェンダ

PowerPivot ＆ Reporting Services 構築の技 PowerPivot PowerPivot for Excel 2010 の特徴／内部動作 PowerPivot for SharePoint 2010 の特徴／内部動作 PowerPivot 利用時のポイント Reporting Services Reporting Services の特徴 SQL Server 2008 R2 からの新機能 共有データセット＆クエリ キャッシュ Reporting Services 利用時のポイント PivotViewer Extension for Reporting Services (CTP1)

PowerPivot とは

SQL Server 2008 R2 からの目玉の新機能！ PowerPivot for Excel 2010（クライアント側） ピボット テーブルの超進化版 使い慣れた Excel で高度なデータ分析が容易に可能 PowerPivot for SharePoint 2010（サーバー側） 共有、Web ベース、セキュアなデータ格納 for SharePoint 2010 for Excel 2010 ピボット テーブル ピボット グラフ スライサー 7 Web から 分析レポートを 参照可能

8

PowerPivot for Excel の特徴

イン メモリ アーキテクチャによる高速動作 サーバー側の作り込みが不要 通常の BI ソフトウェアは、サーバー側の作り込み必須 Excel だけで誰でも手軽にパワフルなデータ分析が可能 イン メモリ アーキテクチャで高速なデータ処理 Analysis Services VertiPaq エンジン 従来の Analysis Services エンジンを進化させたもの 64 ビット（x64）にも対応 32 ビット版と 64 ビット版の提供 Excel の上限 100万件を超えるデータにも対応 x64 環境でメモリを多く搭載していれば、 数億件のデータでも高速なデータ処理が可能 (ブックの最大サイズは 2GB まで)

PowerPivot for Excel の内部動作

Analysis Services VertiPaq エンジン イン メモリ アーキテクチャ（in-proc engine） PowerPivot ファイル （.xlsx） ユーザーの Temp フォルダー \ユーザー名\AppData \local\Temp フォルダー メモリ Excel 画面 集計演算 9 \xl\customData \item1.data ファイル PowerPivot データが 丸ごと圧縮されたファイル （Analysis Services のバック アップと同じ形式） 解凍 メモリへ そのまま展開 カラム（列）ごとに ファイルが作成される カラム ベース アー キテクチャ

データのインポート／データ更新は？

データ ソースへのクエリ実行＆丸ごと取得 データのインポート／データ更新時の動作 クエリ結果を .xlsx ファイル内へ丸ごとインポート クライアント PowerPivot for Excel データ更新時に 実行されるクエリ 1 データソース サーバーへの高負荷 大量データの JOIN など 処理 2 データベース 3 クエリ結果を丸ごとコピー テーブル データ ネットワークへの負荷 大量データの転送 1 3 10 PowerPivot for Excel 多数のユーザーが同時に データ更新を実行する可能性

11

構築／運用時のポイント

手軽さゆえの落とし穴 メモリ サイズに注意（見積りをしっかり！） メモリが足りなかった場合の動作が悲惨 ファイル サイズを小さくするポイント 不要な列（分析に利用しない列）を取得しないこと！ データ更新に注意 多数のユーザーが同時にデータ更新すると ...

なんでもかんでも自由に操作させるのか？ ユーザーにすべて見せるのか？ 見えすぎてもピボット テーブル初級者には操作しづらい 正規化されている業務 DB は、初級者には操作しづらい → ビューやデータ ウェアハウスの構築で回避 セキュリティは？

12

PowerPivot for SharePoint 2010

SharePoint Server 2010 との統合 主な特徴 分析レポートの「共有」と「セキュアなデータ格納」 バッチ更新（バック グラウンドでのデータ更新） Excel Services による Web ブラウザー ベースの共有 Silverlight ベースの PowerPivot ギャラリーの提供 管理ダッシュボードの提供 見栄えの良い PowerPivot ギャラリー

内部動作

PowerPivot for SharePoint 2010 ”新” Analysis Services VertiPaq モードの Analysis Services ＝ Analysis Services VertiPaq エンジン SharePoint コンテンツ DB SharePoint_Content_xx AllDocStreams テーブル PowerPivot ファイル（.xlsx） が バイナリ登録さ れたもの \xl\customData \item1.data

解凍 VertiPaq モードの Analysis Services Backup フォルダー Analysis Services を インストールしたフォルダー \Program Files \Microsoft SQL Server \MSAS10_50.POWERPIVOT

\OLAP\Backup \Sandboxes\ DefaultPowerPivotServiceApp~ メモリへ そのまま展開 メモリ msmdsrv.exe

集計演算 13 SharePoint Excel Services Excel Calculation Services が Web 形式へ変換 Excel Web Access Web ブラウザー

14

構築／運用時のポイント

PowerPivot for SharePoint 2010 メモリ サイズに注意！ メモリが足りなかった場合の動作が ･･･ メモリ サイズの見積もりをしっかりと！ データ更新をバッチ実行できる！ 設定手順は、次スライド参照 Excel Services の考慮事項 Excel Services 処理用 (Excel Calculation Services 用) の SharePoint サーバーの追加を検討 必要に応じて、複数台で負荷分散を実施

15

参考： 設定のポイント

PowerPivot for SharePoint 2010 データ更新（バッチ更新）の設定手順 PowerPivot ファイル（.xlsx）のドロップダウン メニューから ［データ更新の管理］をクリックして、［データ更新］を有効化 サーバーの全体管理 → アプリケーションの全般設定 → PowerPivot → サービス アプリケーションの設定の構成 から、データ更新の基本設定が行える PowerPivot ファイル（.xlsx ファイル）を作成する際に Excel 2010 のリボンの［データ］タブの［プロパティ］から ［ファイルを開くときにデータを更新する］をチェックする SharePoint 2010 のファイルの最大サイズを変更 Excel Services に対しての設定 サーバーの全体管理 → サービス アプリケーションの管理 → ExcelServiceApp1 → 信頼できるファイル保存場所から →［ブックの最大サイズ］を設定（～2GB まで設定可能） SharePoint Web アプリケーションに対しての設定 サーバーの全体管理 → Web アプリケーションの管理 → 全般設定 → ［アップロードの最大サイズ］

参考： 管理ダッシュボードの内部動作

PowerPivot for SharePoint 2010 SharePoint の利用状況収集機能を利用 PowerPivot for SharePoint 利用状況 ・接続ユーザー数 ・クエリ実行数 ・クエリ応答時間 ・サーバー状態 （CPU やメモリ） C:\Program Files\Common Files \Microsoft Shared\Web Server Extensions\14\LOGS フォルダー内 .log ファイル 2 1 逐次 SharePoint ログ DB WSS_ Logging SharePoint Foundation 利用状況データのインポート ジョブ（30分ごと） SharePoint Foundation 利用状況データの処理ジョブ （1日ごと 朝 1～3時） PowerPivot 管理ダッシュボー ド処理タイマー ジョブ （1日ご と 朝 3～5時） PowerPivot 状態統計コレクター タイマー ジョブが収集（15分ごと） 16 PowerPivot System サービスが監視 3 PowerPivot 用 DB DefaultPowerPivot ServiceApplicationDB 4 管理ダッシュボード用 の PowerPivot ファイ ル（.xlsx） 管理ダッシュボード

17

PowerPivot のまとめ

手軽でパワフルな BI システムが構築可能！ 構築のポイント メモリ！ メモリ！ メモリ！ 参考： 1TB（64 x 16GB DDR3 RDIMM）で 850万程度 512GB（64 x 8GB DDR3 RDIMM）で 350万程度 64GB（16 x 4GB DDR3 RDIMM）なら 40万程度 32GB（ 8 x 4GB DDR3 RDIMM）なら 20万程度 CPU パワーも重要！ 集計値の計算には、CPU パワーを利用 クロック数＆コア数が高いものを選択！ VertiPaq エンジンは、カラム（列データ）を圧縮して 格納しているので、解凍処理にも CPU パワーを利用

18

続き

パフォーマンスへの考慮 性能の良いデータ ウェアハウスの構築を検討 データ更新時の負荷への対応 SQL Server 2008 R2 Parallel Data Warehouse エディションへの期待 すべてをインタラクティブに見せる必要はない Reporting Services などを利用して、 静的なデータを作成しておくなど VertiPaq モードの Analysis Services は、 従来の Analysis Services と同様、 OLAP サーバーとして利用することも可能 = Reporting Services などのデータソースとして SharePoint 上の PowerPivot ファイルを指定可能

19

Reporting Services

Reporting Services の特徴 SQL Server 2008 R2 からの新機能 共有データセット＆クエリ キャッシュ Reporting Services 利用時のポイント PivotViewer Extension

Reporting Services の特徴

手軽に見栄えの良いレポートを簡単作成！ パレート図 (ABC 分析グラフ) Bing マップ 連携 自由なレイアウトで 配置 ゲージ ２Y軸 見栄えの良い 多彩なグラフ 20 データバー インジケーター スパークライン 帳票形式

21

SQL Server 2008 R2 からの新機能

さらなる進化！ 見栄えの部分 マップ（地図）機能の追加 Bing マップ連携、Spatial データ型対応 データバー、インジケーター、スパークラインの追加 共有レポート パーツ レポート内のパーツ（グラフや Tablix、ゲージ、マップ など）の共有が可能 更新検知機能もあり 共有データセットとクエリ キャッシュ データセットの共有が可能に クエリ実行結果のキャッシュも可能！ 「キャッシュの更新計画」機能によるキャッシュの ウォーム アップも可能に

共有データセット、クエリ キャッシュ

R2 からの新機能の中で最も役立つ！ 共有データセット レポート ビルダー 3.0 から作成可能 Business Intelligence Development Studio の場合は、既存の データセットから共有データセットへ変換可能 22 クエリ キャッシュ 共有データセットで定義したクエリ結果を キャッシュできる機能 レポート マネージャーから設定可能

23

キャッシュの効果の確認

ReportServer DB の ExecutionLog2 ビュー SELECT , ReportPath ,

DATEDIFF

( Parameters millisecond ,

TimeStart

,

TimeEnd

) AS 実行時間 , TimeDataRetrieval , TimeProcessing , TimeRendering , ByteCount FROM

ExecutionLog2

ORDER BY TimeStart DESC

24

構築のポイント

Reporting Services による BI システム 共有機能を活用する 共有データソース、共有データセット、共有レポート パーツを 利用して、レポート作成時間を短縮 キャッシュ機能を活用する レポート キャッシュ or クエリ キャッシュ キャッシュの更新計画機能でキャッシュをウォーム UP 定期配信機能（サブスクリプション）の検討 メールでの定期配信（Weekly レポート etc） 共有フォルダーへの定期配信 SharePoint 統合モードを利用する利点 Web パーツでポータルの作成ができる 後述の PivotViewer 機能を利用できる

25

PivotViewer Extension（CTP1）

PivotViewer Extension for Reporting Services Silverlight の PivotViewer コントロール http://www.microsoft.com/downloads/details.aspx?displaylang= en&FamilyID=d31f609d-a353-41ad-a1a4-f81456e3a6c4 動作に必要なもの SharePoint 統合モードで動作する Reporting Services PowerPivot for SharePoint 2010 へ配置した .xlsx

26

まとめ

PowerPivot ＆ Reporting Services 構築の技 使われない BI システムとならないために！ PowerPivot ＆ Reporting Services はスゴイ！ 手軽にパワフルな BI システムを構築できる キャパシティ プランニングが重要！ メモリ サイズの見積り、CPU 負荷の見積り etc 内部動作を理解することが重要！ キャッシュが最も重要！ キャッシュを活用できるかどうかが大きな分かれ道！ 今後の期待 （より魅力的な BI システムへの期待） PivotViewer Extension Parallel Data Warehouse エディション

ご清聴ありがとうございました。

T4-304

アンケートにご協力ください。

28

関連セッション

TH-201: SQL Server 2008 再入門 ～ ダイジェスト版 ～ TH-202: SQL Server 2008 R2 新機能ダイジェスト T4-302： SQL Server 2008 R2 Parallel Data Warehouse Edition の特徴 TH-305: Self Service BI 体験 ～ PowerPivot での分析 Step by Step ～ TH-306: レポート作成体験 ～ Reporting Services と Report Builder 3.0 で Bing Map 連携 Step by Step ～

29

リファレンス

SQL Server 2008 R2 自習書シリーズ http://www.microsoft.com/japan/sqlserver/2008/r2/technology/self-learning.mspx

SQL Server 2008 R2 開発者のための必読情報 http://www.microsoft.com/japan/sqlserver/2008/r2/technology/ SQL Server 2008 徹底検証シリーズ http://www.microsoft.com/japan/sqlserver/2008/r2/technology/cqi.mspx

SQL Server サポートオンライン http://support.microsoft.com/ph/13165 動画でわかる SQL Server 2008 R2 http://www.microsoft.com/japan/sqlserver/2008/r2/prodinfo/movie.mspx