SQL Server 2008 データウェアハウスシナリオ Tips and Tricks Part 3
マイクロソフトの植田です。
引き続き「大規模データウェアハウス」シナリオ検証で得られたTipsや注意点についてご紹介していきたいと思います。
今回は「緩やかに変化するディメンジョン」への対処方法について説明したいと思います。
簡単に「緩やかに変化するディメンジョン」とは何かについて触れたいと思います。DWHシナリオでは、ディメンジョンの多くが時間の経過に伴って変化します。多くの場合それらの変化はファクトデータの変化に比べて緩やかで、徐々に起きる変化を反映するディメンジョンのことを、「緩やかに変化するディメンジョン」と呼びます。この時、ディメンジョンテーブルに生じた変化にどのように対処するか、によっていくつかのパターンがあります。主なパターンは以下に分類されます。例えば以下のようなレコードがあり、場所の移転に伴って店舗名称を「代々木店」から「新宿店」に変更するケースについて各パターンを考えてみます。
店舗テーブル
店舗コード |
店舗名称 |
エリアコード |
100 |
代々木店 |
1 |
1. タイプ1
変更があったデータを、新しいデータで上書きする方法です。変更後のレコードは以下のようになります。
店舗コード |
店舗名称 |
エリアコード |
100 |
新宿店 |
1 |
2. タイプ2
変更前と変更後の両方のデータを保持し、レコードの有効/無効を判断する列を追加する方法です。レコードの有効フラグ列を追加するパターンと、レコードの開始/終了日時を追加するパターンがあります。以下の例では、開始/終了日時の列に加え、一意性を表す代替キー（サロゲートキー）の列が追加されています。
店舗ID |
店舗コード |
店舗名称 |
エリアコード |
開始日時 |
終了日時 |
1 |
100 |
代々木店 |
1 |
2005/10/1 |
2008/10/31 |
101 |
100 |
新宿店 |
1 |
2008/11/1 |
3. タイプ3
変更のあったデータの履歴をレコード内に保持し、変更があった日付を追加する方法です。レコードのサイズにより、保持できるデータ履歴の数が制限されます。複数の列が更新されるシナリオではレコードサイズが大きくなります
店舗コード |
店舗名称（現） |
店舗名称（履歴） |
エリアコード（現） |
エリアコード（履歴） |
更新日時 |
100 |
新宿店 |
代々木店 |
1 |
1 |
2008/11/1 |
4. タイプ4
ディメンジョンテーブルは新しいデータで更新し、変更のあったレコードの履歴を別のテーブルで管理する方法です。このようにすると、保持できる履歴データの制限がなくなります。
店舗テーブル
店舗コード |
店舗名称 |
エリアコード |
100 |
新宿店 |
1 |
店舗履歴テーブル
店舗コード |
店舗名称 |
エリアコード |
更新日時 |
100 |
代々木店 |
1 |
2008/11/1 |
この他、全く対処しない場合（タイプ0）や、タイプ1, タイプ2, タイプ3を組み合わせたタイプ6（ハイブリッド）と呼ばれるものがあります。どのタイプを使用するべきか、についてはビジネス要件に依存します。そして、変化するディメンジョンへの対処方法によって、ディメンジョンテーブル、および、ファクトテーブルの設計に影響があります。例えばタイプ2を使用する場合はディメンジョンテーブルに、代替キーの列、および、開始/終了日時の列を追加する必要があります。加えて、ファクトテーブルのデータについても代替キーに変更する必要があります。上記の例では、ファクトデータの「店舗コード」を「店舗ID」で置き換える必要があります。
SQL ServerのIntegration Servicesを使用すると、上記のディメンジョンテーブルの変更をウィザードを利用して設定することができます。また、ファクトテーブルにおける代替キーとビジネスキーの変換も、「データフロータスク」において行うことが可能です。これらの設定方法に関する詳細は、公開予定のホワイトペーパーにて説明する予定です。今回、検証したシナリオでは変化するディメンジョンに対して、タイプ1とタイプ2を組み合わせたハイブリッド方式を採用しました。実装に関してはホワイトペーパーにて詳しく説明させて頂いているので、ここではどのようなビジネス要件が満たされるのか、簡単な例を見ていきたいと思います。
例えばファクトデータとして以下のようなレコードがあるとします。
売上テーブル
|
ID |
ID |
ID |
ID |
||||
|
2007/12/23 |
1000235 |
302 |
50894 |
1 |
2024 |
200,000 |
1 |
|
2007/12/23 |
1000236 |
211 |
40031 |
2 |
3030 |
150,000 |
2 |
|
2007/12/24 |
1000237 |
512 |
8343 |
1 |
1113 |
320,000 |
2 |
|
2008/12/1 |
2100534 |
110 |
25211 |
101 |
1113 |
220,000 |
1 |
|
2008/12/1 |
2100535 |
25 |
76763 |
102 |
2350 |
150,000 |
1 |
ディメンジョンを表すテーブルとして前述の店舗テーブルを使用する場合、テーブルスキーマは以下のようになります。
店舗テーブル
店舗ID |
店舗コード |
店舗名称 （現） |
店舗名称 （履歴） |
エリアコード（現） |
エリアコード（履歴） |
開始日時 |
終了日時 |
1 |
100 |
新宿店 |
代々木店 |
1 |
1 |
2005/10/1 |
2008/10/31 |
101 |
100 |
新宿店 |
新宿店 |
1 |
1 |
2008/11/1 |
NULL |
上記のファクトデータを店舗ごとに集計する場合、移転があった店舗をどのように集計するかが問題となります。分析観点により複数の集計方法がありますが（例えば、移転の前後で別々の店舗として売り上げを集計する、または、同一の店舗とみなして集計する）、最も多い要望は、「移転の前後で分けた場合と、同一店舗とみなした場合の両方の集計を行う」というものです。この要望は上記の店舗テーブルの「店舗名称（現）」、「店舗名称（履歴）」をディメンジョンとする事で実現できます。
集計例
|
年度 |
店舗名称（現） |
店舗名称（履歴） |
売上合計 |
|
2007年度 |
新宿店 |
代々木店 |
40,782,050,000 |
|
2008年度 |
新宿店 |
代々木店 |
25,269,110,000 |
|
新宿店 |
36,093,354,000 | ||
|
合計 |
61,362,464,000 |
どのような観点に基づいてファクトデータを分析するかによって他にも方法があると思いますが、ここでは一例を取り挙げさせていただきました。ディメンジョンの変更に対して、どの方法を用いるのが良いかはディメンジョンのデータ特性、分析観点によると思いますが（例えば、顧客テーブルにおける名前の変更は集計に影響を当たる可能性が小さいのでタイプ1で処理する等）、今回説明させていただいた内容が何かの参考になれば幸いです。