ゆっくりと変化する次元

データ管理とデータウェアハウスにおいて、緩やかに変化するディメンション（SCD）とは、一般的には安定しているものの、時間の経過とともに変化する可能性があり、多くの場合は予測できない形で変化するデータを格納するディメンションです。 ^{[1]これは、顧客ID、製品ID、数量、価格などのトランザクションパラメータなど、頻繁に更新される}急速に変化するディメンションとは対照的です。SCDの一般的な例としては、地理的な場所、顧客の詳細、製品属性などがあります。

SCD 管理の複雑さに対処するには、さまざまな方法論があります。Kimball ツールキットは、SCD 属性を処理する手法をタイプ 1 からタイプ 6 に分類することを普及させました。^[1]これらは、単純な上書き (タイプ 1) から、変更ごとに新しい行を作成する (タイプ 2)、新しい属性を追加する (タイプ 3)、個別の履歴テーブルを維持する (タイプ 4)、ハイブリッド アプローチを採用する (タイプ 6 と 7) まで多岐にわたります。タイプ 0 は、属性が実際にはまったく変更されないものとしてモデル化するために使用できます。各タイプは、履歴の正確さ、データの複雑さ、およびシステム パフォーマンスの間でトレードオフを提供し、さまざまな分析およびレポートのニーズに対応します。

SCD の課題は、データの整合性と参照整合性を維持しながら、履歴の正確性を維持することです。たとえば、売上を追跡するファクト テーブルは、営業担当者と配属先の地域オフィスに関する情報を含むディメンション テーブルにリンクされている場合があります。営業担当者が新しいオフィスに異動になった場合、ファクト テーブルとディメンション テーブルの関係を壊すことなく、過去の売上レポートに以前の配属を反映させる必要があります。SCD は、このような変更を効果的に管理するメカニズムを提供します。

タイプ0のディメンション属性は変更されることはなく、永続的な値を持つ属性、または「オリジナル」と説明されている属性に割り当てられます。例:生年月日、元のクレジットスコア。タイプ0は、ほとんどの日付ディメンション属性に適用されます。^[2]

この方法では古いデータが新しいデータで上書きされるため、履歴データは追跡されません。

サプライヤー テーブルの例:

上記の例では、Supplier_Code が自然キーであり、Supplier_Key が代理キーです。技術的には、行は自然キー (Supplier_Code) によって一意になるため、代理キーは必要ありません。

サプライヤーが本社をイリノイ州に移転した場合、レコードは上書きされます。

タイプ 1 方式の欠点は、データ ウェアハウスに履歴が残らないことです。ただし、保守が容易であるという利点もあります。

サプライヤーの州ごとに事実をまとめた集計表を計算した場合は、Supplier_Stateが変更されたときに再計算する必要があります。^[1]

この方法では、ディメンション テーブル内の特定の自然キーに対して、個別の代理キーや異なるバージョン番号を持つ複数のレコードを作成することで、履歴データを追跡します。挿入ごとに無制限の履歴が保存されます。これらの例の自然キーは、「ABC」の「Supplier_Code」です。

たとえば、サプライヤーがイリノイ州に移転した場合、バージョン番号は順番に増加します。

もう 1 つの方法は、「有効日」列を追加することです。

2 行目の開始日時は、前の行の終了日時と同じです。2 行目の NULL の End_Date は、現在のタプル バージョンを示します。代わりに、標準化された代替の上限日付 (例: 9999-12-31) を終了日として使用して、フィールドをインデックスに含めることができ、クエリ時に NULL 値の置換が不要になる場合があります。一部のデータベース ソフトウェアでは、人工的な上限日付値を使用するとパフォーマンスの問題が発生する可能性がありますが、NULL 値を使用するとこの問題を回避できます。

3 番目の方法では、有効日と現在のフラグを使用します。

Current_Flag 値 'Y' は、現在のタプル バージョンを示します。

特定の代理キー(Supplier_Key) を参照するトランザクションは、徐々に変化するディメンション テーブルのその行で定義されたタイム スライスに永続的にバインドされます。サプライヤの状態別にファクトをまとめた集計テーブルは、トランザクションの時点でのサプライヤの状態など、履歴状態を継続的に反映するため、更新は必要ありません。自然キーを介してエンティティを参照するには、 DBMS (データベース管理システム) による参照整合性を不可能にする一意の制約を削除する必要があります。

ディメンションの内容に遡及的な変更が加えられた場合、またはディメンションに既に定義されているものと異なる有効日を持つ新しい属性 (たとえば Sales_Rep 列) が追加された場合、新しい状況を反映するために既存のトランザクションを更新する必要がある可能性があります。これはコストのかかるデータベース操作になる可能性があるため、ディメンション モデルが頻繁に変更される場合は、タイプ 2 SCD は適切な選択ではありません。^[1]

この方法では、個別の列を使用して変更を追跡し、限られた履歴を保存します。タイプ 3 では、履歴データを格納するために指定された列の数に制限があるため、限られた履歴を保存します。タイプ 1 とタイプ 2 の元のテーブル構造は同じですが、タイプ 3 では追加の列が追加されます。次の例では、サプライヤーの元の状態を記録するためにテーブルに追加の列が追加されており、以前の履歴のみが保存されます。

このレコードには、元の状態と現在の状態の列が含まれています。サプライヤーが 2 度目に移転した場合、変更を追跡することはできません。

このバリエーションの1つは、Original_Supplier_Stateの代わりにPrevious_Supplier_Stateフィールドを作成し、最新の履歴変更のみを追跡することです。^[1]

タイプ 4 の方法は通常、「履歴テーブル」を使用する方法と呼ばれ、1 つのテーブルに現在のデータが保存され、追加のテーブルが一部またはすべての変更の記録を保存するために使用されます。両方の代理キーは、クエリのパフォーマンスを向上させるためにファクト テーブルで参照されます。

以下の例では、元のテーブル名は Supplier で、履歴テーブルは Supplier_History です。

この方法は、データベース監査テーブルと変更データ キャプチャ技術の機能に似ています。

タイプ 5 の手法は、タイプ 4 のミニディメンションを基盤として、タイプ 1 の属性として上書きされるベース ディメンションに「現在のプロファイル」のミニディメンション キーを埋め込むというものです。このアプローチは、4 + 1 が 5 に等しいため、タイプ 5 と呼ばれます。タイプ 5 の緩やかに変化するディメンションでは、ファクト テーブルを介してリンクすることなく、現在割り当てられているミニディメンションの属性値にベース ディメンションの他の属性とともにアクセスできます。論理的には、通常、ベース ディメンションと現在のミニディメンション プロファイル アウトリガーをプレゼンテーション レイヤーの単一のテーブルとして表します。アウトリガー属性には、「現在の収入レベル」などの個別の列名を付けて、ファクト テーブルにリンクされているミニディメンションの属性と区別する必要があります。ETL チームは、現在のミニディメンションが時間の経過とともに変化するたびに、タイプ 1 のミニディメンション参照を更新/上書きする必要があります。アウトリガー アプローチで十分なクエリ パフォーマンスが得られない場合は、ミニディメンション属性をベース ディメンションに物理的に埋め込む (および更新する) ことができます。^[3]

タイプ 6 方式は、タイプ 1、2、3 のアプローチを組み合わせたものです (1 + 2 + 3 = 6)。この用語の起源については、ラルフ・キンボールがKalido のスティーブン・ペースとの会話の中で作ったという説が考えられます^{[引用が必要]}。ラルフ・キンボールは、この方式を「シングル バージョンオーバーレイによる予測不可能な変更」と呼んでいます。^[1]

サプライヤー テーブルは、サンプル サプライヤーの 1 つのレコードから始まります。

Current_State と Historical_State は同じです。オプションの Current_Flag 属性は、これがこのサプライヤの現在のレコードまたは最新のレコードであることを示します。

Acme Supply Company がイリノイ州に移転すると、タイプ 2 処理と同様に新しいレコードが追加されますが、各行に一意のキーがあることを保証するために行キーが含まれます。

タイプ 1 の処理と同様に、最初のレコード (Row_Key = 1) の Current_State 情報を新しい情報で上書きします。タイプ 2 の処理と同様に、変更を追跡するための新しいレコードを作成します。そして、タイプ 3 の処理を​​組み込んだ 2 番目の State 列 (Historical_State) に履歴を保存します。

たとえば、サプライヤーが再度移転する場合は、サプライヤー ディメンションに別のレコードを追加し、Current_State 列の内容を上書きします。

多くのタイプ 2 およびタイプ 6 SCD 実装では、ファクト データがデータ リポジトリにロードされるときに、ディメンションの代理キーが自然キーの代わりにファクト テーブルに配置されます。 ^[1]代理キーは、特定のファクト レコードの有効日とディメンション テーブルの Start_Date および End_Date に基づいて選択されます。これにより、ファクト データを対応する有効日の正しいディメンション データに簡単に結合できます。

以下は、タイプ 6 ハイブリッド方法論を使用して上記で作成したサプライヤー テーブルです。

Delivery テーブルに正しい Supplier_Key が含まれると、そのキーを使用して簡単に Supplier テーブルに結合できます。次の SQL は、ファクト レコードごとに、現在のサプライヤーの状態と、配達時にサプライヤーが所在していた状態を取得します。

delivery.delivery_cost 、supplier.supplier_name 、supplier.historical_state 、supplier.current_stateをFROM deliveryで選択し、supplierをdelivery.supplier_key = supplier.supplier_keyで内部結合します。​​​​​  
  
  
  
 
  
     

各タイムスライスにタイプ 2 の代理キーがあると、ディメンションが変更される可能性がある場合に問題が発生する可能性があります。^[1]純粋なタイプ 6 実装ではこれを使用せず、各マスター データ項目に代理キーを使用します (たとえば、各一意のサプライヤーには 1 つの代理キーがあります)。これにより、マスター データの変更が既存のトランザクション データに影響を与えることが回避されます。また、トランザクションをクエリするときに、より多くのオプションが使用可能になります。

以下は、純粋なタイプ 6 方法論を使用したサプライヤー テーブルです。

次の例は、各トランザクションに対して 1 つのサプライヤー レコードが取得されるようにクエリを拡張する方法を示しています。

SELECT 
supplier.supplier_code 、supplier.supplier_state FROM supplier INNER JOIN delivery ON supplier.supplier_key = delivery.supplier_key AND delivery.delivery_date > = supplier.start_date AND delivery.delivery_date < supplier.end_date ;​​​​​​​​​​​​​​​  
  
 
  
     
        

有効日 (Delivery_Date) が 2001 年 8 月 9 日であるファクト レコードは、Supplier_Code が ABC で、Supplier_State が 'CA' にリンクされます。有効日が 2007 年 10 月 11 日であるファクト レコードも、同じ Supplier_Code ABC にリンクされますが、Supplier_State は 'IL' です。

より複雑ではありますが、このアプローチには次のような多くの利点があります。

1. DBMS による参照整合性は現在可能ですが、Supplier_Code をProduct テーブルの外部キーとして使用することはできません。また、Supplier_Key を外部キーとして使用すると、各製品が特定のタイム スライスに関連付けられます。
2. ファクトに複数の日付がある場合 (例: Order_Date、Delivery_Date、Invoice_Payment_Date)、クエリに使用する日付を選択できます。
3. 日付フィルターのロジックを変更することで、「現在時点」、「トランザクション時点」、または「ある時点」のクエリを実行できます。
4. ディメンション テーブルに変更があった場合 (たとえば、遡及的にフィールドを追加してタイム スライスを変更した場合や、ディメンション テーブルの日付を間違えた場合は簡単に修正できる場合など)、ファクト テーブルを再処理する必要はありません。
5. ディメンション テーブルに二重一時日付を導入できます。
6. ファクトをディメンション テーブルの複数のバージョンに結合して、同じクエリで異なる有効日を持つ同じ情報をレポートできるようにすることができます。

次の例は、「2012-01-01T00:00:00」(現在の日時) などの特定の日付を使用する方法を示しています。

SELECT 
supplier.supplier_code , supplier.supplier_state FROM supplier INNER JOIN delivery ON supplier.supplier_key = delivery.supplier_key AND supplier.start_date < = ' 2012-01-01T00 :00 : 00 ' AND supplier.end_date > ' 2012-01-01T00 : 00 : 00 ' ;  
  
 
  
     
        

別の実装方法としては、代理キーと自然キーの両方をファクトテーブルに配置する方法があります。[ 5 ]これにより^、ユーザーは次の基準に基づいて適切なディメンションレコードを選択できます。

• 事実記録上の主な発効日（上記）、
• 最新の情報、
• 事実記録に関連付けられたその他の日付。

この方法では、タイプ 6 ではなくタイプ 2 のアプローチを使用した場合でも、ディメンションへのより柔軟なリンクが可能になります。

以下は、タイプ 2 の方法論を使用して作成したサプライヤー テーブルです。

現在のレコードを取得するには:

delivery.delivery_cost 、supplier.supplier_name 、supplier.supplier_stateをFROM deliveryで選択し、supplierをdelivery.supplier_code = supplier.supplier_codeで内部結合し、supplier.current_flag = ' Y 'とします。​  
  
  
 
  
     
   

履歴レコードを取得するには:

delivery.delivery_cost 、supplier.supplier_name 、supplier.supplier_stateをdeliveryから選択し、supplierをdelivery.supplier_code = supplier.supplier_codeで内部結合します。​​​​  
  
  
 
  
     

特定の日付に基づいて履歴レコードを取得するには (ファクト テーブルに複数の日付が存在する場合):

delivery.delivery_cost 、supplier.supplier_name 、supplier.supplier_stateをdeliveryから選択し、supplier ON delivery.supplier_code = supplier.supplier_code AND delivery.delivery_dateをsupplier.Start_Date AND supplier.End_Dateで内部結合します。​​​​​​​​​  
  
  
 
  
     
       

いくつかの注意事項:

• 関係を作成するための一意のキーがないため、DBMS による参照整合性は不可能です。
• 上記の問題を解決するためにサロゲートと関係を結ぶと、特定のタイムスライスに結び付けられたエンティティが完成します。
• 結合クエリが正しく記述されていない場合、重複した行が返されたり、誤った回答が返されたりする可能性があります。
• 日付の比較がうまく機能しない可能性があります。
• 一部のビジネス インテリジェンスツールでは、複雑な結合の生成が適切に処理されません。
• ディメンション テーブルを作成するために必要なETLプロセスは、参照データの各個別項目の期間が重複しないように慎重に設計する必要があります。

異なる SCD タイプをテーブルの異なる列に適用できます。たとえば、同じテーブルの Supplier_Name 列にタイプ 1 を適用し、Supplier_State 列にタイプ 2 を適用できます。

• 変更データキャプチャ
• 時間データベース
• ログトリガー
• エンティティ-属性-値モデル
• マルチテナント
• 運用計画
• ビジネスプロセス管理
• データ要素