【Sisense Data Modeling】CIVID-19データと人口変動データによるマルチファクト構成のモデリング

シャローム！Sato-Gです。
新型コロナに関する都道府県間の移動が6月19日に全面的に解禁になった。
というわけで、今、札幌にいる。そして北海道限定（しかも期間限定）のClassicを飲んでいる。
こちらに来る飛行機は満席！
8月から始まると言われる「Go To キャンペーン」の前に都道府県をまたぐ移動をする人は結構増えそう。ただ、航空各社は運休がまだまだ多くて、満席で密にするくらいならもう少し飛ばしてほしいところだ。飛行機で集団感染とか出ちゃうと、もはや移動の足が失われることになりかねないからね。

さて、今回は「年月日」と「都道府県」の結合を行うことで前回の不満点の対策を施し、操作性を改善したい。

１. マルチテーブル結合のデータモデリングのアプローチ方法

前回の不満点は以下のとおり
・感染者数推移と人口増減において、時間軸、都道府県の絞り込みが分離している
・同一グラフ上で感染者と人口増減の関係を比較できない
・フィルターの絞り込みに依存しており、都道府県間の比較を同時に行うことができない

これらは、COVID-19データと人口変動データが結合されていないから起きていた問題だ。
ただ、この２つともファクトテーブル（メジャーを含むテーブル）となるため、マルチファクトテーブル結合はその方法によって誤った集計が行われるため、慎重に行わなければいけない。
ここでは３つのアプローチで実験してみる。

【PLAN-A】2つのキー項目同士でリレーションを2つ設定する
【PLAN-B】2つのキー項目を1つに結合して1つのキー項目にして、1対多でリレーションを設定する
【PLAN-C】2つのキー項目を1つに結合して1つのキー項目にした上で外部テーブル（ブリッジテーブル）化して２つのテーブルをブリッジする。
【PLAN-D】キー項目それぞれについて、ユニークな値となるディメンションテーブルを別々に作成する

2.　前処理

データモデリングを行う前に、このままでは結合に支障が出るため、前処理を行っておく。解決しなければいけない問題点は次のとおり。
【問題点】
・DateTime形式の項目は時刻が入っているケースがあり、結合には向かない
・都道府県コードが、左0埋めされているものとされていないものがある

2.1 COVID-19テーブル

以下の２つのカスタム列を追加する。

・確定日KEY
DateTime型からInteger型へ変換する（GetYear / GetMonth / GetDay）
GetYear([確定日])*10000 + GetMonth([確定日])*100 + GetDay(確定日)

・KEY
ToStringでString型へ変換し、テキストを+結合する
ToString([受診都道府県コード]) + '-' + ToString(GetYear([確定日])*10000 + GetMonth([確定日])*100 + GetDay(確定日))

2.2 人口変動データ

以下の4つのカスタム列を追加する。

・都道府県KEY
都道府県コード左0埋め処理（例：北海道の場合、1→01）を0と+結合し、Rightで2桁取得
Right('0' + ToString([都道府県コード]),2)

・#測定都道府県
都道府県コード左0埋め処理（例：北海道の場合、1→01）を0と+結合し、Rightで2桁取得し、都道府面名と結合
Right('0' + ToString([都道府県コード]),2) + '_' + StrParts([地点],'_',1)

・年月日KEY
DateTime型からInteger型へ変換する（GetYear / GetMonth / GetDay）
GetYear([年月日])*10000 + GetMonth([年月日])*100 + GetDay([年月日])

・KEY
ToStringでString型へ変換し、テキストを+結合する
Right('0' + ToString([都道府県コード]),2) + '-' + ToString(GetYear([年月日])*10000 + GetMonth([年月日])*100 + GetDay([年月日]))

3. ３つのデータモデルで検証

3.1 【PLAN-A】2つのキー項目同士でリレーションを2つ設定する

今回は以下の２つのリレーションを設定する
COVID-19.受診都道府県コード - 人口変動データ.都道府県KEY
COVID-19.確定日KEY - 人口変動データ.年月日KEY

データモデルは以下のとおりとなる。

これでビルドを行った結果、ダッシュボード正しく表示されたように思われた。
しかし、フィルターで区分＝「前年同月比」を選択すると、ウィジェット上でCOVID-19の確定日をX軸に設定したのに2020年5月1日以降しか表示されない。
データとしては4月30日以前のデータがあるはずだ。

また、#測定都道府県で「02_青森県」を選択すると、感染者があった日のみデータが存在しているように表示される。
実際は人口変動データは5月1日以降、毎日存在していたはずだ。

総じて、２つ以上のリレーションを行った時はAND条件で結合される（Inner Join）ということがわかった。

3.2 【PLAN-B】2つのキー項目を1つに結合して1つのキー項目にして、1対多でリレーションを設定する

今回は以下の１つのリレーションを設定する
COVID-19.KEY - 人口変動データ.KEY
※KEYは都道府県コードと年月日KEYを結合して作成したカラムである

データモデルは以下のとおりとなる。

これでビルドを行った結果は、PLAN-Aと同じ結果となった。

3.3 【PLAN-C】2つのキー項目を1つに結合して1つのキー項目にした上で外部テーブル（ブリッジテーブル）化して２つのテーブルをブリッジする

COVID-19テーブルと人口変動データテーブルには年月日と都道府県という２つの共通するキーがある。
このような場合、【PLAN-B】で作成した結合キーである「KEY」という項目を外部テーブル（テーブル名＝Link）として、その外部テーブルに「年月日」と「都道府県」の項目を格納する。
そうすると
Link - COVID-19 → 1対多
Link - 人口変動データ　→　1対多
となるため、Linkテーブルを中心に考えると、データモデルとしては成立すると考えられる。

SELECT Distinct KEY,
        CreateDate(
            GetYear([確定日YYYYMMDD]),
            GetMonth([確定日YYYYMMDD]),
            GetDay([確定日YYYYMMDD])
        ) as 年月日,
       [#受診都道府県]　as "#都道府県"
FROM [COVID-19]
UNION
SELECT Distinct KEY,
        年月日,
       [#測定都道府県]　as "#都道府県"
FROM [人口変動データ]

ここで、ダッシュボードを開いて、人口変動テーブル側の「区分」で「前年同月比」を選択してみる。
そうすると期間が人口変動データの期間である5月1日以降となる以外に、大きな問題が発生してしまった。
感染者数は正しい数値の10倍以上の数値となってしまったのだ。
（都道府県で「02_青森」を選択した場合は【PLAN-A】【PLAN-B】と結果は変わらない）

このモデルではLink - COVID-19、Link - 人口変動データ間では1対多であるが、COVID-19 - 人口変動データという結合が存在し、この結合は多対多となっているためである。
このように出たモデルの中に多対多となるリレーションが存在する場合、フィルターにより破綻をきたす場合がある。
特にこのような結果に至るのは、ファクトテーブルには存在するが、Linkテーブルに存在しないカラムでフィルターをかけた場合である。

3.4 【PLAN-D】キー項目それぞれについて、ユニークな値となるディメンションテーブルを別々に作成する

このプランは【PLAN-C】で失敗した複合キーを回避し、年月日のディメンションテーブル「カレンダー」と都道府県のディメンションテーブル「都道府県マスタ」テーブルを別々に作成し、結合させる方法だ。
２つのカスタムテーブルのSQLは以下のとおり

【カレンダー】

SELECT Distinct [確定日KEY] AS 年月日KEY,
        CreateDate(
            GetYear([確定日YYYYMMDD]),
            GetMonth([確定日YYYYMMDD]),
            GetDay([確定日YYYYMMDD])
        ) as 年月日
FROM [COVID-19]
UNION
SELECT Distinct 年月日KEY,
       年月日
FROM [人口変動データ]

【都道府県マスタ】

SELECT Distinct　都道府県KEY,
        [#測定都道府県]　as "#都道府県"
FROM [人口変動データ]

※人口変動データはすべての都道府県が含まれているため、UNIONする必要はない

リレーションは以下のとおり
COVID-19.確定日KEY - カレンダー.年月日KEY
人口変動データ.年月日KEY - カレンダー.年月日KEY

COVID-19.受診都道府県コード - 都道府県マスタ.都道府県KEY
人口変動データ.都道府県KEY - 都道府県マスタ.都道府県KEY

以上で、以下のようなデータモデルとなる。

これでダッシュボードを表示すると、以下のように「区分」で「前年同月比」を選択しても、期間を個別に選択しない限り、全期間を表示する。
期間を選択すると（カレンダー.年月日=2020/04/01-2020/06/20）、感染者数も増減率も指定した期間ですべて表示してくれた。
全く問題ない。

さらに都道府県マスタの都道府県で「青森県」を選択した結果は以下のとおり

【PLAN-4】が最も望ましい結果が得られた。

4. まとめ

４つのデータモデルで最適なデータモデルについてテストしてきた。
マルチファクトテーブルでデータモデリングを行う場合、ファクトテーブル同士をつなぐとInner JoinとなりAND条件での表示となる。これでよいということなら、このままで一向に構わない。
しかし、Outer Join表示したい場合は、外出しのディメンションテーブルで結合し、ディメンションテーブル - ファクトテーブル間は１対多となるようにする。
その際、複数のディメンションを混在したLinkテーブルのようなものでブリッジするとファクトテーブル側のフィールドのフィルタにより破綻するケースがある。

よって、今回のデータモデルでは

“マルチファクトテーブル構成では、各ファクトテーブルに対して、
単一のディメンションで構成するディメンションテーブルで結合し、
ディメンションテーブル - ファクトテーブル間は１対多となるようにする”

という方法が最適ということがわかった。

マルチファクトテーブルのデータモデリングはこれでバッチリ！（ホントかな？）

ではまた！