この文章はDATUM STUDIO Advent Calendar 2016の16日目です。

趣旨

BIツールを活用する上で、シートの枚数、グラフの枚数を極力少なくすることは、日々の業務を圧縮する上で求められる要件となります。
そんな中で、一つのレコードに複数の日付情報が保存されているデータ、例えば受注日付、出荷日付、返品日付などが存在しているデータがあります。
これをそのまま一枚のグラフに貼り付けると、横軸として使えるのは受注日付となるため、例えば受注日付に出荷や返品のデータも引っ張られてしまうことになります。

例えば上記のデータではorder_dateの軸ではorderの数量は一致しているが、
send_dateの軸ではsendの数量が一致するという形となり、一枚のグラフにはできません。

解決方法

そこで利用できるのが、標準カレンダー(Canonical Calendar)というデータの作りとなります。
例えば下記の様なデータ形式が存在しているとします。

これに対して、下記のようなSQLを別に発行し、これをmultiple_datesとINNER JOIN をさせます。

カスタムSQL

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 返品数量

その後、軸に標準日付を追加し、
上記で作成した計算フィールドを追加することで、
一枚のグラフ上で複数の数量をそれぞれの日付に沿って集計することが可能となります。

参考資料

Canonical Calendar