Refinementsを使用して既存のExploreに期間比較 (Period Over Period) の機能を追加する

  • 12 May 2021
  • 0 replies
  • 107 views

Userlevel 1

本記事は、Use Refinements to add Period over Period functionality to existing explores の翻訳になります。

 

この例では、よくご要望としていただく 期間比較 (Period Over Period) 機能を Refinements を使用して実装する方法をご紹介しております。

 

一般的なRefinements利用の背景 期間比較(Period over Period)の背景

refinementsを利用することで、主要なロジックと機能を実現するロジックをそれぞれ完全に分離することができ、しかも同時に対象となる元オブジェクトに対して機能を追加することができます。例えば、ここでご紹介するパターンを使って、元のコアオブジェクトには手を入れずに、既存のExploreの日付フィールドにPoP機能をシームレスに追加することができます。

 

これについての詳細はこちらの the lookml refinements doc page をご覧いただくか、Fabioの記事をご覧ください。(訳註:Fabioの記事には翻訳記事がございます)

PoPはよくご要望として上がる機能で、いくつかのやり方があります。例えばこちら (Flexible Period-over-period Analysis) やこちら(An Explore filter based approach) の記事でも他のやり方がご紹介されています。

 

本記事でご紹介する方法は実装がシンプルで、またExploreに変更が入ったとしても特別なメンテナンスが不要です。ユーザーにとっては過度な複雑さを避けた柔軟な方法となります。

 

期間比較 (Period Over Period) の見た目はどのようになりますか?

 

ユーザーは新しく作成したディメンションをピボットすることができ、また期間の長さと表示する数を選択することができます。

 

パラメータの入力例など他の2つの例をご覧になる場合はこちらをExpandしてください。

 

(訳註:フィールドピッカーに、Pivot for Period over Period という追加ディメンションが追加されており、また PoP Periods Ago to IncludePoP Period Size という、対象となる期間と単位を選択できるパラメータが追加されています。この場合は0Qつまり今期と1Q前つまり前期を比較しています)

 

 

 

(訳註: 今度は PoP Periods Ago to Include2以下 を指定し、PoP Period SizeYear にすることで、過去 2 年間を比較対象として表示している例です)

 

このアプローチの利点

 

いくつかあるPoPのバリエーションのほとんど全てをカバーできます:

  • 複数の期間を選択できる
  • 期間の単位(年/期/月など)を選択できる
  • 異なる日付フィールドに基づくPoPなど、複数のバリエーションにも対応可能(同じExplore内でも可)

 

さらにこの方法には次の利点があります:

  • 新しいExploreを作ったり、多くの新しいフィールドを作成する必要がなく、既存のExploreおよびreportの上にレイヤーとして追加することができます。既存のドリルなどに影響を及ぼしません。
  • 非常にシンプルかつメンテナンスが容易で、スケールしやすくなります。新しいフィールドが追加される場合でも、派生テーブルのSQLを変更する必要がありませんし、PoPが動作するメジャーは再定義する必要がありません。
  • ユーザーは直感的に操作できます。
  • Lookerで自動生成される日付関数シンタックスを使用するため、他の方法で必要になる可能性のあるデータベース依存の構文変更を回避できるはずです。

Period Over Periodアプローチのコンセプトの説明

 

まず前提として、ユーザーが比較したい期間のサイズ(単位)と期間がわかっていると仮定します。その上で、以下の操作を行います:

  1. (コンセプトとしては)オリジナルのデータセットの複製を作ります。それぞれの期間に対して一つずつ必要です。
  2. 複製したデータを、期間と単位でオフセットします(ずらします)。

結果:それぞれの「前の」期間のデータが、現在の期間のデータに対して正しくマップされます。

 

これらのステップが完了すると、Explore上の他のすべての要素も問題なく動作し、PoPが適用された場合でも正常に動作します。なぜなら、ファンアウト(訳註:テーブルのJOINによって行が増えてしまう現象)する期間は自然と対応するピボット列に含まれるようになるため、結果セットはいずれにしても全く影響を受けないからです。他の項目は以前と同様にグループ化され、計算されます。

 

このステップを図示すると以下のようなイメージになります:

 

 

SQLまたはクエリーベースでのステップは以下のようになります:

  1. pop_support ビューをJOINします
  • それぞれ選択された期間に対して1行、複製が有効になります
  1. Refinementされた日付フィールド=元の日付フィールド_date + ( [pop_support.number_periods_ago] * [選択された期間の長さ] )
  • 日付フィールドの${EXTENDED}キーワード(元のSQL)に日付オフセット関数をラップすることで実現されます。

 

実際の実装ステップ

 

準備)新しいファイルにPoP Support LookMLのコードをペーストしてください。

注)これは任意の数のPoP対応フィールドで再利用できる汎用コードです。

Period over Period Support LookML

# このコードは変更する必要がありません。必要な場所に以下のコードを含むファイルを作成して保存してください。例えばRefinementsのコード部分でも良いでしょう

view: pop_support {

derived_table: {

sql:

select periods_ago from

(

select 0 as periods_ago

{% if periods_ago._in_query%}{%comment%}このビューが何らかの理由で結合されたが、periods_agoが実際には使用されていない場合に、不要なファンアウトを防ぐための追加のバックストップ{%endcomment%}

{% for i in (1..52)%} union all select {{i}}{%endfor%}{%comment%}最大52週まで設定できます。選択が不十分だとピボットが大きくなりすぎてレンダリングが滞る以外は、実運用上の問題はありません。{%endcomment%}

{%endif%}

) possible_periods

where {%condition periods_ago_to_include%}periods_ago{%endcondition%}

{% if periods_ago_to_include._is_filtered == false%}and periods_ago <=1{%endif%}{%comment%}デフォルト1期前までの期間{%endcomment%}

;;

}

dimension: periods_ago {hidden:yes type:number}

filter: periods_ago_to_include {

label: "PoP Periods Ago To Include"

description: "比較期間を指定するために使用します。デフォルトは 0 または 1 です。(つまり 0=現在 と 1つ前になります)。数字として扱って範囲でも指定できます(例:12以下)"

type: number

default_value: "0,1"

}

parameter: period_size {

label: "PoP Period Size"

description: "デフォルトは直感的に機能するはずです(選択したスケール、つまり行の粒度に合わせる必要があります)が、別のオフセット量を指定する必要がある場合はこれを使用できます。たとえば、毎日の結果を見たいが、以前の52週間と比較したい場合など"

type: unquoted

allowed_value: {value:"Day"}

allowed_value: {value:"Month"}

allowed_value: {value:"Year"}

# allowed_value: {value:"Week"}
# allowed_value: {value:"Quarter"}
# 他の時間枠も多少調整すれば処理できると思われますが、Dialect依存のため普遍的にサポートされていない可能性があり、ユーザーにとって不要な場合があります

allowed_value: {value:"Default" label:"Default Based on Selection"}

default_value: "Default"

}



dimension: now_sql {

type: date_raw

expression: now();;

}

dimension: now_converted_to_date_with_tz_sql {

hidden: yes

type: date

expression: now();;

}



dimension: pop_sql_years_using_now {type: date_raw expression: add_years(${periods_ago},${now_sql});;}#use looker expressions to get dialect specific sql for date add functions

dimension: pop_sql_months_using_now {type: date_raw expression: add_months(${periods_ago},${now_sql});;}

dimension: pop_sql_days_using_now {type: date_raw expression: add_days(${periods_ago},${now_sql});;}



dimension: period_label_sql {

hidden:yes

expression:

if(${pop_support.periods_ago}=0," Current"

, concat(

${pop_support.periods_ago}," REPLACE_WITH_PERIOD"

,if(${pop_support.periods_ago}>1,"s","")

," Prior"

)

);;

}

}

 

 

Step1) 汎用のpop_supportを追加したいExploreのJOINにペーストします

この例では、既存のorder_itemsビューをRefinementsし、Exploreのベースビューとして設定しています。ただし、Exploreに含まれる他の結合や機能に関係なく、Refinementsされたビューを含む既存のExploreにPoP Refinementsを同様に追加できることに注意してください。

 

connection: "your_connection" 

explore: order_items {

# その他のJOINなどはここに記述します

# PoPを有効化するために、以下のJOINを目的のExploreに追記します

join: pop_support {

view_label: "PoP Support - Overrides and Tools"
# pop_supportビューではなく、ここでこのExploreで使用するためにビューラベルを更新します。これをPOP日付のビューラベルに合わせることができます

relationship:one_to_one
# ここでは意図的にファンアウトさせます。そのため、one_to_one のままにしておきます

sql:{% if pop_support.periods_ago._in_query%}LEFT JOIN pop_support on 1=1{%endif%};;

# 楔となるピボットフィールド(periods_ago)が選択されている場合にのみprior_periodが含まれ、結合及びファンアウトされるようにします。この安全対策により、ユーザーがPop SupportでPoPパラメーターを選択したが、実際にはPoPのピボットフィールドを選択しなかった場合に、結合によってファンアウトが発生しないことが保証されます。

}

#(オプション):このalways_filterを基準日フィールドに更新して、フィルターの使用を促します。フィルターを使用しない場合は、POPを使用すると、「未来」の期間が表示されます(たとえば、今日のデータは、技術的には来年からみた「昨年」であるため)。

#always_filter: {filters: [order_items.created_date: "before 0 minutes ago"]}

}

include: "/your_refinement_file" # ステップ2で使用したファイル名で更新してください

 

 

Step2) 基準日フィールドを持つ基底ビューをRefinementsします

以下のRefinementsテンプレートをペーストして、いくつかの参照を修正します。(既存のView名及び日付フィールド名称と一致させる)その後、準備しておいたPoP機能のロジックを既存の日付フィールドに適用します。

 

Refinements テンプレート

 

注:このコードブロックでは、既存のオブジェクトに一致するように参照を更新する必要がある行は左揃えになります

 

include: "/pop_support__template" # PoP実装のコアとなるヘルパーフィールドを含めます-つまり:上記のステップ1で説明したようにコードを貼り付けたファイルを含めます

include: "/your_original_view_file.view" # Refinementsの対象となるViewをここに含めます


view: +order_items {# view名を指定して + でRefinementsします。このビューはここに含めておく必要があり、またExploreにも含まれる必要があります。


#Refine YOUR date field by simply updating the dimension group name to match your base date field

dimension_group: created {

convert_tz: no #we need to inject the conversion before the date manipulation

datatype: datetime

sql:{% assign now_converted_to_date_with_timezone_sql = "${pop_support.now_converted_to_date_with_tz_sql::datetime}" %}{% assign now_unconverted_sql = pop_support.now_sql._sql %}{%comment%}PoP Supportテンプレートからロジックを引用し、その中に元のSQLを挿入します。Lookerに変換を実行させたい場合は${:: date}を使用しますが、生のSQLを抽出するには _sql を使用します{%endcomment%}

{% assign selected_period_size = selected_period_size._sql | strip%}

{%if selected_period_size == 'Day'%}{% assign pop_sql_using_now = "${pop_support.pop_sql_days_using_now::datetime}" %}{%elsif selected_period_size == 'Month'%}{% assign pop_sql_using_now = "${pop_support.pop_sql_months_using_now::datetime}" %}{%else%}{% assign pop_sql_using_now = "${pop_support.pop_sql_years_using_now::datetime}" %}{%endif%}

{% assign my_date_converted = now_converted_to_date_with_timezone_sql | replace:now_unconverted_sql,"${EXTENDED}" %}

{% if pop_support.periods_ago._in_query %}{{ pop_sql_using_now | replace: now_unconverted_sql, my_date_converted }}

{%else%}{{my_date_converted}}

{%endif%};; # パラメータ化されたselected-period-size-or-smart-default(以下に定義)を利用して、元のSQL(つまり、${EXTENDED})をカスタム PoP ロジック内にラップします

}



# 選択した期間サイズは、ユーザーがPoP期間サイズパラメータを調整しない場合に、各時間枠で使用するデフォルトの期間長を設定します

# YoYだけ有効にしたい場合は、ここで他のタイムフレームを隠すことでPOP Supportのパラメータから消せます

dimension: selected_period_size {

hidden: yes

sql:{%if pop_support.period_size._parameter_value != 'Default'%}{{pop_support.period_size._parameter_value}}

{% else %}

{% if created_date._is_selected %}Day

{% elsif created_month._is_selected %}Month

{% else %}Year

{% endif %}

{% endif %};; # created_dateとcreated_monthが指定のタイムフレームを指し示すようにこのLiquidを更新します。また潜在的に他のタイムフレーム(weekなど)が参照される可能性がある場合はそれも考慮して、このビュー内の他のRefinementsされたPoP日付フィールドを指すように編集します(存在する場合)

}



dimension: created_date_periods_ago_pivot {# ベースフィールド名に一致するように更新します。これは一般的なSQLロジックです(したがって、pop_supportテンプレートにあると思われるかもしれません)が、この特定の日付ディメンションのグループラベルに専用のピボットフィールドを作成できるように、この楔となるピボットフィールドをここに明示すると便利です。

label: "{% if _field._in_query%}Pop Period (Created {{selected_period_size._sql}}){%else%} Pivot for Period Over Period{%endif%}" # フィールドピッカーで「PIVOT ME」としておくと明確になります。ラベルは選択した期間サイズに基づいて動的出力ラベルが使用されます

group_label: "Created Date" #!Update this group label if necessary to make it fall in your date field's group_label

order_by_field: pop_support.periods_ago #sort numerically/chronologically.

sql:{% assign period_label_sql = "${pop_support.period_label_sql}" %}{% assign selected_period_size = selected_period_size._sql | strip%}{% assign label_using_selected_period_size = period_label_sql | replace: 'REPLACE_WITH_PERIOD',selected_period_size%}{{label_using_selected_period_size}};;#makes intuitive period labels

}



# Optional Validation Support field. If there's ever any confusion with the results of PoP, it's helpful to see the exact min and max times of your raw data flowing through.

# measure: pop_validation {

# view_label: "PoP - VALIDATION - TO BE HIDDEN"

# label: "Range of Raw Dates Included"

# description: "Note: does not reflect timezone conversion"

#sql:{%assign base_sql = '${TABLE}.created_at'%}concat(concat(min({{base_sql}}),' to '),max({{base_sql}}));;#!Paste the sql parameter value from the original date fields as the variable value for base_sql

# }

}

 

 

まとめ)特定のファイル名に基づいて、includesステートメントが必要に応じて更新されることを検証および確認します。

 

既知の問題

 

  • 基準日フィールドのSQLにダブルクォーテーション(“)が含まれている場合、これはLiquidベースの数式の構築に影響を与える可能性があります。ほとんどのダイアレクトでは、引用符の代わりに[]を使用するか、予約文字なしで物理列の名前を変更することにより、ダブルクォーテーションを回避できます。
  • 以前のバージョンでは、datatype:dateとともに「${pop_support.now_converted_to_date_with_tz_sql::date}」を使用していましたが、特定のダイアレクトと基本データ型でデータ型エラーが発生しました。 よって、「${pop_support.now_converted_to_date_with_tz_sql::datetime}」とdatatype:datetimeを使用するようにコードを更新しました。他のダイアレクトまたは他の生のデータ型が「${pop_support.now_converted_to_date_with_tz_sql::date}」に戻す必要がある場合に備えて、ここに付記しております。
  • (訳註:いくつかのダイアレクトでは、datetimeunknown に設定する必要がある場合があります)

その他追加の複雑さ及び考慮事項

  • このプロセスでは、まだ到来していない「将来の期間から見た過去データ」が表示されます。技術的には正確な表現ですが、ユーザーにとってはわかりにくい可能性があります。これをサポートするために、オプションのalways_filterまたはその他のフィルターを適用することを選択できます。
  • 正しいグループ化を維持できるよう、日付操作の前にタイムゾーン変換を行う必要があるため、lookerにconvert_tz:yesを実行させるのではなく、liquidを使用してタイムゾーン変換を適用します。これにより、Refinementsのロジックが複雑になり、タイムゾーン変換を使用しない場合は削除できる可能性がありますが、害はありません。
  • PoP機能は追加の日付フィールドにも適用できることがありますが、しかしながら作成すPoP ピボットディメンションの名前が競合しないように注意する必要があります
  • データベース構文の違いに注意してください... looker式を使用して、lookerに組み込まれているダイアレクト固有の日付関数の処理を活用していますが、一部のダイアレクトには、ファンアウト結合に関する課題など、まだ特定されていない他の制限がある場合があります(代わりにtype:crossを試す、など)。

おわりに

このパターンが、基本コードを複雑にしたり、PoP専用の新しいExploreを追加したりすることなく、チームが期間比較の機能をすばやく実装するのに役立つことを願っています。さらに、これにより、コードの編成と機能管理を改善するための改良を使用するように促されることを願っています。このPoPアプローチやRefinementsを実際に使ってみてフィードバックがありましたらお知らせください。

This topic has been closed for comments