この文章はDATUM STUDIO Advent Calendar 2016の16日目です。
趣旨
BIツールを活用する上で、シートの枚数、グラフの枚数を極力少なくすることは、日々の業務を圧縮する上で求められる要件となります。
そんな中で、一つのレコードに複数の日付情報が保存されているデータ、例えば受注日付、出荷日付、返品日付などが存在しているデータがあります。
これをそのまま一枚のグラフに貼り付けると、横軸として使えるのは受注日付となるため、例えば受注日付に出荷や返品のデータも引っ張られてしまうことになります。
例えば上記のデータではorder_dateの軸ではorderの数量は一致しているが、
send_dateの軸ではsendの数量が一致するという形となり、一枚のグラフにはできません。
解決方法
そこで利用できるのが、標準カレンダー(Canonical Calendar)というデータの作りとなります。
例えば下記の様なデータ形式が存在しているとします。
これに対して、下記のようなSQLを別に発行し、これをmultiple_datesとINNER JOIN をさせます。
SELECT
受注日付 AS 標準日付
ユニークキー
'受注' AS CODE
FROM
FactTable
UNION
SELECT
出荷日付 AS 標準日付
ユニークキー
'出荷' AS CODE
FROM
FactTable
SELECT
返品日付 AS 標準日付
ユニークキー
'返品' AS CODE
FROM
FactTable;
これは、日付情報の数だけデータを繰り返すSQLで、
最後のCODEによってその日付がどの日付情報に従っているのかを判別しています。
具体的には下記の様なSQL構造となります。
続いて、計算フィールドを作成することで、数量が複数ある場合には一つにまとめます。
フィールド名例:数量_標準日付基準
IF CODE = '受注' THEN 受注数量
ELSEIF CODE = '出荷' THEN 出荷数量
ELSEIF CODE = '返品' THEN 返品数量
その後、軸に標準日付を追加し、
上記で作成した計算フィールドを追加することで、
一枚のグラフ上で複数の数量をそれぞれの日付に沿って集計することが可能となります。