[ThoughtSpot]日付フィルターでRollingと固定で数字が合わない！ -カスタムカレンダーを追加する- #BI

はじめに

ThoughtSpot利用歴2年目。最近ThoughtSpotのチャットサービスに質問を投げては日本語⇔英語翻訳を繰り返している非エンジニアです。
アイキャンオンリースピークじゃぱにーず。

さて先月1月に起きたことを記事にします。
ピボットテーブルでレンジごとの件数を表示するグラフを作っていたのですが、「Rolling」と「固定」でそれぞれフィルターをかけて、論理的には同じ値になるはずなのに、異なった値が表示されたということがありました。
なんでこんなことになったのかを次に書きますが、んなことはどうでもいいからカスタムカレンダーの追加方法だけ教えてという人は目次から「解決編：カスタムカレンダーを追加する」へ飛んでください。

物語編：日付フィルターで起きた事象

事象の詳細

先月1月のこと。
「Rolling」で「今月以前」と指定してグラフを作成しました。表示された件数を確認して、表示内容に誤りがないか確認するために「固定」で「2024/1/1以前」でもフィルターをかけてみました。
で、表示される件数が異なっていました。
なんじゃこりゃ。

「Rolling」で指定した方が件数が少ない。論理的には同じ値になるはずなのに。フィルターのかけ方を何回か変えてみましたが全然原因がわからないので、チャットサポートから「なんでこうなっているの？」とメッセージを送りました。
チャットでのやりとりで「Rolling」と「固定」で発行されるSQLが異なっていることがわかりました。
ただ結局理由がわからないのでケースチケットを切ることに。このSQLが異なっているのが原因では？なんで異なっているの？という内容でチャットサポート側でチケットを代理発行。

ちなみにSQLってなんぞや？という方へ。
ThoughtSpotでは検索バーの内容から、どういうSQLでグラフ化しているのか？をここから見ることができます。

原因の発覚

ケースの方で返ってきた回答は「発行されているSQLは正しいよ」とのこと。
正しいと言われても、、、と困惑してしまったのですが、回答に「カスタムカレンダーの中の"absolute_month_number"」というワードがあり、ここでようやくカスタムカレンダーを見にいくことに。

でピンと来ました。
カスタムカレンダーに日付が足りてないかもしれない。
カスタムカレンダー自体の日付は2012/4/1から作っているのですが、今回日付フィルターをかけていた日付項目の最小日付を見たら2004年でした。これだ。
カスタムカレンダーの記事で「元データに対して不足がない日付で設定して」と書いておきながら、自分でやりました。反省。
というかこれはもっと早く原因気づけたのでは……とも思ってしまい更に反省。

原因を細かく解説すると

Rollingで「今月以前」とフィルターをかけたときに裏で下記のようなSQLが動いていて、フィルターかけた日付の"absolute_month_number"とCURRENT_DATEの"absolute_month_number"を比較してどの日付が「今月以前」なのか条件つけているのですが、カスタムカレンダーが足りていなかったことで"absolute_month_number"がnullの日付ができてしまい、その分が検索から零れ落ちた、という感じです。

FROM "******************_table" "ta_1"
  JOIN "****_calender" "ta_2"
    ON "ta_1"."date" = "ta_2"."date"
  JOIN "****_calender" "ta_3"
    ON CURRENT_DATE() = "ta_3"."date"
WHERE ("ta_2"."absolute_month_number" < "ta_3"."absolute_month_number")

解決編：カスタムカレンダーを追加する

ざっくり手順

ということで、すでに作成されているカスタムカレンダーに更にカレンダーを追加して解消しました。
カスタムカレンダーの追加方法を詳しく書こうとしたのですが、だいぶ力業だったのと、果たしてこの追加方法が最善だったのかがイマイチ自信がなくて詳しく書くのは控えようと思います。
ざっくり手順を書くと

ThoughtSpot側で追加したい分の期間で新規にカスタムカレンダーを作成
それを元カレンダーに合わせてメンテ
「absolute_week_number」「absolute_month_number」「absolute_quarter_number」「absolute_year_number」の項目数字が狂わないように元カレンダーの項目をUPDATE
新規に作ったカスタムカレンダー（追加分）を元のカスタムカレンダーにINSERT
ThoughtSpot画面上のカスタムカレンダーから新規に作った分は削除（元カレンダーに取り込んだので不要）

このような手順になります。
追加分のカレンダーを作るときはカレンダータイプや週の初めなどの設定がもともとのカレンダーと一致するように設定するよう気を付けてください。

気になったら見て

absolute項目について

absolute項目は、カスタムカレンダーの開始日から順番に1,2...と項番が振られている項目になります。
この項目に入っている数字を使って、日付のRollingフィルターで「今月以前」などの検索を可能にしている項目になります。ここの数字順が狂ってしまうと日付のBefore/After判定が狂ってしまい、正しくフィルターがかからなくなる恐れがあります。

カスタムカレンダーの上限

ThoughtSpotでカスタムカレンダーを作成するときに上限の日付があります。
細かくは検証していないですが、2000/1/1～2050/12/31の50年分は問題なく作成できました。
2000/1/1～2100/12/31の100年分はエラーが出て作成不可能でした。
（以前は50年分も作成できなかった気がするのですが、上限あがったんですかね）
50年分のカレンダーが作成できればほぼ安泰かな……とは思うので、最初からmaxの範囲でカスタムカレンダーを作成しておくことをおススメします。
（上限あがったのなら、こんなことしないで、年名プレフィックスとかのメンテが面倒でなかったらイチから作り直した方が良いのかもしれない）

おわりに

ちなみに今回扱っていたデータは2004年から綿々と続いているものではなく、2004年、2006年、2010年と飛び飛びにデータが入っていて、2012年からは連続してデータが入っているというような代物でした。
扱っていたデータが「2012年から」のデータだという無意識な思い込みがあって、それ以前の日付が入っている可能性を最初から考えなかったのも悪かったです。SQLの方疑ってごめんなさい。

でもこの方法でカスタムカレンダーを大きくするともしかしたらデータ読み込みのときに時間がかかるとかそういう理由から上限があるのかもしれない。エンジニアではないから詳しいことは分からないけれど。有識者意見求ム。