[Looker] Period over Period 分析をLookMLで実装する その弐 #looker

この記事では、こちらの記事で紹介されている、前年同日比もしくは前月同日比分析（Period over Period 分析）の8つのユースケースのSQLを、SnowflakeのSQLに対応したものに書き換えて試してみる第二弾です。

• Period over Period 分析をLookMLで実装する その壱

第二回も、引き続きdimension groupのtimeframeを利用したシンプルな実装ですが、第一回の難点であったExploreでの操作性の悪さを解消する実装になっています。

また、フィルターを利用してtimeframeを指定するので、ダッシュボード上でもPoPの種類を変更できるようになります。 しかし、この実装でもやはり、フィルターメジャーが利用できないのが難点です。

第一弾の難点であった、どのtimeframeをPivotにして、どのtimeframeをそのまま選択すればいいのかがややこしいという、Exploreでの操作性の悪さを解消するために、第二弾では、parameterパラメーターを利用してフィルターに事前に値を定義しておいてその入力値を元にtimeframeを決定するdimensionを フィルターで時間軸の粒度や、各種PoP(前週同日比(WTD)、前月同日比(MTD)、前年同日比(YTD))を選択できるようになるので、ダッシュボードを見るだけのユーザーでも日にちを変更するといった操作ができるようになります。

LookML

LookMLの実装は、第一弾で定義したsqlパラメーターでCURRENT_TIMESTAMPと上記のcreated_raw(受注日時生データ)を比較して評価するdimensionの${wtd_only}と${mtd_only}、${ytd_only}はそのまま使います。

追加で、parameterパラメーターでPivot用のtimeframeを選択するフィルターと、Pivotしない方のdimensionのtimeframe選択するフィルターの2つのfilter-only fieldを作ります。

さらに、label_from_parameterパラメーターとorder_by_fieldパラメーターを利用して、可視化のラベルをフィルターの選択の値に合わせたり、クエリ結果の表示も選択したtimeframeの順番でソートする表示に指定します。

include: "/view_file_name.view.lkml"

view: pop_simple {
  extends: [order_items]

  # 集計を出すtimeframeの粒度を選択(Pivotしない)
  parameter: choose_breakdown {
    label: "Choose Grouping (Rows)"
    view_label: "_PoP"
    type: unquoted
    default_value: "Month"
    allowed_value: {label: "Month Name" value: "Month"}
    allowed_value: {label: "Day of Year" value: "DOY"}
    allowed_value: {label: "Day of Month" value: "DOM"}
    allowed_value: {label: "Day of Week" value: "DOW"}
    allowed_value: {value: "Date"}
  }

  # 集計結果を比較するtimeframeの粒度を選択(Pivotする)
  parameter: choose_comparison {
    label: "Choose Comparison (Pivot)"
    view_label: "_PoP"
    type: unquoted
    default_value: "Year"
    allowed_value: {value: "Year"}
    allowed_value: {value: "Month"}
    allowed_value: {value: "Week"}
  }

  # choose_breakdownパラメーターで選択された値に応じて使用するtimeframeを判断する
  dimension: pop_row {
    view_label: "_PoP"
    label_from_parameter: choose_breakdown
    type: string
    order_by_field: sort_by_row
    sql:
      {% if choose_breakdown._parameter_value == 'Month' %} ${created_month_name}
      {% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
      {% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
      {% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week}
      {% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
      {% else %} NULL {% endif %}
    ;;
  }

  # choose_comparisonパラメーターで選択された値に応じて使用するtimeframeを判断する
  dimension: pop_pivot {
    view_label: "_PoP"
    label_from_parameter: choose_comparison
    type: string
    order_by_field: sort_by_pivot
    sql:
      {% if choose_comparison._parameter_value == 'Year' %} ${created_year}
      {% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_name}
      {% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
      {% else %} NULL {% endif %}
    ;;
  }

  # pop_row dimensionのソート順を指定する隠しdimension
  dimension: sort_by_row {
    hidden: yes
    type: number
    sql:
      {% if choose_breakdown._parameter_value == 'Month' %} ${created_month_num}
      {% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
      {% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
      {% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week_index}
      {% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
      {% else %} NULL {% endif %} ;;
  }

  # pop_pivot dimensionのソート順を指定する隠しdimension
  dimension: sort_by_pivot {
    hidden: yes
    type: string
    sql:
      {% if choose_comparison._parameter_value == 'Year' %} ${created_year}
      {% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_num}
      {% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
      {% else %} NULL {% endif %} ;;
  }
}

Exploreで可視化する

1. PoPの種類を選択する

   第一弾と同様に、PoP分析したい粒度のdimensionをフィルターで選択します。

2. 集計・比較をするそれぞれのdimensionのtimeframeをフィルターの値から選択する

   1で選択した粒度にあったtimeframeをそれぞれ選択したます。(下図)

   

3. PoP Row dimensionをそのまま選択、PoP Pivot dimensionをPivotで選択、集計したいmeasureを選択

   

結果

無事に結果が帰ってきました！

可視化を見ると、きちんとフィルターから値を持ってきてくれています。

ダッシュボードで操作する

ダッシュボード上で分析粒度を変更するのに必要な項目をダッシュボードフィルターとして設定して、YoYで保存したタイルをMoMに変更しました。

MTD (Yes/No)とWTD (Yes/No)、YTD (Yes/No)、そして受注DateのフィルターはAdvancedがお勧めです。

まとめ

第一弾ではどのtimeframeをrowとして選んで、どのtimeframeをpivotするべきかがわかりづらかったので、rowとpivot用のdimensionを選択しておけばフィルターで選択された値に応じてtimeframeを引っ張ってきてくれるのはありがたかったです。

ダッシュボード上でのフィルターの設定や、初見のビューアーが果たしてすぐにフィルターを使いこなせるのかという問題はありますが、この方法でExploreへのアクセス権限のないユーザーでもダッシュボード上で操作できる仕組みは良さげです。

次回は、LookMLが複雑になりますが、Exploreでの操作は比較的シンプルで、かつフィルターメジャーも使用できる方法です！