3/15/2017 12:37 AM © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
有限会社エスキューエル・クオリティ 代表取締役/コンサルタント 松本 美穂 3/15/2017 12:37 AM セッション ID:T4-304 Microsoft BI を支える Reporting Services & PowerPivot ~ 松本美穂のすぐに使える BI システム構築の技 ~ 有限会社エスキューエル・クオリティ 代表取締役/コンサルタント 松本 美穂 © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
Speaker 自己紹介 有限会社エスキューエル・クオリティ 主なコンサルティング実績 3/15/2017 12:37 AM 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 パフォーマンス チューニング 高負荷テスト(ラッシュ/ストレス テスト)実施のためのテスト アプリの作成支援 © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
自習書シリーズのご紹介(弊社執筆) 全 29本、約 3,200 ページ! 3/15/2017 12:37 AM 自習書シリーズのご紹介(弊社執筆) 全 29本、約 3,200 ページ! SQL Server 2008 R2 からの新機能 PowerPivot for Excel 2010 セルフ サービス分析 セルフ サービス レポーティング(Report Builder 3.0) マルチ サーバー&アプリケーション管理 MDS によるマスター データ管理 StreamInsight によるストリーム データの処理 SQL Azure 入門 お勧めコンテンツ Reporting Services 入門/応用/実践 Analysis Services 入門/応用 Integration Services 入門/応用 インデックスの基礎とメンテナンス etc SQL Server 2008 R2 対応へ Update 済み © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
セッションの目的とゴール PowerPivot & Reporting Services 構築の技 PowerPivot や Reporting Services を利用したBI システム構築における、知っておいてほしい構築時のポイント/考慮事項をご理解いただく セッションのゴール PowerPivot や Reporting Services を利用する上でのポイントを理解できるようになる PowerPivot や Reporting Services の基本的な操作手順については、弊社執筆の自習書シリーズをぜひご覧ください!
アジェンダ PowerPivot & Reporting Services 構築の技 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 Excel 2010 for SharePoint 2010 ピボット テーブル ピボット グラフ スライサー Web から 分析レポートを参照可能
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 フォルダー メモリ Excel 画面 \ユーザー名\AppData \local\Temp フォルダー 集計演算 解凍 メモリへ そのまま展開 \xl\customData \item1.data ファイル カラム(列)ごとに ファイルが作成される PowerPivot データが 丸ごと圧縮されたファイル (Analysis Services のバックアップと同じ形式) カラム ベース アーキテクチャ
データのインポート/データ更新は? データ ソースへのクエリ実行&丸ごと取得 データのインポート/データ更新時の動作 クエリ結果を .xlsx ファイル内へ丸ごとインポート サーバーへの高負荷 大量データの JOIN など クライアント PowerPivot for Excel データ更新時に 実行されるクエリ データソース 1 処理 データベース 2 3 テーブル データ クエリ結果を丸ごとコピー ネットワークへの負荷 大量データの転送 3 1 多数のユーザーが同時に データ更新を実行する可能性 PowerPivot for Excel
構築/運用時のポイント 手軽さゆえの落とし穴 メモリ サイズに注意(見積りをしっかり!) メモリが足りなかった場合の動作が悲惨 ファイル サイズを小さくするポイント 不要な列(分析に利用しない列)を取得しないこと! データ更新に注意 多数のユーザーが同時にデータ更新すると ... なんでもかんでも自由に操作させるのか? ユーザーにすべて見せるのか? 見えすぎてもピボット テーブル初級者には操作しづらい 正規化されている業務 DB は、初級者には操作しづらい → ビューやデータ ウェアハウスの構築で回避 セキュリティは?
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 Excel Services SharePoint コンテンツ DB VertiPaq モードの Analysis Services Backup フォルダー メモリ SharePoint_Content_xx Excel Calculation Services が Web 形式へ変換 AllDocStreams テーブル msmdsrv.exe Analysis Services を インストールしたフォルダー \Program Files \Microsoft SQL Server \MSAS10_50.POWERPIVOT \OLAP\Backup \Sandboxes\ DefaultPowerPivotServiceApp~ Excel Web Access 集計演算 PowerPivot ファイル(.xlsx)が バイナリ登録されたもの 解凍 \xl\customData \item1.data メモリへ そのまま展開 Web ブラウザー
構築/運用時のポイント PowerPivot for SharePoint 2010 メモリ サイズに注意! メモリが足りなかった場合の動作が ・・・ メモリ サイズの見積もりをしっかりと! データ更新をバッチ実行できる! 設定手順は、次スライド参照 Excel Services の考慮事項 Excel Services 処理用 (Excel Calculation Services 用) の SharePoint サーバーの追加を検討 必要に応じて、複数台で負荷分散を実施
参考: 設定のポイント PowerPivot for SharePoint 2010 データ更新(バッチ更新)の設定手順 PowerPivot ファイル(.xlsx)のドロップダウン メニューから [データ更新の管理]をクリックして、[データ更新]を有効化 サーバーの全体管理 → アプリケーションの全般設定 → PowerPivot → サービス アプリケーションの設定の構成 から、データ更新の基本設定が行える PowerPivot ファイル(.xlsx ファイル)を作成する際に Excel 2010 のリボンの[データ]タブの[プロパティ]から [ファイルを開くときにデータを更新する]をチェックする SharePoint 2010 のファイルの最大サイズを変更 Excel Services に対しての設定 サーバーの全体管理 → サービス アプリケーションの管理 → ExcelServiceApp1 → 信頼できるファイル保存場所から →[ブックの最大サイズ]を設定(~2GB まで設定可能) SharePoint Web アプリケーションに対しての設定 サーバーの全体管理 → Web アプリケーションの管理 → 全般設定 → [アップロードの最大サイズ]
参考: 管理ダッシュボードの内部動作 PowerPivot for SharePoint 2010 C:\Program Files\Common Files \Microsoft Shared\Web Server Extensions\14\LOGS フォルダー内 PowerPivot for SharePoint SharePoint ログ DB PowerPivot 用 DB .log ファイル 2 3 WSS_ Logging DefaultPowerPivot ServiceApplicationDB 利用状況 SharePoint Foundation 利用状況データのインポート ジョブ(30分ごと) 4 ・接続ユーザー数 ・クエリ実行数 ・クエリ応答時間 ・サーバー状態 (CPU やメモリ) 1 SharePoint Foundation 利用状況データの処理ジョブ(1日ごと 朝 1~3時) 管理ダッシュボード用の PowerPivot ファイル(.xlsx) 逐次 PowerPivot 管理ダッシュボード処理タイマー ジョブ (1日ごと 朝 3~5時) PowerPivot 状態統計コレクター タイマー ジョブが収集(15分ごと) PowerPivot System サービスが監視 管理ダッシュボード
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 パワーを利用
続き パフォーマンスへの考慮 性能の良いデータ ウェアハウスの構築を検討 すべてをインタラクティブに見せる必要はない データ更新時の負荷への対応 SQL Server 2008 R2 Parallel Data Warehouse エディションへの期待 すべてをインタラクティブに見せる必要はない Reporting Services などを利用して、 静的なデータを作成しておくなど VertiPaq モードの Analysis Services は、 従来の Analysis Services と同様、 OLAP サーバーとして利用することも可能 = Reporting Services などのデータソースとして SharePoint 上の PowerPivot ファイルを指定可能
Reporting Services Reporting Services の特徴 SQL Server 2008 R2 からの新機能 共有データセット&クエリ キャッシュ Reporting Services 利用時のポイント PivotViewer Extension
Reporting Services の特徴 手軽に見栄えの良いレポートを簡単作成! パレート図 (ABC 分析グラフ) Bing マップ 連携 自由なレイアウトで配置 2Y軸 見栄えの良い 多彩なグラフ ゲージ 帳票形式 データバー インジケーター スパークライン
SQL Server 2008 R2 からの新機能 さらなる進化! 見栄えの部分 マップ(地図)機能の追加 Bing マップ連携、Spatial データ型対応 データバー、インジケーター、スパークラインの追加 共有レポート パーツ レポート内のパーツ(グラフや Tablix、ゲージ、マップ など)の共有が可能 更新検知機能もあり 共有データセットとクエリ キャッシュ データセットの共有が可能に クエリ実行結果のキャッシュも可能! 「キャッシュの更新計画」機能によるキャッシュの ウォーム アップも可能に
共有データセット、クエリ キャッシュ R2 からの新機能の中で最も役立つ! レポート ビルダー 3.0 から作成可能 Business Intelligence Development Studio の場合は、既存のデータセットから共有データセットへ変換可能 クエリ キャッシュ 共有データセットで定義したクエリ結果を キャッシュできる機能 レポート マネージャーから設定可能
キャッシュの効果の確認 ReportServer DB の ExecutionLog2 ビュー SELECT ReportPath, Parameters ,DATEDIFF(millisecond, TimeStart, TimeEnd) AS 実行時間 ,TimeDataRetrieval,TimeProcessing,TimeRendering,ByteCount FROM ExecutionLog2 ORDER BY TimeStart DESC
構築のポイント Reporting Services による BI システム 共有機能を活用する 共有データソース、共有データセット、共有レポート パーツを利用して、レポート作成時間を短縮 キャッシュ機能を活用する レポート キャッシュ or クエリ キャッシュ キャッシュの更新計画機能でキャッシュをウォーム UP 定期配信機能(サブスクリプション)の検討 メールでの定期配信(Weekly レポート etc) 共有フォルダーへの定期配信 SharePoint 統合モードを利用する利点 Web パーツでポータルの作成ができる 後述の PivotViewer 機能を利用できる
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
まとめ PowerPivot & Reporting Services 構築の技 使われない BI システムとならないために! キャパシティ プランニングが重要! メモリ サイズの見積り、CPU 負荷の見積り etc 内部動作を理解することが重要! キャッシュが最も重要! キャッシュを活用できるかどうかが大きな分かれ道! 今後の期待(より魅力的な BI システムへの期待) PivotViewer Extension Parallel Data Warehouse エディション
ご清聴ありがとうございました。 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 ~
リファレンス 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
Microsoft Tech∙Ed Japan 2010 © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY,AS TO THE INFORMATION IN THIS PRESENTATION.