ビジネス・インテリジェンスが情報を活かす！
第2回 「DB2 Data Warehouse Edition V9.1」が提供するETL（DWE SQL Warehousing Tool）
コンテンツシリーズ
このコンテンツは全#シリーズのパート#です： ビジネス・インテリジェンスが情報を活かす！
このコンテンツはシリーズの一部分です：ビジネス・インテリジェンスが情報を活かす！
このシリーズの続きに乞うご期待。
はじめに
第1回目では、新しくリリースされたDB2 Data Warehouse Edition V9.1の新機能の説明と統合開発環境（Design Studio）について説明をしました。以降の記事では、提供する機能にブレイクダウンし、どのような機能か？どのように使うか？に焦点をあてていきます。 第2回目は、DB2 Data Warehouse Edition V9.1が提供をする機能の1つであるETLツール（DWE SQL Warehousing Tool）を説明します。ETLとは、Extract（データの抽出）、Transform（データ変換）、Load（データロード）の略で、既存システムからデータウェアハウスやデータマートにデータ移行します。ETL処理は、データウェアハウスのデータ信頼性を保持するにあたり重要で、データクレンジング設計を含めると非常に時間がかかる工程です。DWE SQL Warehousing Toolは、ETL処理を短期間で簡単に作成できるツールです。
ETLツールとは
ETLツールの目的
ETLツールは、データの品質管理とデータモデルの変更（データ移行）の2つの目的で使用されます。複雑な要件に対して、プログラムを組むのではなく、対話的にわかりやすく短時間で作成できます。また、昨今の情報システムでは、ユーザーのニーズにより、ETL処理を頻繁に変更する場合が多く、わかりやすくメンテナンス性がよいことはツールとしての機能要件でもあります。
データの品質管理
データの緒元には、基幹系システム、ホストのレガシーなシステム、インターネット、ユーザーの入力などさまざまなものが存在します。各々は、尺度が異なっている場合が多く、製品や店番のコード体系、円やドルといった単位の統一が必要です。また、ユーザー入力の不備による不良データがある場合は、修正やフィルタリングを行う必要があるかもしれません。不良なデータを使用しての分析は、データの品質レベル以上にはならず、意味のないものになります。 このように、データを洗浄してフィルタリングする処理をデータクレンジングとよびます。データクレンジングは構築時のみで行われるのではなく、データ品質を継続的に保証するしくみ作りが必要です。
データモデルの変更
基幹系と情報系ではデータモデルが異なる場合が多く、基幹系システムとデータウェアハウス間、データウェアハウスとデータマート間でETLツールが使用されます。基幹系のOLTPデータベースでは、更新を意識した正規化モデル（アプリケーション要件により一部非正規化されている場合が多い）で作成されています。一方、情報系のデータベースでは、分析軸を意識したスタースキーマデータモデルで作成されている場合が多数あります。
「データウェアハウス」ソリューション構成
DWE SQL Warehousing Toolの説明
DWE SQL Warehousing Toolを構成する要素は？
- DWE SQL Warehousing Toolクライアント
DWE SQL Warehousing Toolクライアントは、EclipsベースのDesign Studio上で動作する環境です。Design Studioでは、データフロー、コントロールフロー、およびデプロイメント・パッケージを作成するためのグラフィカル環境が用意されています。 - アプリケーション・サーバー（Administration Console）
WebSphere Application Server上にAdministration Consoleがデプロイされている環境です。Design Studio上のDWE SQL Warehousing Toolクライアント環境でETLアプリケーションを作成した後、Administration Consoleを使用してWebSphere Application Serverにデプロイします。Administration Consoleでは、デプロイされたETLアプリケーションの実行やスケジューリングを実行できます。なお、WebSphere Application Serverは、DB2 Data Warehouse Edition V9.1 Enterpriseに 含まれています。
ETLアプリケーションの作成手順は？
Design Studioを使用して、データフローとコントロールフローを作成します。データフローは個別のデータ処理を定義し、コントロールフローは複数のデータフローやサブフローをまとめて処理全体を定義したものです。フローの定義では、デバッグ機能で処理動作を確認できます。動作確認済みのアプリケーションはAdministration ConsoleからWebSphere Application Serverにデプロイします。
DWE SQL Warehousing Toolの構成とETLアプリケーション作成手順
DWE SQL Warehousing Toolでのアプリケーション構築例
では、実際にDWE SQL Warehousing Toolの機能を利用して、ETLアプリケーションを構築していきましょう。 スタースキーマでは、ファクト表とディメンション表で構成をされ、参照性制約で結ばれている必要があります。スタースキーマ構成では、ETLアプリケーションを動作させるにあたり、留意すべき点があります。
- ファクト表の外部キーが、ディメンション表に存在する必要があります。
- 親表であるディメンション表は、ファクト表よりも先にデータロードします。
今回の例では、売上分析を目的とするスタースキーマ構成のデータモデルを作成しています。ディメンション表は「商品マスター」「日付マスター」、ファクト表は「売上トランザクション」で、2つのディメンション表と1つのファクト表で構成されています。(実システムの分析要件では、「店舗」や「プロモーション」といった軸（ディメンション表）の要件が想定されます。) このデータモデルが運用フェーズにあると仮定し、差分ファイルをディメンション表とファクト表にデータロードする例を説明します。差分ファイルは既にステージング表にロードされている状態にあるとします。ステージング表とは、データの変換処理やロード準備のために設けた一時的な中間表です。今回紹介をするこの処理は、スローリング・チェンジング・ディメンションという手法で、スタースキーマデータモデルのデータ保守によく使用されている方法です。
ETLアプリケーションの構築では、開発はDesign Studio, ETLアプリケーション保守／実行はAdministration Consoleと機能分担されています。この例で説明する手順1〜4はDesign Studio、手順5〜6はAdministration Consoleでのオペレーションになります。
図は、データモデル（表情報）と処理内容を記述しています。
売上分析データモデル（スタースキーマデータモデル）
手順1：データフローの作成（ディメンション表側）
ディメンション表へのデータロードはファクト表より先に行います。売上ステージング表を確認すると、「商品マスター」に含まれていない2つの商品「パイナップル」と「レモン」があり、この商品を「商品マスター」表へロードします。図は、このデータフローを表しています。
- ソース表とターゲット表 ソース表は、売上ステージング表、ターゲット表は、商品マスター表になります。ソース表とはデータの抽出元、ターゲット表とはデータの移行先です。
- スローリング・チェンジング・ディメンション処理（変化の遅いディメンション処理） 売上ステージング表に含まれている商品で、商品マスター表に含まれない商品を抽出し、サロゲートキーを付加してロードします。サロゲートキー（代理キー）とは、実プライマリキーに対して、連番号のキー項目を意味します。商品マスター表の例では、「ばなな」の商品コードの“AA1000”に対して、商品サロゲートキー“101”が付番されています。サロゲートキーの付番は、DB2のCREATE SEQUENCE文で行い、重ならない番号で付番します。
サロゲートキーを使用すると一般的にいくつかのメリットがあります。
- プライマリーキーが複数の項目で構成されている時、サロゲートキーは数値タイプの1項目なので検索処理は高速です。
- ディメンション表に履歴情報を持てます。 例えば、Aさんは、B会社に勤めていた(サロゲートキー番号0として付番)が、現在はC会社に勤務(サロゲートキー番号1として付番)しています。同一の人物にも関わらず、2つのレコードを保持できます。
サロゲートキーの使用は、データの変換処理が必要な分だけデータロードに時間かかるというデメリットがあります。これは検索効率やデータモデルの使い勝手とのトレードオフになります。
データフロー図(ディメンション表へのデータロード)
手順2：データフローの作成（ファクト表側）
差分ファイルには、年月日「20060523」に発生をした4レコードがあります。このレコードを売上トランザクションに追加します。
- ソース表とターゲット表 ソース表は、売上ステージング表、日付マスター表、商品マスター表の３表、ターゲット表は、売上トランザクション表です。
- ファクトキー置換処理 売上ステージング表に含まれるプライマリキーは年月日と商品コードですが、ファクト表である売上トランザクション表は、サロゲートキーを用いて格納しています。よりまして、売上ステージング表のプライマリキーを、ディメンション表の日付マスターと商品マスターを参照しながら、サロゲートキーへ変換します。
データフロー図(ファクト表へのデータロード)
手順3：コントロールフローの作成
コントロールフローは、処理の流れをモデル化するもので、それぞれの演算子は、アクティビティーを表しています。定義できるコントロールフローのアクティビティーには、データフロー以外に、「イテレーター（同一処理の繰り返し）」、「WebSphere DataStageジョブの組込み」「メール（処理結果の通知）」等が含まれています。例えば、一部の処理を高速に行いたい等の要件には、並列実効性に優れたWebSphere DataStageジョブを組み込むことは有効です。
今回の例では、日々のトランザクションデータを、売上分析データモデルにレコードを追加する処理を定義しています。2つのデータフロー（商品マスターのロード処理、売上トランザクションのロード処理）を一連の流れとしています。また、データフローの処理が失敗をした場合に、エラー内容をメールで送信する処理を記述しています。
コントロールフロー図
手順4：デバッグおよびデプロイファイルの作成
DWE SQL Warehousing Toolでは、ETLアプリケーションの動作確認は、サーバー／クライアントモデルでJDBCドライバーを使用し実施します。このデバッグ機能はデータフローやコントロールフローで提供されています。動作に問題がある場合は、フローの定義に立ち戻り、プロパティーや問題点を確認します。動作確認後は、ETLアプリケーションを、WebSphere Application Serverにデプロイするためのファイルを作成します。デプロイファイルは、ETLアプリケーションのスクリプトが含まれ、指定ディレクトリーに拡張子がzip形式で作成されます。
デバッグメニュー
フローメニューから妥当性の検査、実行を選択します。
デプロイファイルの作成
データベースエクスプローラから、ETLアプリケーションを選択します。
手順5：ETLアプリケーションのデプロイ
Administration Consoleで接続先データベースのデータソースを作成します。このデータソースはETLアプリケーションが処理対象とするデータベースです。次に、ETLアプリケーションをWebSphere Application Serverにデプロイします。ここでは、Design Studioで作成をしたデプロイファイルを指定します。
データソースの作成
左側メニューからデータソースの作成を選択します。この例では、データソース RENSAIを作成します。処理対象となる表（ステージング表、マスター表、トランザクション表）は、このデータベースに含まれています。
ETLアプリケーション組込み
左側メニューからETLアプリケーションのデプロイを選択します。
手順6：アプリケーション実行とスケジューリング
デプロイしたETLアプリケーションの実行およびスケジューリングは、Administration Consoleから実施します。