Integration Servicesでデータ変換処理を制御する [SSRS with Bing Maps #3]

こんばんは。佐久間です。

「Reporting ServicesとBing MapsでWEB上のHTMLデータを可視化するレポートを作成する」

第3回目はWorkテーブルのデータに対する付加情報の追加とデータ変換処理を行います。
（目次はこちら）

 
2. データを取得・加工する：Integration Services

2-1. 抽出(Extract)
 2-1-1 HTMLからデータを抽出してTSVを作成する
 2-1-2 ワークテーブルを空にする
 2-1-3 TSVからSQL Serverへデータをインポートする
 
2-2. 変換(Transform) ←今回
 2-2-1 都道府県IDを付与する
 2-2-2 最大震度IDを付与する
 2-2-3 座標データをGeography型に変換する
 
2-3. ロード(Load)
 2-3-1 インデックスを無効化する
 2-3-2 ワークテーブルのデータでレポート用テーブルのデータを洗い替える
 2-3-3 インデックスを再構築する

 
[変換(Transform)]
 
2-2-1 都道府県IDを付与する

まずは震源データと都道府県の紐付けを行います。
ここでの紐付け判定は震源が都道府県名で始まっているかどうかです。
都道府県名で始まっていない震源についてはすべて不明扱いになってしまいますが、
データの可視化手段を公開することが主目的なので割愛します。
※より精度の高いデータにするためには名寄せ処理が必要です。

紐付け処理は前回も使用したSQL実行タスクで行います。
ストアドは下記の通り。
都道府県マスタのデータを利用してIDを設定します。

CREATE PROCEDURE dbo.Set都道府県ID
AS
BEGIN
    UPDATE dbo.Work
    SET 都道府県ID =
        CASE WHEN 都道府県マスタ.都道府県ID is null
            THEN 99
            ELSE 都道府県マスタ.都道府県ID
        END

    FROM dbo.Work
    LEFT OUTER JOIN dbo.都道府県マスタ
    ON Work.震源 LIKE 都道府県マスタ.都道府県名 + '%'
END

2-2-2 最大震度IDを付与する

最大震度IDもやることは都道府県IDの紐付け処理と基本的に同じです。
※震度マスタを作る際に用意した置換文字列（全角）のデータはここで使用します。

CREATE PROCEDURE dbo.Set最大震度ID
AS
BEGIN
    UPDATE dbo.Work
    SET 最大震度ID =
        CASE WHEN 震度マスタ.震度ID IS NULL
            THEN 99
            ELSE 震度マスタ.震度ID
        END
    FROM dbo.Work
    LEFT OUTER JOIN dbo.震度マスタ
    ON Work.最大震度 = 震度マスタ.置換文字列
END

2-2-3 座標データをGeography型に変換する

そして最後は座標データをGeography型に変換する処理です。
今回はBing Mapsとの連携を行うので地理空間データ型であるGeography型を使用します。
変換に使用するgeography::STGeomFromText()についての詳細は下記を参照してください。
※PointではなくLINESTRINGの場合のサンプルコードが記載されています。

STGeomFromText (geography データ型)

CREATE PROCEDURE dbo.ConvertPointToGeography
AS
BEGIN
    UPDATE dbo.Work
    SET 座標 = geography::STGeomFromText('POINT(' + Str(東経, 6, 2) + ' ' + Str(北緯, 6, 2) + ')', 4326)
END

設定は以上で完了です。
前回と同様に各タスクを緑の矢印でつないでデバッグ実行してみましょう。

すべてのタスクが緑に染まればデバッグもOK。

Workテーブルの座標・都道府県ID・最大震度IDにデータが入っているはずです。

ここではわかりやすくするために変換処理をそれぞれ別々のストアドにしました。
タスクも分かれているので、実行順を簡単に変更できる点がメリットです。
タスクをつなぐ順番を入れ替えるだけで処理が変わります。

ただし、個々のストアドがそれぞれにWorkテーブル中のデータ全件に対して処理を行うため、
対象データの件数が増えれば増える程処理時間も増加してしまいます。
変更と管理のし易さよりも実行効率を優先する場合にはまとめて行ってもよいでしょう。

以上で、本日の変換処理は完了です。
次回はReporting Servicesのレポートから参照する地震データテーブルにこのデータをロードします。

– おまけ –

各タスクの右クリックメニューに含まれる「無効化」を選択すると任意のタスクを無効にできます。
たとえば前回作成したタスクをすべて無効化した状態でデバッグ実行を行うと、
今回作成したタスクのみのデバッグが行えるのでちょっと便利です。
また、実運用中に特定の処理が不要になった場合にも特定のタスクのみを無効化できるので
メンテナンスにも使えます。