Time Metrics

​

Introduction

Modeling time based metrics is a common need of semantic layers. The Time Metric accessor is a wrapper around Honeydew metrics to allow easily defining time metrics.

The time metric allows to extend a pure aggregation with time properties - grain, offest, join field to a time spine and more.

​

Time Metrics

This creates a time metric from an aggregation:

TIME_METRIC(
	metric or aggregate expression,
    offset => interval,
    -- join based on field and grain
	grain => grain (day/week/month/quarter/year),
    date_field => join attribute,
    -- join based on a range
    date_range_start => join range start date expression,
	date_range_end => join range end date expression
)

Parameters:

• expression: either a Honeydew metric or aggregation expression. Must not include a GROUP BY.
• offset: based on Snowflake interval expression syntax, sets a negative interval to offset the grain. Note that if has a higher grain than the chosen grain (i.e. a week interval on a monthly metric) it might get truncated to the chosen grain.
• grain: based on Snowflake date or time part syntax - day/week/month/quarter/year. Sets the minimal grain of a metric.
• date_field: a field that is used to join to the time spine. When combined with grain a date_field is truncated to the grain.
• date_range_start, date_range_end: a pair of fields that are used to join to the time spine.

All parameters (except for expression) are optional. However, a time metric is valid only with join configuration (date_field or date_range_*) set.

​

Example

Consider the following metric sum_qty in lineitem entity:

sum(lineitem.l_quantity)

Time metrics connect automatically to the time spine entity date.

However sum_qty is not a time metric.

A query that groups it by date.year will have a result such as

If a time metric named sum_qty_when_shipped, that counts shipped quantity, is built of top of it

TIME_METRIC(
    lineitem.sum_qty,
    date_field => lineitem.l_shipdate)

Then connecting it to date will be done by joining it to the l_shipdate column, attributing all quantity to the shipping date

However, a different metric sum_qty_when_received may count quantity to be shipped by counting quantity on the date an order was received:

TIME_METRIC(
    lineitem.sum_qty,
    date_field => lineitem.l_receiptdate)

Comparing the two would show that shipped quantity is less than the quantity to be shipped at the first year, and then follows:

May also use time metrics to look at a previous time period. For example, the following metric sum_qty_when_shipped_prev_year

TIME_METRIC(
    lineitem.sum_qty,
    date_field => lineitem.l_shipdate,
    offset => '1 year')

This can be used for YoY growth calculations (note to coalesce the null to zero).

​

Composing Time Metrics

​

Entity default date field

The sum_qty metric in the lineitem entity is not a time metric. When joined to a time spine it will not connect:

However adding a default date field in an entity would make all metrics in that entity to become time metrics over the default date field.

For example, the lineitem entity can add as a default date field the lineitem.l_shipdate:

type: entity
name: lineitem
default_date_field: lineitem.l_shipdate
... other entity parameters

Then the sum_qty metric knows how to join on time

​

Combining qualifiers

It is possible to compose time metrics by combining qualifiers, i.e.

orders.t1 = TIME_METRIC(orders.count, date_field => orders.order_date)
-- compose a time metric by adding a grain to t1
orders.t2 = TIME_METRIC(orders.t1, grain => month)
-- compose a time metric by adding an offset to t2
orders.t3 = TIME_METRIC(orders.t2, offset => '1 month')

-- t3 is equivalent to
orders.t3_full = TIME_METRIC(
    orders.count,
    date_field => orders.order_date,
    grain => month,
    offset => '1 month'
)

Note that a time metric is computable only when has both date_field and grain set.

​

Common Examples

​

Revenue on close date

A metric that sets the time spine, but not a grain.

TIME_METRIC(order_lines.revenue, date_field => orders.close_date)

​

Revenue based on a different time spine

TIME_METRIC(order_lines.revenue, date_field => orders.shipping_date)

​

Monthly revenue

TIME_METRIC(order_lines.revenue, grain => month)

​

Monthly revenue at previous month

TIME_METRIC(order_lines.revenue, grain => month, offset => '1 month')

​

Monthly revenue at next month

TIME_METRIC(order_lines.revenue, grain => month, offset => '-1 month')

​

Same time last year

TIME_METRIC(order_lines.revenue, offset => '12 months')

​

Balance on the last day of the month

TIME_METRIC(MAX_BY(orders.balance, orders.date), grain => month)

​

Revenue of the 2nd week of the month

TIME_METRIC(order_lines.revenue, grain => month) FILTER (WHERE WEEK(date.month) = 2)

​

MoM growth

TIME_METRIC(order_lines.revenue, grain => month) /
TIME_METRIC(order_lines.revenue, grain => month, offset = '1 month')

​

Daily contribution

TIME_METRIC(order_lines.revenue, grain => day) /
TIME_METRIC(order_lines.revenue, grain => month)

​

Monthly contribution to monthly total

TIME_METRIC(order_lines.revenue, grain => month) /
TIME_METRIC(order_lines.revenue, grain => month) GROUP BY (date.month)

​

Monthly contribution to city

TIME_METRIC(order_lines.revenue, grain => month) / (order_lines.revenue GROUP BY (*, orders.city))

Generates

​

Common Examples - Cumulative

​

Last 30 days rolling revenue

TIME_METRIC(order_lines.revenue, date_range_start = orders.close_date - interval '30 days', date_range_end => orders.close_date)

Note this is a daily metric (each day has a different value) but uses a window to accumulate.

​

Last 30 days rolling revenue, yesterday

TIME_METRIC(order_lines.revenue, date_range_start = orders.close_date - interval '30 days', date_range_end => orders.close_date, offset = '1 day')

​

Month-to-date (MTD) revenue

TIME_METRIC(order_lines.revenue, date_range_start => DATE_TRUNC('month', orders.close_date), date_range_end => orders.close_date)

​

Year-to-date (YTD) revenue

TIME_METRIC(order_lines.revenue, date_range_start => DATE_TRUNC('year', orders.close_date), date_range_end => orders.close_date)

​

Monthly open deals

TIME_METRIC(deals.count, grain => month, date_range_start => deals.create_date, date_range_end => deals.close_date)