本日は新宿で [Tech Fielders セミナー 東京 [BI を用いたログ管理（取込みから分析まで）～SQL Server 2008 R2 編]] が
開催されていたので参加してきました。
夜の部の [第2回 SQL Server & SQL Azure Bloggers Meeting] にも続けて参加。
久しぶりに SQL Server にどっぷりな一日です。
今日はそのセミナーレポートを Tech Fielders と Bloggers Meeting にわけて投稿。
まずは前半の Tech Fielders のセミナーレポートから。
■『BIを使ったログ管理（取込みからレポートまで）～SQL Server 2008 R2編』
Microsoft のエバンジェリスト 安納さんによる、SQL Server 2008 R2 を使用したログ管理のセッションです。
SSIS を使用して外部ファイルを SQL Server / SQL Azure に取り込み、PowerPivot で分析をするというシナリオになります。
■BI の 3つの柱
- データの準備：SSIS
- データの分析：SSAS
- データの可視化：SSRS
データの分析には若干のコーディング力が求められるので、エンドユーザーが実施するには敷居が高い…。
→ 2008 R2 からの PowerPivot でエンドユーザーでもデータ分析が、使いやすくなる。
エンドユーザーが慣れ親しんでいる、Excel のピボットテーブルを使用して、データの分析が可能に。
エクセルで外部データを取り込んで、分析が行える。
PowerPivot では、Excel の上限である 100 万行を超えることができる。(大量のデータを取り込むにはメモリは必要)
?
■環境の準備
- Windows Server 2008 R2 + SQL Server 2008 R2 EE RC
- Windows Server 2008 R2 + SharePoint Server 2010 RC
- Windows 7 + Excel 2010 RC + PowerPivot
- SQL Azure
Office? 2010 は Beta 版となるが、他については評価版で入手が可能。
注意:
OLEDB ドライバの Office 2010 用だと SSIS で使えなかったので、今回は Office 2007 用の OLEDB ドライバを使っている。
また、Office 2007 は x86 のみとなっているので、64 ビットランタイムは無効にする必要がある。
■SSIS の基本的な使い方
郵便番号データを使用した、データの取り込みのシナリオを実施。(新規 / 差分削除 / 差分追加)
# 日本郵便が提供しているデータを使用。
ポイント:
SSIS を使う場合には、変換元と変化先をつなぐ、接続マネージャーを意識する必要がある。
Excel → SQL Server
インポートおよびエクスポート ウィザードを使用して郵便番号データの Excel を取り込むデモ。
→? ウィザードで先頭行が列かどうかを指定することが可能。(他の場所でファイルが開かれていると取り込めない)
変換元として Excel、変換先として SQL Server を指定。
→? すべてのデータをコピーすることも、クエリを記述して元になるファイルの内容を絞り込むことが可能。
Excel の場合は、取り込むシートを指定することが可能になる。
テーブルのからむとセルのマップは自動で行われるが、新規テーブルに取り込む場合は、手動でカラムの属性を変更することも可能
テーブルが既に存在する場合は削除するかを指定することができる。
ウィザードが終了すると SSIS のパッケージが作成されるので、作成したパッケージを実行することでデータを取り込むことができる。
CSV → SQL Server
CSV の場合は、Excel と異なり、データの区切りや、ファイルのコードページ等を指定する必要がある。
CSV もインポートおよびエクスポートウィザードで取り込みが可能。
変換元として [フラット ファイル ソース] 指定する。
→ プレビューで区切り等の設定状況を確認できる。
CSV の場合は各列の属性 (列名、列の型、文字列長) を指定する設定がある。
→ 取り込み先のテーブルの型定義に反映される。
先頭の 1000 行を使った型の推測機能があるが、データの量が多い場合は、推測されたものと実際の内容に乖離があることがあるため、
推測に頼りすぎるのは注意！！
作成されるパッケージの流れは Excel 同一。
データフロータスクが Excel と異なる。(変換元が、CSVになっている)
データフロータスクでは、エラー時のフローを指定することができる。
→ 失敗時のフローを、フラットファイル変換先に接続することで、エラーがあったものを別のテキストに出力することができる。
[フラットファイルソースのエラー出力列] という項目にエラーとなった行が丸ごと含まれている。
ポイント:
通常、エラーが発生すると SSIS 本体にエラーが返されるので、変換元の [エラー出力] から、[エラー] を [エラーコンポーネント] ではなく
[行のリダイレクト] に設定し、外部ファイルに出力する必要がある。
変換元に指定したものは、そのまま変換タスクに列を渡すのではなく、変換元で外部列 → 出力列 という流れでデータが渡される。
# 変換元のフローの場合は右クリックして、[詳細エディターの表示] を開き、[入力プロパティと出力プロパティ] を表示することで確認ができる。
差分データの削除
差分の削除データを受け取った場合の削除処理の実装方法。
OLE DB コマンドを使用して差分データの削除を指定できる。
→ パラメータ化された SQL 文を指定する。
[詳細エディターの] 列マッピングを使用して、OLE DB コマンドで指定したパラメータ (? で指定) と入力列を対応付けする。
先頭のパラメータから、[Param_x] で連番 (0からの数字) となるので、入力のどの項目とパラメータが対応数r化をマッピングさせ、既存の列を削除することができる。
処理の自動化
作成したプロジェクトを [ビルド] して dtsx ファイルを作成し、SQL Server Agent に [SQL Server Integration Services パッケージ] を
実行するステップを含むジョブとして登録する。
ビルド：BIDS (BI Development Studio)
ジョブの登録 : SSMS (SQL Server Management Studio)
パッケージはファイルシステムとしてローカルに保存した [dtsx] ファイルを指定することが可能。
# パッケージをビルドするとローカルの bin フォルダに dtsx ファイルが保存されるのでそちらを使用。
ポイント:
デフォルトでは、[SQL Server Agent] のサービスは停止しているので、自動起動にする。
CSVl → SQL Azure
SQL Azure のサーバー名を指定することで、SSMS で直接接続することができる。
SQL Azure を使用する場合、認証は [SQL Server 認証] しか、現状使うことができない。
# 接続時された後にオブジェクト エクスプローラーで表示されるアイコンはオンプレミスと SQL Azure で異なる。
SSMS だけでなく、BIDS でも接続することが可能。
CSV ファイルの変換先を SQL Azure に指定して、直接 SQL Azure に投入するデモ。
SQL Azure の文字列データは [Unicode] を使用する。
SQL Azure にテーブルを作成する場合は、 [ID] 列が必ず必要となる。
■SSIS によるイベントログの取り込み
イベントログ → SQL Server
イベントログの再利用。
SQL Server にイベントログを格納することで検索時の利便性向上。
WMI を使用してイベントログの情報を取得し、SQL Server に格納する仕組みを SSIS で構築。
TimeWritten の項目を使用して前回取り込み以降の情報を制御する。
# TimeWritten : イベントが書き込まれた日時
テーブルは、イベントログの種類 (Application / System / Security) に分けている。
各テーブル内には、複数のサーバーの情報が格納される。
イベントログを取得する対象のサーバーはソースリスト用のテーブルに格納している。
コンピュータのリストを取得して、[Foreach ループ コンテナ] で取得したコンピュータリストに対して繰り返し処理を実行する。
- SQL 実行タスクでコンピュータリストのテーブルから、全レコードを取得
結果の取得方法に関しては、[結果セット] で指定する。
今回は [完全な結果セット] を [User::ComputerList] に格納する。(コンピュータ名とイベントログの名前)
- 取得した [User::ComputerList] を Foreach ループコンテナで使用する。
[ADO オブジェクト ソース変数] に [User::ComputerList] を指定している。
# Foreach ADO 列挙子取得した項目を、[変数のマッピング] で 変数に設定する。
[User::tmmpComputerName] [user::tmpLogfileName] という変数に取得した値を格納している。
# ADO 列挙子で取得した値は、インデックス番号 0 から始まる。Foreach ループコンテナで [ループの各繰り返しの開始点で停止します] のブレークポイントを指定すると、
ループで使用されている値がわかりやすい。 - テーブル名の動的生成
コンピュータリストに格納されている、イベントログの名前を使用してテーブルを動的に作成する。
# 特定のコンピュータにしか指定していない、イベントログが存在する可能性もある。
そのため、取得したレコードを元に、対象のイベントログの名前のテーブルが存在しているかを確認し、
存在していない場合は、テーブルの新規作成が必要となる。Expression を使用することで、動的に SQL を生成し、実行することができる。
今回は Expression を使用して、[SqlStatementSource] という、実行する SQL を指定するプロパティの値を動的に生成して、
実行する SQL を柔軟に設定することを可能にしている。 - 取り込み範囲のチェック
現在処理をしている、コンピュータ 名/ 対象のログの種別から、[TimeWritten] の最大値を取得。処理しているコンピュータ名 / ログの種別によって動的に SQL を生成する必要があるためこちらも Expressioin を使用。
取得した、値を、[User::MaxTimeWritten] に設定する。
- イベントログの取得
[WMI データ リーダー タスク] を使用して、WMI でイベントログのデータを取得。今までの SQL 実行と同様、実行する WQL (WMI で実行するクエリ) 波動的に変更される。
WQL を直接書いてしまうと、特定のサーバーの情報しか取得ができない。そのため、[WqlQuerySource] には、WQL を直接記載するのではなく、変数 (今回は [User::WQL]) を指定している。
Expression を使用して動的に SQL を生成している変数を作成して、実行するクエリとして指定している。ポイント:
範囲指定で使用する、[User::MaxTimeWritten] は日付型なので、文字列にキャストする必要がある。
取得した結果は [User::EventData] に格納しているが、値の型を [Object] にし、どのような戻り値であっても対応可能とする。
データ型は画面左の [変数] で確認ができる。
キャストについては式ビルダで確認することが可能。 - 取得したイベントログを繰り返し処理
Foreach ループコンテナーで取得したイベントログを ADO 列挙子として、指定して繰り返し処理を実行
変数のマッピングで、取得した ADO 列挙子の内容を、変数に格納 - INSERT 文の生成
イベントログの内容は、改行やシングルクォートが入っている可能性があるため、をのまま INSERT をするとエラーとなる可能性がある。
# 形式エラーの可能性も (日付型に対しての NULL 値 等)そのため、スクリプトタスクで SQL を成形して、SSIS の変数として格納
- SQL の実行
生成した INSERT 文を実行する。
実行する SQL としてスクリプトタスクで生成した変数を指定する。
■PowerPivot を使用した分析
SQL Server 2008 までは SSAS を使用していたが、R2 では PowerPivot を使用して分析を行うというシナリオになる。
→ 今までと同様、BIDS SSAS そのまま使用することもできるが PowerPivot によって、エンドユーザーにより使いやすくなっている。
今までの、BI は BIDS を使用して準備をしないと使えなかったためファーストステップが難しかった。
IW (インフォメーションワーカー層) が使用できる、セルフサービス BI を SQL Server 2008 R2 で実現。
PowerPivot には以下の 2 種類がある。
- PowerPivot for Excel 2010
Excel の上限を超えた件数のデータに対応
インメモリエンジン
ローカル PC のリソースを使用
PowerPivot のアドインをインストールする必要がある
- PowerPivot for SharePoint 2010
SharePoint の Excel サービスを使用
for Excel 2010 では自分が使用する機能であり、多数のユーザーに公開ができない
→ for SharePoint ではポータル経由で複数のユーザーに公開ができる。
ブラウザを介してアクセスするため、サーバーのリソースを使用
PowerPivot を使用することで、SSAS を介さずにデータの分析が可能となる。
ポイント :
PowerPivot はエンドユーザーが SQL Server に直接接続をするため、SQL Server で適切な権限を設定する必要がある。
PowerPivot の利用手順
- 計画
何を分析するのかを明確にする必要がある。
分析対象が決まらないとキューブを作成することができない。何を基準に分析をするか (分析軸) を決めることも重要
- 分析軸用のテーブルを準備
分析対象のデータを何を基準に分析するか。
分析の基準となる分析軸用のテーブルを作成する。
→ 時間軸 / ログタイプ (情報 / 警告 / エラー / 重大)? / コンピューター名分析軸用テーブルは SQL Server 上のテーブルではなく、EXCEL のシートで用意しておけばよい。
時間軸のテーブルは Excel の [フィル] の [連続データの作成] の [データ予測] を使用すると簡単に作れる。
→ ドラッグして作るのはオートフィル - Excel 2010 から PowerPivot を起動
PowerPivot のアドインをインストールするとリボンに PowerPivot が追加されている。
リボンから、PowerPivot for Excel を起動することが可能となる。
- 分析に使用するテーブルを読み込み
デフォルトで他データベースや SQL Azure を選択することも可能。
→ SSRS や ATOM フィードからインポートすることも可能。SSIS 同様、クエリによるデータの絞り込みも可能。
分析に使用するベースとなるデータと分析軸として使用するデータの取り込みを行う。
- 計算列の追加
分析に使用する項目が既存の列を使用して計算をする必要がある場合は列を追加する。データと分析軸でリレーションが設定できるように、値の粒度 (精度) を同じにする。
→ 分析軸のデータを分析で使用する粒度に分割することもポイント。 - リレーションシップの作成
[デザイン] の [リレーションシップの作成] からリレーションシップを作成することができる。
- ピボットテーブルで分析
PowerPivot からピボットテーブルを作成し、データの分析を行う。スライサーにより、分析対象の選択がしやすくなる。
→ ドラッグアンドドロップでフィルタができる UI を表示することができる。ここまでが PowerPivot for Excel で操作する内容。
- PowerPivot for SharePoint による分析データの公開
SharePoint の PowerPivot ライブラリに公開することができる。
# Excel サービス + PowerPivot サービスをサーバー側で使っている。PowerPivot で指定したスライサーも利用することができる。
アクセス権の制御は SharePoint 上で行う事ができる。
→ MOSS のアクセス許可で設定できる。プレビューは Excel のシートを元に表示されるため、空のシートがある場合は削除しておくと見栄えが良い。
→ プレビューには SilverLight が使われている。
?
■ライトニングトーク
最後は、Tech Fielders 恒例のライトニングトークになります。
今回は SQL Server と Oracle の資格体系の比較と TMG についてのライトニングトークでした。
まさか LT で TMG が来るとは思っていませんでした。
SQL Server と Oracle の資格体系の比較では、Oracle の Bronze / Silver / Gold / Platium と比べると、
SQL Server の MCITP は上長のうけと学習の内容の差について。
Oracle Bronze では SQL が範囲にあり、クエリの勉強が必須となりますが MCITP ではあまり構文に触れないと言った
話は確かにそうだな～と思いました。
SQL Server だと構文は MCP ではなく MCA の別体系の資格になってしまうんですよね…。
Oracle の方が一つの資格体系でクエリも含めた基礎から通して勉強できるので話にあったように新入社員の方にも
勧めやすい資格だと思います。
TMG については TMG でこのようなことができますという機能紹介が。
若い方がセキュリティ製品について話をされていたことにまず驚きでした。
バーチャルパッチやウイルス検知、メール保護、SSL検査等の TMG が持っているセキュリティ機能について LT をされていました。
TMG はバーチャライゼーションサミットでも話があったので興味がある方は多かったのではないでしょうか。
?
前半の Tech Fielders に関してはこの辺で。
続いては Bloggers Meeting の内容を。