DB Stories

DBに関する過去、現在、未来の話題をプロフェッショナルの視点で紹介

ファクトテーブル(2)

異なるタイミングでのファクト

同時に発生しない複数のイベントが存在する場合、それらは異なるプロセスであると言えます。それが一つのファクトテーブルで扱われるとき、それぞれのプロセスを分析する際に混乱を生みます。個別のファクトテーブルで扱う方が無駄な混乱を生じさせることがありません。

このことを具体的に考えてみます。セールス部門における業務要件を以下のように仮定します。

  • 「受注量」を「日付」「顧客」「製品」で分析する。
  • 「出荷量」を「日付」「顧客」「製品」で分析する。

この時、「受注量」「出荷量」がファクトテーブルで、「日付」「顧客」「製品」がディメンションになります。ディメンションはデータの最小粒度を示しています。

これら二つは同じディメンション項目を持っていますが、この2つのファクトは同時に発生しません。たとえば、ある日に顧客が注文を行ったとしても、そのタイミングでは出荷処理は行われません。受注と出荷は同じプロセスではないということを意味しています。

一つのファクトテーブルにおける問題点

以下のスタースキーマは一つのファクトテーブル(sales_facts)を保持していて、受注量(quantity_ordered)と出荷量(quantity_shipped)があります。ディメンションテーブルについての項目は省略しています。受注と出荷に関するデータ粒度は「日付」「顧客」「製品」で同一です。このことは、「受注と出荷」「受注もしくは出荷」という分析を可能とします。このことが問題点となるケースについて解説します。

f:id:good-value:20170529230003p:plain

このスタースキーマが受注と発注の双方で利用されるという点を具体的に理解するためには、テーブルデータを見る必要があります。テーブルデータについて左側の3列はディメンションテーブルにひもづく外部キー(FK)です。このキーの値を見るとディメンションテーブルとの関連が想像できると思います。

6つの行のデータは同じ顧客(customer_key=777)についてのデータとなります。以後、顧客(777)と表記します。初めの3行については(day_key=123)となっており、同じ日付のデータを意味しています。以後、日付(123)と表記します。日付(123)において、顧客(777)は3つの異なる商品(product_key=111,222,333)を注文しています。

この受注に関する3つの行はジレンマを生みます。出荷量(quantity_shipped)列には何を設定すべきかという点です。この3つの商品に対して日付(123)に出荷はありません。つまり、次の2つの対応が考えられます。「値をNULLとする」「 値を0とする」ということです。ここではさしあたり、受注が行われた日に出荷が行われない場合は0(ゼロ)を設定することにします。

次の2行(4行目と5行目)については日付(456)のデータとなります。この日、商品(111,222)についての出荷が行われています。反対に言うと、顧客(777)についての受注は行われていません。この時、0を受注量(quantity_ordered)にセットします。最後の1行(6行目)の日付(789)について、商品(222)の出荷が行われています。

この状況で、顧客(777)の分析をこのファクトテーブルを元に行うとします。この時、以下のレポートを出力するための検索を実行します。この時、ファクトテーブルのキー(FK)を元に集計を行うのではなく、ディメンションテーブルのキー(ナチュラルキー)で行われる必要があります。商品コードもしくは商品名が集計の単位として使われます。

一つのファクトテーブルで2つのプロセス(ここでは受注と出荷)を記録する場合、誰かが一つのプロセスについて分析しようとした際に問題が発生します。 今回の例において「出荷」分析を想定すると、レポートの最終行で問題が生じます。レポートの集計期間において商品(333)の出荷が行われていないにもかかわらず、レポート上は出荷量ゼロ(0)として表示されます。より大規模なレポートの場合は、このゼロが多く含まれることになります。このことは利用者(エンドユーザー)に「なぜ商品(333)について表示されているのか」という混乱を生じさせます。商品(444)についても出荷をしていないがなぜ商品(333)だけ表示されているのかというのも疑問を生じます。

f:id:good-value:20170529230023p:plain

このレポートに商品(333)が含まれているのは次のように容易に説明可能です。レポートの作成期間に受注があったからです。受注があってファクトテーブルの行が作成されるためです。受注当日に出荷が行われなかったため、出荷量はゼロと記録されています。この説明は受注に関心がない出荷担当者にとっては余り意味がありません。このゼロの値は混乱する情報となります。

このゼロ値を含むレポートについての問題は複数のプロセスを一つのファクトテーブルで扱う際に問題となります。一つのプロセスを分析しようとするときにもう一つのプロセスをどのように扱うのかという問題です。今回の出荷量について、出荷が同時に行うという制約がない場合、別の行を作成する必要があります。注意したいのはNULL値の利用がこの問題を解決はしないということです。商品(333)の行はレポートにおいてゼロの代わりにNULL(空白)を表示したとしても表示されます。

この問題を解決するために、スタースキーマ設計を見直すのではなく、検索時においてゼロ値見分けるという対応に誘惑にかられることもあります。例えば、このクエリーはHAVING句を利用することができます。

SELECT product_key, SUM(quantity_shipped) FROM sales_facts GROUP BY product_key HAVING SUM(quantity_shipped) > 0

設計上の問題を回避するためのこの回避策をおこなうとレポート作成はより複雑なものとなります。このSQLの対応(havingの利用)は出荷に関するすべての検索において対応する必要があります。同様のことが逆に受注においても必要となります。一つ以上のプロセスを一つのファクトで扱う場合、すべての場合においてSQLの対応が必要となります。

この回避策について私の古い友人は「ゆでガエル」と呼んでいました。このゆでガエルの話は聞いたことがあると思います。もし、あなたがストーブ上のポットにカエルを入れて徐々に温度が上げていったとしたら、カエルは温度の変化に気づくことができないというものです。1時間に1度ずつ上げていった場合、カエルは温度の上昇を感じることはありません。最後に茹だってしまい死に至ります。もちろん実際はカエルはどこかの段階でポットから飛び出ます。このたとえ話はスタースキーマ設計の問題点を示しています。つまりレポート開発者がこの話のカエルに相当します。彼らの採用するすべての回避策は水温の1度の上昇にたとえられます。回避策の一つが小さなものであっても、その累積効果は許容できなくなります。もし一つのプロセスについて分析をする際に、すべての検索にHAVING句をつける必要があります。この他の種類の検索についても同様です。個数集計(count)、平均(average)、サブクエリー、相関サブクエリーこれらすべてにおいてより複雑なものとなります。さらに悪いことに、(一つではなく)二つのプロセスについて分析を行う場合は、HAVING句は除去することが重要になります。スキーマ設計においては「ゆであがらない」ように対応する必要があります。

一つのファクトテーブルを利用するパターンとしてファクトごとにレコードを作成するというものがあります。データが「受注」か「出荷」かを判別するディメンションを作成します。このテクニックの例を以下に示します。

f:id:good-value:20170529230037p:plain

この対応はすべての行が受注もしくは出荷を表すことになるため、ゼロ値問題は防ぐことができます。注意点としては、どの種類のファクトかというのを意識する必要があるという点です。クエリーの結果はそのまま利用できない形となるため、レポート作成時には別のフォーマッティング(整形)が必要となります。この整形は二つのプロセスを比較しようとした際に問題となります。同じ列で並べたい項目が別の行で出力されることになります。もう一度繰り返しますが、設計での妥協はレポート開発者を混乱させることになります。

(つづく)