The Data Warehouse Toolkit: 15 章
久野靖∗

2004.7.27

1• 業界の統合整理←グローバル化、規制緩和、相互会
社からの転換Insurance□ ここまでに出て来た概念を総活用→損保の DWH のケース

• 市場 (顧客) も変化

スタディ□ ともかく情報が重要な資産、的確な情報に基づく迅速な□ 背景分析→要求分析→バスマトリックス→次元モデル

応答を各部署とも指向□ 扱う概念:□ 業務システム/プロセスで必要なデータは既に取られて• 要求駆動アプローチによる次元デザイン

いる (保険会社では何トンもの業務データが提供されて• バリューチェーンいるのが普通)。

• バスマトリックス• ただしそれらは統合されていない。

• トランザクション、定期スナップショット、累積スナッ• 政治判断部門とデータ処理部門の間には高い壁。商

プショット

品、顧客、販売チャネルそれぞれごとにまったく違• 4 ステップのデザインプロセス

うデータソース。

• 次元のロールプレイング• 業務システム上では 1 人の顧客が自動車、家屋、物• SCD(Slowly Changing Dimension) の扱い品の各部門でバラバラに 3 回登録されていたりする• 大きく、頻繁に変更される次元→ミニ次元• これまではそれでよかったがこれからはデータを共有
しないと…→大量の不統一かつ冗長性を含んだデー• 多値を持つ次元属性

タを扱う必要• 縮退次元 (degenerate dimensions)• データの流れを追跡するための追跡次元□ ユーザはデータに容易にアクセスできなかったという問• ビジネスの場面によって大幅に属性や fact が違うよ
うは非均質な製品

題→部門ごとにコンサルタントを頼んでデータ抽出を行っていた→バラバラ• 次元や fact の conformance• 経営陣は部門ごとに同じデータが違う値だったりす
るので問題を認識• 複数のビジネスプロセスの尺度を連結する連結ファクトテーブル• バラバラのデータウェアハウス群では長期的にはやっ• factless fact tableて行けない• 次元モデル構築におけるアリガチなミス□ 新しく CIO を雇用し、従来のデータ群を統合した新世界を作るよう指示

2Insurance Case Study• CIO に効果的な情報統合に必要な権限を付与□ $5,000,000,000 規模の損保 (自動車、家屋、個人財物)• CIO は企業内の大量データを迅速に利用可能とする□ 支払、field operations、保険計理、ファイナンス、マーケティング各部門の代表者、役員にインタビュー

ビジョンを策定• DWH チームを結成してビジョンに応じたシステムを設計/実装開始• その結果、業界の流動的な状況を把握 (←新規参入←ネット損保等)□ 経営陣の主張→業務主導から顧客主導のシステムへの∗ 筑波大学大学院経営システム科学専攻
転換1• CIO もこれに呼応

2.2

Draft Insurance Bus Matrix• 社員も「特定用途向けデータ」から「全社でのデー□ 以上のインタビュー内容にもとづき、バスマトリクスをタ共有」に描いた (図 15.1)• 従来のタコツボから離れ、自分の業務も含めた全社□ 次元はコアとなるものだけに絞って描いた (全部描くとデータにサマリーレベル、詳細レベルでアクセス可100 以上になる)
能なシステムへの指向

2.1

3

Insurance Value Chain

Policy Transaction□ 保険業のバリューチェーンは短く単純であるように見 □ マトリクスの 1 行目→契約の作成/変更
える• 契約とは、保証範囲の集まりで、顧客に販売されるもの□ 契約→保険料収入→支払処理• 保証範囲が 1 つの「商品」に相当。

• これらの各プロセスをよりよく理解したい• 家屋→火災、水害、盗難、過失、…。自動車→総合• 契約決定、支払処理などのトランザクションを詳細保証、事故、無保険補填、過失、…。に分析したい• 保険対象 (家、自動車、…) は契約で厳密に規定。1• 期間ごと、対象物品ごと、地域ごと、人口構成ごと、つの対象が複数の保険でカバーされることも、1 つの販売チャネルごとの収支を分析したい

保険が複数の対象をカバーすることも、ともにある。
• 企業全体レベルでも、営業対象ごとの個別にも、分析したい□ 代理店が保険を販売する祭に、保険計理人がその保証対象についての条件に応じた保険料率を決定し、引き受け□ 保険会社は当然、他の関連業務にも関わっている。保険人が承認した上で契約料の運用、HRM、金融、購買、等。ここではコアビジネ□ オペレーショナルシステムは次のトランザクションをス部分のみを対象。

収録□ バリューチェーンの始点である契約について→契約トランザクションを 1 つのビジネスプロセス/ファクトテー

• 契約の新規作成、変更、キャンセル (+理由)

ブルとして扱いたい• 対象物に対する保証の新規作成、変更、キャンセル(+理由)• 場合によってはこれでは単純化しすぎで、ファクト• 対象物の価値見積り、見積り減額 (+理由)

テーブルを分割したいかも→ 3 章にこのあたりの議論• 引き受け契約、引き受け契約の減額 (+理由)□ 各契約による保険料収入を月単位で計算したい□ DWH 側の粒度も契約トランザクション単位、できるだけ• 物品販売とちがって、契約=収入ではない。契約→以後毎月の収入の予定

コンテキスト情報も収録して次元モデル分析できるように• さらに顧客は保険料を前払いして割引を受けたりす• 次元の例→契約日付、発行日付、顧客、担当社員、保る (雑誌販売などでも定期購読+割引の処理が必要に証範囲、保証対象、契約番号、トランザクション種別。

なったりする)• 均等は保険期間に渡って収入を分配していく必要• 契約トランザクションンからそのつど毎月の収入を計算するのは遅いだけでなく複雑すぎて困難

3.1

Dimension Details and Techniques□ このスキーマの次元について、本章までに学んだことの• →契約トランザクションファクトと保険料スナップ発展も含めて検討。

ショットファクトの両方が必要

2

3.2

□ 6 章でやったように、大きな次元で属性変更を追跡する

Dimension Role-Playingには、変更が起こる部分をミニ次元として分離するのが□ 契約トランザクションには 2 つの日付→契約が運用シスよいテムに投入された日付、契約が発効する日付• ミニ次元は fact table に別の代理キーを通じて結合□ 契約者 (顧客) は複数の人かも→例:本人+配偶者、企業による契約。

• 属性を参照したりクェリーするときに性能の上では負担になる• 契約者次元は非常に大きいかも。5 億の契約で、数百万人が関与とか。

• 更新の時も面倒だが、あらかじめ可能な組合せをす

3.3

べて入れてしまっておけるなら簡単

Slowly Changing Dimension

3.5□ 保険会社→次元の変更履歴の追跡は重視□ 多値次元→ 9 章で 1 つの口座に複数の顧客を対応させた。13 章で患者に複数の診断を対応させた。

□ TYPE 1 →上書き更新。たとえば「誕生日」(変更=誤り修正)□ ここでも 1 つの契約に複数の契約者を対応させるのは同□ TYPE 2 →新しい行を新しい代理キーで作成して更新。
最も普通。ZIP コードの変更など。

様にできる□ 別の例として、企業顧客にその企業の分類を対応させることを考える• 古い fact は古い ZIP コードを参照する。新しい fact

• 企業顧客には 1 つ以上の産業分類コード (SIC) が対応

は新しい代理キーを使うから新しい ZIP コードを参照する。

• 13 章の診断グループと同様、SIC グループブリッジテーブルを作ってグループ情報を収録。顧客次元の• 柔軟性があり強力だが、弱点は ETL に負担を掛けること

outrigger に。

• また行が増えて行くということも問題になる場合が• fact table の数値をどの SIC コードについても重みつき集計可能 (または重みを使わない場合 impact

ある→これが問題ならミニ次元を利用 (少しあとでこれについても復習)□ 例:Multivalued Dimension Attirbutesreport)

契約者のセグメント分類を行っているとする。時• SIC コードがない顧客→ SIC 次元に「Unknown」を入れておくとともにセグメント分類は詳細化されたりする• 以前の分類で分析したいことも、最新の分類で分析□ 保証範囲次元→大きな保険会社では 1 つの種類の保証対したいこともある

象について、数十〜数百の保証範囲が選べる• TYPE 3 →「旧分類」を現在の分類と別に残すことで両方に対応• 保証上限や免責については、fact table の数値とするのがよい。たとえば住宅火災保険では上限は住宅• まとまった分類見直しがあった場合などに有効の現在価格だが、このような連続数値は fact であり• 2 より多くのバージョンを追跡したり、複数属性につ次元の属性ではないいて適用しようとすると複雑になりすぎる□ 保証対象次元は 1 つの対象物につき 1 行→契約者次元より大きいのが普通→ミニ次元化を考える

3.4

Minidimensions for Large or Rapidly
Changing Dimensions• テキストによる記述は fact table より次元側に入れたい。複数の決まった記述から 1 つ選ぶようにす□ 契約者次元は百万行くらいとかの巨大な次元になるれば、集計分析しやすい。完全な自由記述だとあま□ 契約対象物次元も同様 (契約者が 1 個以上の対象物に対り役に立たないして契約)□ 社員→そのトランザクションを扱った社員。契約作成、保証範囲作成については、代理店が対応。評価について□ これらの一部の属性については正確な内容追跡が必要←

は、評価担当者。引き受けにについては引き受け人。

契約時、契約変更時、支払請求時に必要

33.6• アプリケーション側で種別に会った次元テーブルを結合する必要

Degenerate Dimension□ 契約番号→契約書のヘッダにある必要なものをすべて他□ この方法では fact table は専用化しないでよいことにの次元に収納してあれば縮退次元でよい

注意。次元だけ新しく追加している。論理的には次元の• 契約に関する各種事項 (契約者、日付、保証範囲、…)
は契約 fact table に入れ、契約次元に入れたくは属性を増やしているものと考えればよいない。

3.9• ただし場合によっては契約書に対応する fact が残る場合も。例: 引き受け人が契約書全体を通じてのリスク評価。→縮退次元でなくなることも。

Alternative (or Complementary) Policy Accumulating Snapshot□ トランザクションの累計効果を把握するための累積ス
ナップショットの用法についてあとひとこと。

□ 契約トランザクション種別次元は「種別×あり得る理由」→ごく小さい行数 (100 程度まで) で済むはず□ このケースでは fact table の粒度→ 1 つの保証範囲/保証対象が 1 行

3.7• 関連する日付:

Audit Dimension

見積り日、評価日、引き受け日、発効日、更新日、満了日。

□ transaction fact にキーを入れて監査次元にリンクで• 他の次元についても同様のことがいえそう (トランザクション種別次元は除いて)きる。8 章で述べたように、監査次元にはそのデータがどこからいつどのように取り出されたか、用いたソフトウェアのバージョンなどの情報を格納。

□ →累積スナップショットは拡張された fact set に対応することになるだろう□ 図 15.2 に契約トランザクション次元を示す。典型的なトランザクション粒度ファクトテーブル。

• 累積スナップショットは契約トランザクションに関す• ほとんどすべてがキーから成っている

る主要なマイルストンの情報を収集するのに有効• 契約、保証対象、保証品目などのライフスパンにわたる追跡• トランザクション単位なので詳細に渡る分析ができる• 数値データは「契約金額」。その解釈は契約種別に応じて変わってくる。

• ただしすべてのトランザクションの情報を収録はしない←例外的なイベントや通常のプロセスから外れ• さまざまな種類の契約が混ざって含まれるため、もっるものは入れないと特定的なラベルをつけることはできない。

• その代わり、プロセス間の遅延時間などがはっきり分かる• 逆に種別が増えたとしてもスキーマの変更は必要としない

3.8

4

Heterogeneous Products

Policy Periodic Snapshot□ 契約トランザクションスキーマ→広い範囲の問い合わせ□ 企業全体を通じた視点は欠かせないが、ユーザは自分のに回答可能

受け持ち種別固有の事項も見たい• しかし、大量のトランザクションが流れているため• 保険の場合、対象によってかなり扱う情報が違う (住「ある時点での資産価値」のようなものは把握しづ

宅保険と自動車保険、個人所有物、損害保険、など)• ここまでに述べたようにそれらを統合して扱えるが、固有の事項もやはり追跡したい• 9 章でやった「非均質製品」の手法を適用→図 15.2

らい• 情報自体はトランザクションにあるとしても、それを最初から累計して来ないと現時点の値にならない• KPM をこのようにして毎回計算してくるのは論外□ 自動車対象の固有情報を扱う部分を図 15.3 に示す□ トランザクションテーブルと対で累計テーブルも作る。

• 各種の対象物ごとに専用の次元テーブル (保証対象、保証範囲ともに) を用意
4

この場合月毎の保険金スナップショット。粒度は各月ごと、契約範囲、契約対象ごとに 1 行。

4.1

□ 一括入金が入って来るとトランザクション粒度と毎月のConformed Dimensionsスナップショットの組合せが必要。どちらか片方だけで□ もちろん、新しいテーブルでも次元はできるだけ再利用済ませられるようには普通できない。

(共通化) する
• 新しいテーブルの次元はトランザクションテーブル

4.4と同一か、または部分集合に□ スナップショットに特定業務むけデータを含めることも• 契約者、契約対象、契約範囲などの次元は同一• 日付の代わりに月次元に (conformance はある)

必要

• 担当社員については月累計では追跡しなくてよい• その特定業務のものでない行についてはその属性はnull → null だらけに

• 代理店については入金に関わるので残したい• トランザクション種別次元は不要 (月単位の粒度で関係ない)• その解決策としては、スナップショットを業務ごとに• 代わりに status 次元を作って「新規」「キャンセル」• →コア (共通) スナップショット+各業務のカスタム分ける等を収録

4.2

Heterogeneous Products Again

スナップショット群• カスタムスナップショットにはコアのデータもコピーで入れるか、または 9 章のようにキーを使ってコアConformed Facts

テーブルに結びつける□ fact についても conform する必要

• 1 つの fact が複数の fact table に現れるなら、整合性のある定義やラベルを持つ必要 (後で例)

4.5□ 自動車だと 1 契約に複数の契約運転者があるかも→多値• 逆に同じでないものは違う名前にすることも重要

4.3

Multivalued Dimensions Again次元• 図 15.5 のようにブリッジテーブルで結びつけて重み

Pay-in-Advance Metrics

つけ計算□ マネジメントは「いくら分収入があった」だけでなく「い• 変更に対応するためブリッジテーブルには開始日/終了日を入れるくら分契約がとれたか」も関心• この場合、トランザクションを累計すれば分かるわ
けではない

5

• 顧客が契約を結んで契約金を払ったとしても、収入はサービス提供とひきかえ

More Insurance
Background

CaseStudy• 保険の場合は収入は (キャンセルされない限り) 毎月 □ 保険業には暗〜い側面もある→支払請求と損失保険料として入る□ 支払請求者 (契約者でも新規の第 3 者でもあり得る) が• 正確な金額はオペレーションシステムと同じ計算規支払請求→当然、契約内容に基づいて則で計算しなければ分からないがひどく複雑→オペ
レーションシステムに計算してもらえば済むが• 請求があった場合、調査を (請求者、契約者、その他に対して) 行う→トランザクション群の発生。複雑な□ 図 15.4 のように、保険料収入については「契約額」とものでは複数の外部専門家に判断を求めたりもする「入金額」の 2 つを含む

• だいたいは調査後、支払を行う→支払先は第 3 者の• たとえばある年契約を 1/1 に$600 で取ったとすると、1 月は契約額は$600 だが入金額は$50(つまり 1ことが多い。医者、弁護士、修理工場。請求者に直月ぶん)。2 月は契約額$0、入金額は$50。3 月に契約

• 大きな保険会社では 1,000 人以上が請求に対する支キャンセルしたら、入金額は$50、契約額は-$450。

払いを行える→どの社員がその請求を担当したかも接支払うこともある

それぞれ記録5□ 保証対象物は支払い後に保険会社の所有物となる場合が □ 図 15.6 のマトリクスを更新してみると、契約についあるて設計していたときの次元が多くそのまま利用。日付(2role)、社員、トランザクション種別。
• たいていはジャンクの価値→入金したらその請求の□ あたらしい次元ももちろんある (図 15.8)。
会計に入る• 請求次元→請求内容をコード化。契約範囲、契約品□ 支払い完了後、請求は完了→トランザクション群は完結目に対応つけられる必要。

• もめた場合はさらに請求があったり裁判になって請求• 請求者は通常は個人。第 3 者は目撃者、専門家、支払い受領者など。これらは「汚れた次元」。きれいにがまた変わる→収支もまた計算し直しに→どれくらい、どんな場合にこれが起こるかなども重要なデータ識別し追跡するのは難しい。悪意ある受領者は他の請求に関係していることを同定されないように行動□ 係争中の裁判や反訴が決着したらいくら戻るかなどもする。

計算□ 請求データについても非均質な製品と同様の手法が有効• これらの権利をまとめて専門家に売却して換金する場合も (subrogation) →そういうトランザクションとして記録

7□ 個別の記録に加えて請求の全体像も知りたい。請求がClaims Accumulating Snapshot□ トランザクションをきっちり押えていても回答するのがあってから支払いまでに何日か→請求処理の効率が分難しい問い合わせがいろいろあったかる• 請求の現時点での収支などもこの類

5.1• 毎日、その日までの収支を累計スナップショットとして計算

Updated Insurance Bus Matrix□ 請求についても分かったので図 15.1 のマトリクスを更• 粒度は契約×保証範囲×保証対象×請求の異る組合

新→図 15.6

せごとに 1 行• 多くの次元は再利用。請求、請求者、第 3 者を追加。
□ マトリクスの詳細度で苦しむかも→計画段階では抽象レベルの高いところに留まるのがよい• 請求が開始されると行ができて決着するまで順次更新されていく□ 図 15.9 のように多くの次元は互換性があり再利用。こ
こではマイルストン間の進捗が分かるように細かく記録。

• 1 つのビジネスプロセスの行から複数の fact tableが作られるかも• status もすぐ分かるように記録。
• トランザクションレベルの次元である社員、請求者、□ 実装設計段階まで来たらマトリクスの一部を取ってもっ第 3 者、トランザクション種別などは除外。と詳しく堀り下げる→図 15.7

□ 長期間 (何年も?) 掛かる請求の場合は累計スナップショッ

6

トでなく月毎定期スナップショットのようにするかも。

Claim Transactions• 粒度は現在開いている請求 1 つにき 1 行□ 請求処理から次のようなトランザクションが発生• fact は数値で加算可能なものでその月に属するもの。

• クレーム開始、再開、終了

請求金額、支払い金額、留保の変更額。

• 留保設定、留保再設定、留保終了□ 場合によっては 3 種類 (トランザクション、累積スナッ• ジャンク見積り、ジャンク歳入プショット、定期スナップショット) すべて作ることにな• 調査員による調査、インタビューるかも。

• 裁判開始、裁判終了• 支払い実行、支払い受領• 請求の下取り6

8

Policy/Claims
Snapshot• 記述テキストは dimension table にConsolidated• そこから先は個々に。測定ぽいものは fact、記述ぽいものは dimension□ ここまでで契約と請求について (スナップショットまで• コメントを fact table に残さないように含めて) はっきりしたが、利用者は収支が知りたい□ 誤り 9:• それぞれの集計をそのつど差引することもできるが…

ト量を制限する• 保険料収入と請求による支出を合わせた fact table• 次元テーブルの量はたかが知れているを作れる (図 15.10)。月単位スナップショットなので• 使いやすい DWH のためにちゃんと読めるテキストを使おう

次元も少しすくなく→ consolidated fact table(7 章)• 次元テーブル中のテキストはユーザインタフェースのため重要• 基本的なモデルがそれぞれ出来たあとで作るのがよい

9□ 誤り 8:

Factless Accident Events• 最下位レベルを指定すれば全部決まる

factless fact table として表せる→図 15.11• ユーザは階層構造については分かっている□ 被害者→事故に遭遇した他の人 (同乗者、歩行者など)• バラバラにするのではなく素直に見せることが大切(DWH はプレゼンテーションのためのもの!)• 車 両 → 自 動 車 と 関 わ り の な い 人 で あ れ ば「NoVehicle」• 複数の階層が独立にあってもいっこうに構わない• 被害関係→関わり方を表す□ 誤り 7:• 事故カウント→「いつも 1」集計に便利

次元属性の変更を追跡することを考えない• ユーザは次元属性の変更による影響を知りたいと思う (意外かも知れないが)□ factless fact table を使うことで複雑な事故でも収録できる• 最新版だけ見せておけば満足だろうというのは大間
違い• 請求者や被害者が複数の場合、多値次元で増やすことも→ 1 事故 1 レコードに保てるという利点• SCD の 3 つのタイプを学んだ。タイプ 1 のみに頼らないこと• 一群の属性が激しく変化→ためらわずにミニ次元化すること

Common Dimensional Modering Mistakes to Avoid• どれくらい変化するかは最初からは分からない□ ここまでで次元モデリングの技法に関する章はおしま□ 誤り 6:

い→デザイナはどこから先は行くべきで*ない*かにつ

性能問題には常にハードウェア増強で対処する• 集合化、サマリーテーブルの 2 つが性能改善には極めて有効いて。

• ここまでは「何をやるのが*いい*」という positive• 多くのクェリーツールは集合化の機能を組み込んでいるな話だった• なのでここでは「*やらない方がいい*」話• 索引つけ、DBMS ソフトの増強、メモリ増強、CPU 増強、並列度の増強などバランスをとって進めるべき• 例によって重要でない順 (でも最初のでも失敗したらダメージあるよ)□ 間違い 10:階層/階層レベルを複数の次元として分割する• 階層→多対 1 関係の連続したもの□ 自動車事故の場合、関係する車両、人すべての関連を10

領域節約のため次元テーブルに入れるテキス□ 誤り 5:

実務システムのキーやスマートキーを facttable に入れて join に使う

制約/グループ化のためのテキスト属性をfact table に入れる

• 初心者は全部次元テーブルに入れて代理キーで連結とかいう考えに抵抗がある• 数値は fact table に7• そうすると実務システムのキーや日付文字列をキーに使ってしまう• そうしうると必ず後でいろいろ醜い問題に遭遇する。
代理キーを使おう□ 誤り 4:fact table の粒度を最初に定義せず、後で選択を守らない• 次元モデル→ビジネスプロセスが生成する基本デー
タから始めるべき• 粒度をまず決める。最も原始的な細かいレベルを選ぶことで抵抗を排除• 測定値をその粒度にあった次元で囲む• 粒度に忠実であることが次元モデルの心臓• 小計とかのように粒度に合わないものを fact tableに入れるとまずい (一見よさそうでも集計すると多重カウントになるしいいことがない)□ 誤り 3:

特定のレポートに基づいて次元モデルを設計する• 次元モデルはそのレポートとは独立なはず。
• あくまでも計測プロセスに基づいたものが次元モデルであるはず。
• 各レポート向けに数百ものテーブルを作ってしまった設計チームの悲劇□ 誤り 2:

ユーザが最下位レベルの原始データを正規形で検索すると思うこと• 最下位レベルのデータは基本だが、ユーザはそれをそのまま見るわけではない。
□ 誤り 1:

複数の fact table 間で fact や次元の互換性を保たないこと• ここまで読んで来て孤立したテーブルを作るようではしょうもない• 複数のソースから「収入」が取れて来るならそれは別の名前にして次元的に適合させること→ fact の互換性

• 次元が適合しいればこそ複数の fact table を結合して分析ができる。これがとにかく基本。

11

Summary□ これで次元モデリングの用語もツールも分かりましたよね?

8