複数のファクトテーブルのリンク-Link Table

[ShoNakajima]

■はじめに

売上の分析などを行う場合、売上の実績データと、別にファイルで管理している予算・計画のデータを組み合わせて分析を行う場合があります。このような形で集計と対象となるテーブルが複数存在するようなデータモデルを一般的に「マルチファクト」と呼びますが、ここではLink Tableを利用したそのようなデータモデルの扱いについてご説明します。

■シンプルな複数ファクトテーブルの扱い

では、まずシンプルな例から始めていきたいと思います。以下のロードスクリプトで予算と実績のデータをそれぞれ「予算データ」と「実績データ」テーブルに格納します。

[予算データ]:
Load * Inline [
年月,支店ID,製品ID,予算
201501,1,1,200
201501,1,2,120
201501,2,1,220
201501,2,2,230
201502,1,1,120
201502,1,2,130
201502,2,1,200
201502,2,2,80
201503,1,1,120
201503,1,2,90
201503,2,1,150
201503,2,2,170
];


[実績データ]:
Load * Inline [
年月,支店ID,製品ID,顧客ID,実績
201501,1,1,1,120
201501,1,1,2,120
201501,1,2,1,100
201501,2,1,1,100
201501,2,1,2,90
201501,2,2,2,260
201502,1,1,1,90
201502,1,2,2,140
201502,2,1,1,100
201502,2,1,2,130
201502,2,2,1,30
201502,2,2,2,60
201503,1,1,2,130
201503,1,2,2,80
201503,2,1,1,80
201503,2,1,2,90
201503,2,2,1,90
201503,2,2,2,100
];

このケースでは、「予算」と「実績」が別々のテーブルに格納されていますが、両データとも共通で「年月」「支店ID」「製品ID」を持っており、それらをキーとして「予算」と「実績」を紐付けることができます。

上記のスクリプトを実行すると、以下のデータモデルが作成されます。「年月」「支店ID」「製品ID」が複合キーとなってしまっていますが、問題なく「予算」と「実績」のデータは紐付いています。

 

また、データモデルをもう少し改善させるには、以下の形でJoinを使って2つ目のテーブルを一つ目のテーブルに結合させれば、テーブルが一つに纏まってデータモデルがよりシンプルで分かり易くなります。(Joinの詳細については「テーブルの結合–ApplyMap, Lookup, Join, Keep」のエントリをご参照ください。）

Join([予算データ])
Load * Inline [
年月,支店ID,製品ID,顧客ID,実績
201501,1,1,1,120
201501,1,1,2,120
201501,1,2,1,100
201501,2,1,1,100
201501,2,1,2,90
201501,2,2,2,260
201502,1,1,1,90
201502,1,2,2,140
201502,2,1,1,100
201502,2,1,2,130
201502,2,2,1,30
201502,2,2,2,60
201503,1,1,2,130
201503,1,2,2,80
201503,2,1,1,80
201503,2,1,2,90
201503,2,2,1,90
201503,2,2,2,100
];

 

■複数ファクトテーブルの扱いにおける課題

上記の形で予算や実績といった複数のファクトテーブルを纏めることができれば手順として非常にシンプルですが、実際のケースではよりデータモデルが複雑となってしまってなかなか上手くいかないケースもあります。

例えば以下のロードスクリプトを実行してみます。

[実績データ]:
Load * Inline [
年月,支店ID,製品ID,顧客ID,実績
201501,1,1,1,120
201501,1,1,2,120
201501,1,2,1,100
201501,2,1,1,100
201501,2,1,2,90
201501,2,2,2,260
201502,1,1,1,90
201502,1,2,2,140
201502,2,1,1,100
201502,2,1,2,130
201502,2,2,1,30
201502,2,2,2,60
201503,1,1,2,130
201503,1,2,2,80
201503,2,1,1,80
201503,2,1,2,90
201503,2,2,1,90
201503,2,2,2,100
];

[支店マスタ]:
Load * Inline [
支店ID, 支店名
1,関東支店
2,関西支店
];

[製品マスタ]:
Load * Inline [
製品ID, 製品名, 製品カテゴリID
1,製品A,1
2,製品B,2
];

[製品カテゴリマスタ]:
Load * Inline [
製品カテゴリID,製品カテゴリ名
1,製品カテゴリA
2,製品カテゴリB
];

[顧客マスタ]:
Load * Inline [
顧客ID,顧客名
1,顧客A
2,顧客B
];

そうすると、以下のような形でデータモデルに循環参照などが作成されてしまっており、正しく集計がされない状態となってしまっています。これは、「予算データ」は「製品カテゴリID」の項目を含んでいますが、「実績データ」は「製品ID」の項目を含んでおり、「製品カテゴリマスタ」を介してデータモデルがループしてしまう構造となってしまっています。

 

この様に、予算では月、支店、製品カテゴリレベルでデータを持っているが、実績では年月、営業担当、顧客、製品レベルでデータを持っている等、持っているデータの粒度が異なるなどの理由で、先ほど挙げたシンプルな例の様に上手くデータを紐付けれないことがあります。

また、さらに予算、実績、速報、見込み等といった形でファクトテーブルの数が増えたり、マスタテーブルが多くなったりするとデータモデルが複雑となり、なかなか求める形のものが作成できないケースも起こります。

そのような場合に活用できるのがここでご紹介するLink Tableです。Link Tableは複数のファクトテーブルを紐付ける全てのキー項目を格納したテーブルで、このLink Tableを中心としてファクトテーブルとディメンションテーブル(マスタテーブル)を紐付ける役割を担います。

 

■Link Tableを使った複数テーブルのリンク

では、実際にLink Tableを作成したいと思います。Link Tableは以下の3つのステップを踏んで作成します。

• 共通の項目を含んだ複数のファクトテーブルをロード
• 複数のファクトテーブルから一意な共通項目の値を抽出してLink Tableを作成
• ディメンションテーブル(マスタデータ)をロード

また、上記作業を完了してデータが問題なく紐付けられたことを検証した上で、ベストプラクティスの観点から以下の作業を行うことが推奨されます。

• 複合キーへのAutoNumberの追加

尚、ここで作成するアプリのサンプルは以下からダウンロードが可能です。

>>LinkTableのサンプルアプリをダウンロード

共通の項目を含んだ複数のファクトテーブルをロード

まず複数のファクトテーブルをロードします。この時、以下の2つのことを行う必要があります：

• データの粒度が異なる場合にはその粒度も合わせる必要があります。先ほどの例では「予算データ」は「製品カテゴリID」、「実績データ」は「製品ID」とそれぞれ異なる階層の項目で集計されていたため、ここではより上位階層の「製品カテゴリID」に粒度を揃えます。
• 複数ファクトテーブル間を紐付ける共通のキー項目を複合キーとして一つの項目に纏めます。

まず、予算データをロードします。予算データは「製品カテゴリID」を含んでいるため粒度を揃える作業は不要で、共通キーを複合キーとして纏める処理のみを行います。「|」で区切って「年月」「支店ID」「製品カテゴリID」の項目を連結して「Key」という名前の項目に格納しています。

//「年月」「支店ID」「製品カテゴリID」の複合キーを作成して予算データをロード
[予算データ]:
Load
    年月 & '|' & 支店ID  & '|' &  製品カテゴリID　AS Key,
    予算
Inline [
年月,支店ID,製品カテゴリID,予算
201501,1,1,200
201501,1,2,120
201501,2,1,220
201501,2,2,230
201502,1,1,120
201502,1,2,130
201502,2,1,200
201502,2,2,80
201503,1,1,120
201503,1,2,90
201503,2,1,150
201503,2,2,170
];

尚、複合キーには以下の様な形でデータが格納されます。

 

次に実績データをロードしますが、その前にApplyMapを使って「製品ID」を「製品カテゴリID」に変換するための「Mapping_Product」という名称のMapping Tableを作成します。(ApplyMapの詳細については「テーブルの結合–ApplyMap, Lookup, Join, Keep」のエントリをご参照ください。）

//「製品ID」を「製品カテゴリID」に変換するためのMapping Tableを作成
Mapping_Product:
Mapping Load 
	製品ID,
    製品カテゴリID
Inline [
製品ID, 製品名, 製品カテゴリID
1,製品A,1
2,製品B,2
];

そして、実績データをロードします。ここでも先ほど同様に共通項目を複合キーとして「Key」という項目に格納していますが、「製品カテゴリID」は「製品ID」を引数として「Mapping_Product」のMapping Tableから取得している点に注意して下さい。また、共通項目とは別に、この実績データのみが持っている「製品ID」と「顧客ID」を残しておくことができます。

//「製品カテゴリID」はApplyMap関数を使ってMapping Tableより取得
//「年月」「支店ID」「製品カテゴリID」の複合キーを作成して実績データをロード
[実績データ]:
Load
    年月 & '|' & 支店ID  & '|' &  ApplyMap('Mapping_Product',製品ID)　AS Key,
    製品ID,
    顧客ID,
    実績
Inline [
年月,支店ID,製品ID,顧客ID,実績
201501,1,1,1,120
201501,1,1,2,120
201501,1,2,1,100
201501,2,1,1,100
201501,2,1,2,90
201501,2,2,2,260
201502,1,1,1,90
201502,1,2,2,140
201502,2,1,1,100
201502,2,1,2,130
201502,2,2,1,30
201502,2,2,2,60
201503,1,1,2,130
201503,1,2,2,80
201503,2,1,1,80
201503,2,1,2,90
201503,2,2,1,90
201503,2,2,2,100
];

複数のファクトテーブルから一意な共通項目の値を抽出してLink Tableを作成

ここではLink Tableを作成します。Link Tableは全てのファクトテーブルとディメンションテーブル(マスタテーブル)の間の共通項目をキーとして紐付を行う役目を果たします。そのために、このテーブルには以下のデータをファクトテーブルから格納します：

• 共通項目を1つの項目に纏めて作成した複合キー（ここでの例では「Key」の項目)
• 全ての共通の項目（ここでの例では「年月」「支店ID」「製品カテゴリID」）

全てのファクトテーブルから上記データの一意な組み合わせを全てLink Tableに格納すれば、Link Tableを介して全てのファクトテーブルとディメンションテーブル(マスタテーブル)を紐付けることができるということになります。

予算データと実績データの上記の項目を以下の形でロードします。今回はLink Tableに対してDistinctを指定してロードを行います。

//予算データの一意な共通項目の値ををLink Tableに格納
LinkTable:
Load　Distinct
	年月 & '|' & 支店ID  & '|' &  製品カテゴリID　AS Key,
    年月,
    支店ID,
    製品カテゴリID
Inline [
年月,支店ID,製品カテゴリID,予算
201501,1,1,200
201501,1,2,120
201501,2,1,220
201501,2,2,230
201502,1,1,120
201502,1,2,130
201502,2,1,200
201502,2,2,80
201503,1,1,120
201503,1,2,90
201503,2,1,150
201503,2,2,170
];

//実績データの一意な共通項目の値ををLink Tableに格納
LinkTable:
Load Distinct
	年月 & '|' & 支店ID  & '|' &  ApplyMap('Mapping_Product',製品ID)　AS Key,
    年月,
    支店ID,
    ApplyMap('Mapping_Product',製品ID) AS 製品カテゴリID
Inline [
年月,支店ID,製品ID,顧客ID,実績
201501,1,1,1,120
201501,1,1,2,120
201501,1,2,1,100
201501,2,1,1,100
201501,2,1,2,90
201501,2,2,2,260
201502,1,1,1,90
201502,1,2,2,140
201502,2,1,1,100
201502,2,1,2,130
201502,2,2,1,30
201502,2,2,2,60
201503,1,1,2,130
201503,1,2,2,80
201503,2,1,1,80
201503,2,1,2,90
201503,2,2,1,90
201503,2,2,2,100
];

上記スクリプトを実行すると以下の内容を含むLink Tableが作成されます。

 

ディメンションテーブル(マスタデータ)をロード

最後にディメンションテーブル(マスタデータ)をロードします。ここで注意が必要なのは、共通項目としてLink Tableに紐付くマスタテーブルと、個別に特定のファクトテーブルに紐付くマスタテーブル間でキーの紐付きがなされないようにする必要があります。今回の例では個別に実績データのみに紐付く「製品マスタ」に「製品カテゴリID」を含んだままにしてしまうと、共通項目の「製品カテゴリマスタ」とも紐付いてしまうため、循環参照が発生してしまいます。そのため、「製品マスタ」から「製品カテゴリID」を削除してロードしています。

[支店マスタ]:
Load * Inline [
支店ID, 支店名
1,関東支店
2,関西支店
];

[製品マスタ]:
Load 
    製品ID, 
    製品名
Inline [
製品ID, 製品名, 製品カテゴリID
1,製品A,1
2,製品B,2
];

[製品カテゴリマスタ]:
Load * Inline [
製品カテゴリID,製品カテゴリ名
1,製品カテゴリA
2,製品カテゴリB
];

[顧客マスタ]:
Load * Inline [
顧客ID,顧客名
1,顧客A
2,顧客B
];

上記スクリプトを実行すると、最終的に以下の形のデータモデルが作成されます。

 

複合キーへのAutoNumberの追加

これまでの作業で「年月」「支店ID」「製品カテゴリID」の3つの項目を纏めて「Key」という名称の1つの項目を作成しました。このように複数のキー項目を一つにまとめたキー項目を「複合キー」と呼びます。

この複合キーは区切り文字で連結しているため、以下の様な文字列のデータとなっており、キー項目としては処理効率が良くありません。

 

そこで、以下の様な形で複合キーを作成する処理にAutoNumberを追加します。

AutoNumber(年月 & '|' & 支店ID  & '|' &  製品カテゴリID)

AutoNumberでは、引数として与えられた値が同一であれば同一アプリ内であれば同じ値で連番を作成します。つまり、キー項目の文字列を以下の形で数値に置き換えることができます。

 

この様な形で、複合キーについては全てAutoNumberを追加して数値に置き換えることが推奨されます。

■まとめ

予算データと実績データでは持っているデータの粒度が異なるなどの理由で、これらのマルチファクトのデータを上手くデータを紐付けれないことがありますが、そのような場合に活用できるLink Tableの手法をご説明させて頂きました。

LinkTable.zip