有限会社エスキューエル・クオリティ

セッション ID：T4-304
Microsoft BI を支える
Reporting Services
& PowerPivot
～松本美穂のすぐに使える
BI システム構築の技～
有限会社エスキューエル・クオリティ
代表取締役／コンサルタント
松本美穂
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 パフォーマンスチューニング
高負荷テスト（ラッシュ／ストレステスト）実施のためのテストアプリの作成支援
3
自習書シリーズのご紹介（弊社執筆）
全 29本、約 3,200 ページ！
SQL Server 2008 R2 からの新機能
PowerPivot for Excel 2010 セルフサービス分析
セルフサービスレポーティング（Report Builder 3.0）
マルチサーバー＆アプリケーション管理
MDS によるマスターデータ管理
StreamInsight によるストリームデータの処理
SQL Azure 入門
SQL Server 2008 R2
お勧めコンテンツ
Reporting Services 入門／応用／実践
Analysis Services 入門／応用
Integration Services 入門／応用
インデックスの基礎とメンテナンス etc
4
対応へ Update 済み
セッションの目的とゴール
PowerPivot ＆ Reporting Services 構築の技
セッションの目的
PowerPivot や Reporting Services を利用した
BI システム構築における、知っておいてほしい
構築時のポイント／考慮事項をご理解いただく
セッションのゴール
PowerPivot や Reporting Services を利用する
上でのポイントを理解できるようになる
PowerPivot や Reporting Services の基本的な
操作手順については、弊社執筆の自習書シリー
ズをぜひご覧ください！
5
アジェンダ
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)
6
PowerPivot とは
SQL Server 2008 R2 からの目玉の新機能！
PowerPivot for Excel 2010（クライアント側）
ピボットテーブルの超進化版
使い慣れた Excel で高度なデータ分析が容易に可能
PowerPivot for SharePoint 2010（サーバー側）
共有、Web ベース、セキュアなデータ格納
for Excel 2010
for SharePoint 2010
ピボット
テーブル
ピボット
グラフ
スライサー
7
Web から
分析レポート
を参照可能
PowerPivot for Excel の特徴
インメモリアーキテクチャによる高速動作
サーバー側の作り込みが不要
通常の BI ソフトウェアは、サーバー側の作り込み必須
Excel だけで誰でも手軽にパワフルなデータ分析が可能
インメモリアーキテクチャで高速なデータ処理
Analysis Services VertiPaq エンジン
従来の Analysis Services エンジンを進化させたもの
64 ビット（x64）にも対応
32 ビット版と 64 ビット版の提供
Excel の上限 100万件を超えるデータにも対応
x64 環境でメモリを多く搭載していれば、
数億件のデータでも高速なデータ処理が可能
(ブックの最大サイズは 2GB まで)
8
PowerPivot for Excel の内部動作
Analysis Services VertiPaq エンジン
インメモリアーキテクチャ（in-proc engine）
PowerPivot
ファイル
（.xlsx）
ユーザーの
Temp
フォルダー
メモリ
\ユーザー名\AppData
\local\Temp フォルダー
集計演算
解凍
9
\xl\customData
\item1.data ファイル
PowerPivot データが
丸ごと圧縮されたファイル
（Analysis Services のバッ
クアップと同じ形式）
メモリへ
そのまま展開
カラム（列）ごとに
ファイルが作成される
カラムベース
アーキテクチャ
Excel 画面
データのインポート／データ更新は？
データソースへのクエリ実行＆丸ごと取得
データのインポート／データ更新時の動作
クエリ結果を .xlsx ファイル内へ丸ごとインポート
クライアント
PowerPivot for Excel
データ更新時に
実行されるクエリ
サーバーへの高負荷
大量データの JOIN など
データソース
1
処理
2
3
データベース
テーブルデータ
クエリ結果を丸ごとコピー
ネットワークへの負荷
大量データの転送
10
1
PowerPivot for Excel
3
多数のユーザーが同時に
データ更新を実行する可能性
構築／運用時のポイント
手軽さゆえの落とし穴
メモリサイズに注意（見積りをしっかり！）
メモリが足りなかった場合の動作が悲惨
ファイルサイズを小さくするポイント
不要な列（分析に利用しない列）を取得しないこと！
データ更新に注意
多数のユーザーが同時にデータ更新すると ...
なんでもかんでも自由に操作させるのか？
ユーザーにすべて見せるのか？
見えすぎてもピボットテーブル初級者には操作しづらい
正規化されている業務 DB は、初級者には操作しづらい
→ ビューやデータウェアハウスの構築で回避
セキュリティは？
11
PowerPivot for SharePoint 2010
SharePoint Server 2010 との統合
主な特徴
分析レポートの「共有」と「セキュアなデータ格納」
バッチ更新（バックグラウンドでのデータ更新）
Excel Services による Web ブラウザーベースの共有
Silverlight ベースの PowerPivot ギャラリーの提供
管理ダッシュボードの提供
見栄えの良い
PowerPivot ギャラリー
12
内部動作
PowerPivot for SharePoint 2010
”新” Analysis Services
VertiPaq モードの Analysis Services
＝ Analysis Services VertiPaq エンジン
VertiPaq モードの Analysis Services
SharePoint
コンテンツ DB
Backup
フォルダー
SharePoint_Content_xx
AllDocStreams テーブル
PowerPivot
ファイル
（.xlsx）が
バイナリ登録
されたもの
\xl\customData
\item1.data
13
Analysis Services を
インストールしたフォルダー
\Program Files
\Microsoft SQL Server
\MSAS10_50.POWERPIVOT
\OLAP\Backup
\Sandboxes\
DefaultPowerPivotServiceApp~
解凍
メモリ
msmdsrv.exe
SharePoint
Excel Services
Excel Calculation Services
が Web 形式へ変換
Excel Web Access
集計演算
メモリへ
そのまま展開
Web ブラウザー
構築／運用時のポイント
PowerPivot for SharePoint 2010
メモリサイズに注意！
メモリが足りなかった場合の動作が･･･
メモリサイズの見積もりをしっかりと！
データ更新をバッチ実行できる！
設定手順は、次スライド参照
Excel Services の考慮事項
Excel Services 処理用 (Excel Calculation Services 用)
の SharePoint サーバーの追加を検討
必要に応じて、複数台で負荷分散を実施
14
参考：設定のポイント
PowerPivot for SharePoint 2010
データ更新（バッチ更新）の設定手順
PowerPivot ファイル（.xlsx）のドロップダウンメニューから
［データ更新の管理］をクリックして、［データ更新］を有効化
サーバーの全体管理 → アプリケーションの全般設定
→ PowerPivot → サービスアプリケーションの設定の構成
から、データ更新の基本設定が行える
PowerPivot ファイル（.xlsx ファイル）を作成する際に
Excel 2010 のリボンの［データ］タブの［プロパティ］から
［ファイルを開くときにデータを更新する］をチェックする
SharePoint 2010 のファイルの最大サイズを変更
Excel Services に対しての設定
サーバーの全体管理 → サービスアプリケーションの管理
→ ExcelServiceApp1 → 信頼できるファイル保存場所から
→［ブックの最大サイズ］を設定（～2GB まで設定可能）
SharePoint Web アプリケーションに対しての設定
サーバーの全体管理 → Web アプリケーションの管理
→ 全般設定 → ［アップロードの最大サイズ］
15
参考：管理ダッシュボードの内部動作
PowerPivot for SharePoint 2010
SharePoint の利用状況収集機能を利用
PowerPivot for
SharePoint
C:\Program Files\Common Files
\Microsoft Shared\Web Server
Extensions\14\LOGS フォルダー内
.log ファイル
2
利用状況
・接続ユーザー数
・クエリ実行数
・クエリ応答時間
・サーバー状態
（CPU やメモリ）
1
逐次
SharePoint
ログ DB
WSS_
Logging
SharePoint Foundation
利用状況データのインポー
トジョブ（30分ごと）
SharePoint Foundation
利用状況データの処理ジョ
ブ（1日ごと朝 1～3時）
PowerPivot 管理ダッシュ
ボード処理タイマージョブ
（1日ごと朝 3～5時）
PowerPivot 用
DB
3
DefaultPowerPivot
ServiceApplicationDB
4
管理ダッシュボード
用の PowerPivot
ファイル（.xlsx）
PowerPivot 状態統計コレクター
タイマージョブが収集（15分ごと）
PowerPivot System
サービスが監視
16
管理ダッシュボード
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 パワーを利用
17
続き
パフォーマンスへの考慮
性能の良いデータウェアハウスの構築を検討
データ更新時の負荷への対応
SQL Server 2008 R2 Parallel Data Warehouse
エディションへの期待
すべてをインタラクティブに見せる必要はない
Reporting Services などを利用して、
静的なデータを作成しておくなど
VertiPaq モードの Analysis Services は、
従来の Analysis Services と同様、
OLAP サーバーとして利用することも可能
= Reporting Services などのデータソースとして
SharePoint 上の PowerPivot ファイルを指定可能
18
Reporting Services
Reporting Services の特徴
SQL Server 2008 R2 からの新機能
共有データセット＆クエリキャッシュ
Reporting Services 利用時のポイント
PivotViewer Extension
19
Reporting Services の特徴
手軽に見栄えの良いレポートを簡単作成！
パレート図
(ABC 分析グラフ)
Bing マップ
連携
自由なレイアウト
で配置
２Y軸
見栄えの良い
多彩なグラフ
ゲージ
帳票形式
20
データバー
インジケーター
スパークライン
SQL Server 2008 R2 からの新機能
さらなる進化！
見栄えの部分
マップ（地図）機能の追加
Bing マップ連携、Spatial データ型対応
データバー、インジケーター、スパークラインの追加
共有レポートパーツ
レポート内のパーツ（グラフや Tablix、ゲージ、マップ
など）の共有が可能
更新検知機能もあり
共有データセットとクエリキャッシュ
データセットの共有が可能に
クエリ実行結果のキャッシュも可能！
「キャッシュの更新計画」機能によるキャッシュの
ウォームアップも可能に
21
共有データセット、クエリキャッシュ
R2 からの新機能の中で最も役立つ！
共有データセット
レポートビルダー 3.0 から作成可能
Business Intelligence Development Studio の場合は、
既存のデータセットから共有データセットへ変換可能
クエリキャッシュ
共有データセットで定義したクエリ結果を
キャッシュできる機能
レポートマネージャーから設定可能
22
キャッシュの効果の確認
ReportServer DB の ExecutionLog2 ビュー
SELECT ReportPath, Parameters
,DATEDIFF(millisecond, TimeStart, TimeEnd) AS 実行時間
,TimeDataRetrieval,TimeProcessing,TimeRendering,ByteCount
FROM ExecutionLog2 ORDER BY TimeStart DESC
23
構築のポイント
Reporting Services による BI システム
共有機能を活用する
共有データソース、共有データセット、共有レポート
パーツを利用して、レポート作成時間を短縮
キャッシュ機能を活用する
レポートキャッシュ or クエリキャッシュ
キャッシュの更新計画機能でキャッシュをウォーム UP
定期配信機能（サブスクリプション）の検討
メールでの定期配信（Weekly レポート etc）
共有フォルダーへの定期配信
SharePoint 統合モードを利用する利点
Web パーツでポータルの作成ができる
後述の PivotViewer 機能を利用できる
24
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
25
まとめ
PowerPivot ＆ Reporting Services 構築の技
使われない BI システムとならないために！
PowerPivot ＆ Reporting Services はスゴイ！
手軽にパワフルな BI システムを構築できる
キャパシティプランニングが重要！
メモリサイズの見積り、CPU 負荷の見積り etc
内部動作を理解することが重要！
キャッシュが最も重要！
キャッシュを活用できるかどうかが大きな分かれ道！
今後の期待（より魅力的な BI システムへの期待）
PivotViewer Extension
Parallel Data Warehouse エディション
26
ご清聴ありがとうございました。
T4-304
アンケートにご協力ください。
関連セッション
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 ～
28
リファレンス
SQL Server 2008 R2 自習書シリーズ
http://www.microsoft.com/japan/sqlserver/2008/r2/technology/selflearning.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
29

Download Report