BI Development StudioでETL処理を開発する

　前回の「Integration Servicesで異種データも楽々インポート」では、エンタープライズレベルに対応したIntegration Servicesの概要を紹介しました。アーキテクチャの一新による大規模開発対応、開発／管理環境の統合による生産性向上など、DTS（Data Transformation Services）の後継としてあらゆる点でIntegration Servicesは強化されています。

　アーキテクチャなどについて解説した前回に続き、今回はIntegration Servicesの統合開発環境である「BI Development Studio」を利用して、簡単なETL処理を実装してみたいと思います。

 

BI Development StudioによるETL処理

　Integration Servicesでは、ETL処理を開発、管理するための統合開発環境としてBI Development Studioが提供されます。Visual Studio 2005のシェルによって、Visual Studioと同様のインターフェイスを持っているので、ツールの使い方などで戸惑うことは少ないでしょう（図1）。

 

図1　BI Development Studioのインターフェイス（クリックすると拡大します）

 

　BI Development Studioを利用したETL処理パッケージの構築は、次の3点の処理を定義することがメインになります。

処理	概要
制御フロー	処理の流れを定義する。データフローの実行やファイルの生成、メールの送信、スクリプト実行などのタスクを実行できる。
データフロー	データの処理に関する一連の流れを定義する。データの変換元、変換処理、変換先を定義することにより､ETL処理の中核となる抽出からロードまでの流れを定義する。
イベントハンドラ	イベント発生時の処理を定義する。エラーによって発生したイベントや、処理完了後のイベントなどに対する処理を定義する。
表1　BI Development StudioによるETL処理パッケージの定義内容 制御フロー、データフローでそれぞれどのような処理を行えるかは、前回「Integration Servicesで異種データも楽々インポート 」にまとめています。

 

 

 

BI Development Studioによる開発概要

　それでは、早速BI Development Studioを立ち上げて、ETL処理を開発してみましょう。BI Development Studioは、「スタート」メニューから「Microsoft SQL Server 2005」－「SQL Server Business Intelligence Development Studio」と操作して起動します。

　今回、BI Development Studioを利用して、次のような処理を実現したいと思います。この処理では、商品マスタの追加データとなるCSVファイルを読み込み、必要なデータの抽出と変換、テーブルへのロードを行います（図2）。

 

図2　処理フローの構成

 

（1）商品マスタファイルを作業用フォルダにコピーする。ファイルのコピーは「ファイル システム タスク」を利用する。

（2）データを読み込み、テーブルへ格納するための計算処理を行う。

（3）テーブルへデータのロードを行う。

 

　CSVファイルの内容は、図3のような簡単なものです。データ中の単価が税抜き単価なので、税込み単価を計算して商品マスタに格納したいと思います。

 

図3　変換元のCSVファイルの内容

 

プロジェクトの作成

　プロジェクトの作成は、「ファイル」－「新規作成」－「プロジェクト」より行います。BI Development Studioは、Analysis ServicesやReporting Servicesの開発環境も兼ねているので、プロジェクトテンプレートが各種用意されています。この中から「Integration Services プロジェクト」を選択します（図4）。

 

図4　新規プロジェクトの作成（クリックすると拡大します）

 

　プロジェクトを作成すると、BI Development StudioによるIntegration Services開発画面が表示されます。画面中央のタブを切り替えることにより、「制御フロー」「データフロー」「イベントハンドラ」などの開発を行うことができます。

 

制御フローの記述

　CSVファイルを、一時ディレクトリへコピーする処理を記述します。ファイルのコピーには「ファイル システム タスク」を利用します。

　ツールボックスより「ファイル システム タスク」をドラッグ＆ドロップし、［デザイン］ウィンドウへ配置します。タスクの名前をプロパティから設定できるので、適宜設定しておくとよいでしょう。ここでは「CSVファイルコピー」としました。

　「CSVファイルコピー」タスクをダブルクリックして「ファイル システム タスク エディタ」を表示させます（図5）。

 

図5　ファイル システム タスク エディタ（クリックすると拡大します）

 

　各設定項目には表2の内容を設定します。

項目	設定	説明
SourceConnection	商品マスタ.csv	コピー元のファイルを指定します。
Operation	ファイルのコピー	ファイルに対する実行操作を指定します。
DestinationConnection	temp	ファイルのコピー先を指定します。「使用法の種類」を「既存フォルダ」として、コピー先ディレクトリを指定してください。
OverwriteDestination	True	ファイルのコピーによる上書きを認めるか設定します。
表2　ファイル システム タスク エディタでの設定内容

 

 

 

データフロータスクの呼び出し

　ツールボックスより「データ フロー タスク」を［デザイン］ウィンドウへ配置します。

　処理の流れを「ファイルコピー」→「データ処理」という形にしたいので、「CSVファイルコピー」タスクをクリックして表示される緑色の矢印を「データ フロー タスク」へ接続しましょう（図6）。緑色の矢印は、接続元が成功した場合の処理フローを示します。

 

図6　タスクの接続（クリックすると拡大します）

 

　続いてデータベースの接続先を指定するために、「ソリューション エクスプローラ」よりデータベースへの接続を定義しましょう。

　「ソリューション エクスプローラ」から「データ ソース」を右クリックし「新しいデータ ソース」をクリックします。表示された「データ ソース ウィザード」にて接続先のデータベースを定義します。ここではSQL Server 2005のサンプル用に用意したデータベースを指定しています（図7）。

 

図7　接続マネージャ（クリックすると拡大します）

 

データフローの記述

　ここまでで制御フローの定義が完了したので、続いてデータフローの定義を記述します。データフローでは、制御フローでコピーしたファイルを利用し、データの読み込みと変換、データベースへのロードを行います。

　データフローの記述を行うために、表示タブを「データフロー」へ切り替えます。

　制御フローの記述と同じように、まずはツールボックスより必要なオブジェクトを配置します。表3の3点の配置を行ってください（図8）。

分類	オブジェクト名	名前
データフローの変換元	フラットファイルソース	商品マスタ
データフロー変換	派生列	税込単価
データフローの変換先	SQL Server 変換先	商品マスタ追加
表3　データフロー定義に使用する3つのオブジェクト

 

 

 

図8　データフロータスクの配置（クリックすると拡大します）

 

フラットファイルソースの設定

　「商品マスタ」をダブルクリックして、「フラット ファイル ソース エディタ」を表示させます。さらに、「フラット ファイル接続マネージャ」の「新規作成」をクリックし、「フラット ファイル接続マネージャ エディタ」より、データ元CSVの設定をします（図9）。

 

図9　フラット ファイル接続マネージャ エディタ（クリックすると拡大します）

 

　CSVファイルは、先頭行が列名になっているため、「先頭データ行を列名として使用する」にチェックを入れてください。また、「詳細設定」より、単価列の「Data Type」を「通貨[DT_CY]」としてください。

　これで「商品マスタ」の設定は完了したので、「税込単価」へ緑の矢印を接続します。

派生列の設定

　「税込単価」をダブルクリックして「派生列変換エディタ」を表示させます。表4に従い、派生列の設定を行います（図10）。

 

図10　派生列変換エディタ（クリックすると拡大します）

項目	設定
派生列名	税込単価
派生列	新しい列として追加
式	[単価] * 1.05
データ型	通貨[DT_CY]
表4　派生列変換エディタでの設定内容

 

 

 

　このように、派生列を利用すれば計算などを行った値を収める新たな列の生成が可能です。「税込単価」の設定は完了したので、「税込単価」と「商品マスタ追加」を接続します。

データフローの変換先の設定

　データフローの設定に入る前に、変換先となるデータベースへの接続を定義します。「接続マネージャ」を右クリックし「新しいデータソースからの接続」を選択し、データベースへの接続を設定します。

　それでは、これまでと同じく「商品マスタの追加」より、データフローの変換先の設定を行います。ダブルクリックで表示される「SQL 変換先エディタ」より接続先となるデータベースを指定しましょう（図11）。

 

図11　SQL 変換先エディタ（クリックすると拡大します）

 

　以上で今回の処理フローの記述が完了しました。早速実行してみましょう！

パッケージの実行

　パッケージを実行するには、「ソリューション エクスプローラ」より「Package.dtsx」を右クリックし「パッケージの実行」をクリックします。

　パッケージの実行を行うと、デバッグ用画面に切り替わり、各タスクが実行されていきます。タスクの実行結果は分かりやすく表示され、実行中が黄色、成功が緑、失敗が赤として表示されます。また、データフロー画面では、タスクごとの処理行数が表示されます（図12）。

 

図12　パッケージの処理結果（クリックすると拡大します）

 

　データが正しく生成されているか確認してみましょう。データの確認はSQL Server 2005の管理ツールである「SQL Server Management Studio」より行います（図13）。

 

図13　実行結果をSQL Server Management Studioより確認

 

　CSVによる入力データに対し、計算が行われ、その結果がテーブルへロードされていることが分かります。

データ ビューアによるデバッグ

　BI Development Studioでは、実行時にデータフローを流れるデータの中身をデータビューアを利用して確認することができます。

　データビューアでは、一般的な表による表示であるグリッドに加え、ヒストグラム、散布図、縦棒グラグといった種類から表示方法を選択可能です。

　データビューアを有効にするには、データフローの各オブジェクトをつなぐ矢印を右クリックし、「データビューア」から「データ フロー パス エディタ」にて設定を行います（図14）。データビューアを有効にすると、デバッグ実行時、該当個所で実行が停止し、データビューアが表示されます。

 

図14　データビューア（クリックすると拡大します）

 

　以上で、単純なマスタデータのインポート、計算、ロードまでのETL処理をBI Development Studioを利用して開発しました。ここまで見てきたとおり、ほとんどの処理をGUIから設定可能であり、非常に簡単に開発できることを実感できたと思います。

まとめ

　今回は簡単な処理を実装しましたが、より複雑な処理の実装ももちろん可能です。XMLファイルやExcelデータのインポート、OracleやDB2といった異種間データベースの接続、さらにはピボットやマージ、派生列といったデータ変換処理を加えることによって、柔軟なETL処理の実装が可能です。また、標準で用意された機能群では要件を満たせない場合、ネイティブコードおよび.NET準拠の言語でIntegration Servicesのプログラムを開発可能です。

　企業にはさまざまなデータが溢れ、これらのデータを利用するシステムへの要求はますます高まっていますが、Integration Servicesを利用することにより、多くの労力を解消することができるのではないでしょうか。