シャローム！Sato-Gです。
札幌から東京に戻って来た。やはり飛行機は運休が多くて、飛行機の中はまたしても「密」。
相変わらず東京の新型コロナ感染者は減らず30人超えの日が続いているが、今度はキャバクラでの集団感染だとか...
人口変動率データに「夜の街」はないのかな。
夜の人口変動率もほしいな。
さて、前回まででCOVID-19感染者データと人口変動率データを取り込んで可視化するデータモデルは完成を見たわけだけど、今回からはダッシュボードで可視化してみようと思う。
BIというのは、ないデータは見せることはできない。
のっけから何言ってるの？って感じだと思うんだけど、BIを使ったことがある人はそうなんだよなと思うはず。
例えば
・製品A 10個
・製品B 20個
・製品C 0個(売れなかった)
というような場合、その売上を棒グラフで表示すると、通常は製品Cは表示されない。
まあ、売れなかったんだからデータがない、それが普通。
でも時系列でそれをやっちゃうと、傾向が見えなくなってしまう。
売上がない日を表示してこそ意味があるのだから...
そこでだ。まず前回まで作成した以下のようなデータモデルを基に感染者数の推移を表す棒グラフを作成してみる。
ウィジェットの設定は
・カテゴリ 年月日（カレンダーテーブル）
・値 SUM(人数)
で表示できるんだけど、上の「ないものは表示できない」を回避するために時系列に関しては「連続的タイムライン」という設定が使える。
この連続的タイムラインを使うと感染者がいない日もきちんと表示してくれる。
出来上がったウィジェットをコピーして右に配置し、今度は感染者の累計を表示してみる。
累計で使う関数はRSUM()だから、値の数式は下記のように編集する。
RSUM(SUM([人数]))
はい、これで完成！
「ではまた！」
で今回の記事を終わらせると、「おいおい今さらこんな記事書くの」とTurtleに笑われる。
実は問題大有りなのだ。
というのは...
ここでフィルタの都道府県で「02_青森県」を選んでみる。
「あーこういうこと?!」ってことになる。
上記の右のウィジェットを見てほしい。
累計なんだから、連続的に累計を表示してほしいよね。テレビや新聞のグラフはちゃんとできてるのにBIは
「ないものは表示できない」
から累計計算はできないんだよ。
「ではまた！」
で終わりたくなってきた！
実は簡単ではなかった。
だって「値がないんだから計算できないでしょ」というのが従来のBI屋さんの回答なんだもん。
そこでだ。
少し発想を変えてみる。
「データがないからこういうことになるんだ」
...ということを逆手に取って、全く自己流なやり方で解決したのが今回の記事。
コミュニティに載ってるのかもしれないし、もっといい方法があるのかもしれないけど、まあ今回はこんな方法もあるってことで僭越ながら...
カレンダーテーブルと都道府県マスタの２つのカスタムテーブルのSQLを編集する
■カレンダーテーブル
カレンダーテーブルには以下のカラムを追加する
・[ZERO] 全てのレコードに"0"という値が入る
・[FAKE_KEY] 全てのレコードに"1"という値が入る
SELECT Distinct [確定日KEY] AS 年月日KEY,
CreateDate(
GetYear([確定日YYYYMMDD]),
GetMonth([確定日YYYYMMDD]),
GetDay([確定日YYYYMMDD])
) as 年月日,
1 as FAKE_KEY,
0 as ZERO
FROM [COVID-19]
UNION
SELECT Distinct 年月日KEY,
年月日,
1 as FAKE_KEY,
0 as ZERO
FROM [人口変動データ]
■都道府県マスタ
都道府県テーブルには以下のカラムを追加する
・[FAKE_KEY] 全てのレコードに"1"という値が入る
SELECT Distinct 都道府県KEY,
[#測定都道府県] as "#都道府県" ,
1 as FAKE_KEY
FROM [人口変動データ]
次にリレーション設定を行う。
今回のアイデアは、
「値がないなら値を作ればいい」
という発想から来ている。
カレンダーテーブルには年月日に[ZERO] というカラムに0という値が常に入っているからデータはあるのである。
つまり、SUM([人数])とSUM([ZERO])を日単位で足して、累計すればいいんじゃないか－
人数がない日でも、0という値が入っている[ZERO]を足しているからデータがあるよね。
だからその累計は計算できる！
しかしだ！
元々のデータモデルのリレーションのままだと、都道府県マスタの都道府県を選択すると、そのフィルタは「COVID-19」で感染者がある日を絞り込み、さらに「人口変動データ」では人口変動データがある日を絞り込むことになる。
従って、カレンダーテーブルに到達するときには、やはり絞り込まれていない日の[ZERO]は拾えない！
なんてこった！
「ではまた！」...三たび
って言いたくなるけど、ここで救ってくれるのがFAKE_KEYである。
カレンダーテーブルと都道府県マスタのFAKE_KEY同士でリレーションを設定する。
これで新しいクエリパスができて、双方とも1が入っているから、どの都道府県を選択しても全ての年月日が取得できる。
ここまでくれば理屈はわかったよね？
これで以下のような数式を設定してあげればいい。
RSUM(SUM([人数])+SUM([ZERO]))
そして、完成形がこれ！
「02_青森県」を選択してもきちんと累計してくれている。
実はこの累計の歯抜け問題は「Sisenseのバグじゃないのか？」と思っていた。
サポートにチケット上げようかと真剣に考えていた。
しかし、「ないものは表示できない」と言われればそれまでなので（僕ならそう言っちゃうかも）考えてみたんだ。
悔しいじゃん、そういうの。
で今回のインサイトは
フィルターが邪魔な時はデータモデル側で
FAKE_KEYでフィルタに無関係なパスを作ってあげる
ということ。
それじゃあ本当に（笑）
ではまた！
