データウェアハウスシステム（DWH）を定義するプロセスが始まりました。 ビジネス要件に関連するディメンション テーブルの概要を説明しました。 これらのテーブルでは、何を計量し、観察し、測定するかを定義します。 次に、測定方法を定義する必要があります。
ファクト テーブルは、これらの測定値を格納する場所です。 これらは、ディメンションの組み合わせで集計可能なビジネス データを保持します。 しかし、実際には、ファクト・テーブルはそれほど簡単に説明できるものではなく、独自の風味を備えています。 この記事では、ファクト・テーブルに関する基本的な質問に答え、各タイプの長所と短所を検討します。
ファクト テーブルとは
最も一般的な意味では、ファクト テーブルはビジネス プロセスの測定値です。 これらは主に数値データを保持し、特定のレポートではなくイベントに対応します。
ファクト・テーブルの最も重要な機能は、測定値以外に粒度です。 粒度は、特定のイベントに対してどのレベルの詳細が観察されるかを定義します。 (これを、ユーザが自分のアカウントに対して非常に小さな詳細を制御できることを意味する「細粒度制御」という表現と比較してみてください)。
ファクト・テーブルの粒度が細かいほど、予測不可能なビジネス要件への対処が容易になります。 粒度が大きいことの欠点は、データ格納のためにより多くの物理スペースが必要になることです。
ファクト・テーブルの構造
一般的なファクト・テーブルは、主に 2 つの属性グループから構成されます。 縮退ディメンジョンとは、親ディメンジョン・テーブルが存在しないディメンジョン・キーです。 これらは、ディメンジョンに関するすべての重要な情報がファクト・テーブルに既に存在する場合に発生します。 例としては、さまざまなコントロール・ヘッダ番号、チケット番号、注文番号などがあります。
Measure (すなわち Additive: あらゆるディメンジョンで合計可能
メジャーと外部キー以外に、多くの技術的な列が存在する可能性があります。 技術的なカラムは、モデルの監査や低レベルのメンテナンスに便利です。 ファクト・テーブルで挿入または更新が発生したときに印を付けるために使用されるタイムスタンプは、技術的な列の一般的な例です。
汎用ファクト・テーブルを説明するために、非常に単純なスター・スキーマを見てみましょう。
このモデルでは、3つのディメンジョン・テーブルに囲まれた単一のファクト・テーブルが存在します。 ファクト・テーブルの外部キーには、以下のものがあります。
-
time_id– 時間次元テーブル (dim_time) -
product_id– 商品次元テーブル (dim_product) -
store_id– 店舗次元テーブル (dim_store) -
pos_transaction-縮退次元を参照する。 POSに関する必要な情報はこれだけなので、ここに格納すればよい。
メジャー・キー・グループの構成：
-
sales_quantity– 1つの店舗における1つの製品の1回の売上高です。 -
sales_price– 店頭で販売される商品の価格。 これは非加算の指標です。
技術的な列は1つあり、time_insertedは行が挿入された時刻を格納します。
ファクト・テーブルの概要がわかったところで、そのさまざまな種類を掘り下げてみましょう。 ファクト・テーブルには、トランザクション、定期的スナップショット、累積スナップショット、およびファクトレス・ファクト・テーブルの4つのタイプがあります。 ただし、これらの異なるファクト・テーブルの機能を詳しく調べる前に、重要な共通要素である「疎性」、つまりファクト・テーブルに格納されるデータの割合について説明します。 Sparsity は粒度に関係し、クエリ性能に影響を与えます。
ファクト テーブルの Sparsity とは
ファクト テーブルを設計するとき、その Sparsity、つまり、テーブルの行のいくつが入力されているか、いくつが空になっているか、を考慮します。 多くの基礎となるテーブルからファクト・テーブルを埋める場合、疎密を推定することが賢明です。 この推定は、ファクト・テーブルの粒度のレベルに基づいて行われます。 ファクト・テーブルが基礎となるソース・テーブルと一致する場合、基礎となるテーブルのカーディナリティを使用して疎密を計算できます。
疎密を計算するには、最初に充填率、つまり全セルに対する空でないセルの数を求めます。 充填率を計算するには、ソース・テーブルの行数を各ディメンジョン・テーブルの異なる行の数で割ります。 スパース性は、1-(f)として計算され、f = 充填率です。
例：
100,000 行のデータベース テーブルと 20,000 行のスプレッドシートの 2 つのソースから fact_retail_sale テーブルを埋めたとします。 これらの行は、年単位で入力されます。 9105>
疎性を次のように計算します。
1-(100,000 + 20,000)/(365*100*1,000) = 0.99671
行の 1% 未満が非ゼロ値なので、この事実表は疎であることがわかります。
経験則では、2% 未満は非常に疎であり、それ以上はそれほど疎ではないと考えます。
Transaction Fact Tables
これは最も単純で最も一般的なタイプのファクト・テーブルです。 このタイプの粒度は、トランザクションごとに 1 行、またはトランザクションの行ごとに 1 行です。 トランザクション・ファクト・テーブルの粒度は、空間と時間における一点です。 9105>
トランザクションが発生すると、そのトランザクションに関する広範なコンテキストが取得されます。 このコンテキストにより、ディメンション テーブルに多くの詳細が作成されるため、多くのディメンションが期待されます。
一度トランザクション テーブルに行を挿入すると、再訪されることはほとんどありません。 このため、この種のテーブルにデータを入力するための特定のテクニックを検討する必要があります。 更新がないということは、より単純な ETL (抽出、変換、およびロード プロセス) であることを意味します。
Cons: クエリにおけるパフォーマンスの問題。
A Example of a Transaction Fact Table
典型的な金融スター・スキーマをみてみましょう。 このテーブルのサンプル・データは以下のとおりです。
注: time_date は dim_time ディメンジョンに示されているように表され、技術列は無視されます。
口座所有者がお金の入金や引き出しなどの取引を完了すると、すべての行動を記録します。
この構造により、以下の質問に答えることが可能になります。 当社のビジネスでは、1 日に何件の取引を処理するのか。
Periodic Snapshot Fact Tables
Periodic Snapshotテーブルは、事前に定義された期間でのビジネスの累積パフォーマンスを記録します。 スナップショットを取るためのあらかじめ決められた間隔が重要で、毎日、毎週、毎月などです。 このタイプのファクト・テーブルでは、多くの自由裁量が与えられます。期間中のアクティビティを記述する任意の情報を組み込むことができます。 (次回の記事で説明するように、SQL にはこれを処理するいくつかの巧妙なアルゴリズムがあります)。 それは、あるスナップショットから別のスナップショットへのトランザクションを加算することだけの問題です。 トランザクション ファクト テーブルに多くの次元があるように、スナップショットには通常、より少ない次元しかありません。
Pros: パフォーマンス向上、ビジネスの縦断ビュー、物理的なコンパクトさ。
A Example of a Periodic Snapshot Fact Table
別の財務スター スキーマを考えてみましょう。 前のファクト・テーブルのデータからこのファクト・テーブルを構築することもできました。
私たちは、スナップショット日ごとに balance_amount を表示する必要があります。 すべてのアウトバウンド・トランザクションを日単位で集計し、入金タイプは加算、出金タイプは減算です。 結果は次のとおりです。
注: time_dateはdim_timeディメンジョンに表示されているとおりで、技術列は無視されます。
これで、次のような一般的なビジネス質問に答えられるようになりました。 顧客は 1 か月に何日プラスの口座残高を持つか?
Reorganizing a Periodic Snapshot Fact Table
前の例で表示されたデータで、いくつかの冗長性に気づいたかもしれません。 スナップショット・ファクト・テーブルでさえも大きすぎて、パフォーマンスが低下することがあります。 では、どうすればよいのでしょうか。 9105>
2つの新しい列、date_from および date_to を追加しました。 これらは、balance_amount が有効であった期間を表します。
このテーブルのデータは次のようになります。
balance_amountが変更されるたびに、期間を閉じて新しい期間を開きます。
date_to列には包括しない時間区切りがあることに注意してください。 データを照会する際には、これを考慮する必要があります。 また、興味深い日付として、9999年12月31日を見つけることができました。 この日付は、最後の行が最後の既知の残高であることを示している。
以下では、特定の日付 (:date) のスナップショットをクエリするために使用する SQL を見てみましょう。 そのサイズは何テラバイトにもなり、データ ウェアハウスで最も大きなスペースを占めます。 ファクト テーブルの設計プロセスの初期にエラーが発生すると、多くの問題が発生し、ウェアハウスが進化しても、解決は容易ではありません!
あるタイプのファクト・テーブルから別のタイプのファクト・テーブルへデータを再編成することは可能です。 これを行うためのテクニックについては、次回の投稿で検討します。 さまざまな種類のファクト・テーブルを使用した経験はどうですか。 新しいエキサイティングな方法でそれらを混ぜることができましたか。 行った場合は、私にお知らせください。
