Universal calendar support

[siljamardla]

Problem

Most of the time when we work with metrics, we want to slice their values by a time grain. We might be asking for the metric value for "yesterday" or "monthly" active users. Very often we want to see metrics side by side, sliced by the same time grains. For example, we might be asking for "yesterday's" revenue and active users numbers.

Every time we put a metric on a timeline, we need to think about the timestamp we use for that. In finance this is called "recognition", if I'm not mistaken. Let's say there's an order that was created at 23:55 on Jan 1st and finished at 00:15 on Jan 2nd. Should the revenue from that order be recognised for January 1st or January 2nd?

Therefore, it's part of metric definition to define the (default) timestamp to be used for adding the metric to a timeline.
That said, there are legitimate use cases where we might be interested in multiple versions of a metric, with different timestamp logic.

The recognition topic becomes especially tricky if we get into derived metrics. For example, order completion rate.

• orders_created metric should clearly depend on the timestamp when the order was created (Jan 1st)
• orders_finished metric should probably depend on the timestamp when the order was finished (Jan 2nd)
• now the ratio of orders_finished to orders_created on January 1st would be 0 and on January 2nd would be infinity (or simply return an error as we're trying to divide by 0)

The correct thing to do here is to define an additional metric:

• orders_in_finished_state that uses the order creation timestamp
• the orders_in_finished_state ratio to orders_created now makes sense and returns 100%, because the metric timestamps are aligned

Potential

There's potential to solve one of the most common reasons for a mismatch in numbers between different reports or use cases. There's potential to get rid of edge effects, that are most noticeable on highly granular aggregations (e.g. small user segments).
There's potential for derived metric validation (should not mix different timestamps).
These details would not matter much on monthly grains, because the edge effects would be so small. These days, we want analytics to be more and more granular and more operational. Meaning, our time grains get smaller and edge effects bigger.

BTW, when I ask my data teams to contribute to metric definitions and explain all this detail, a common feedback I get is that I'm overcomplicating. My response: if the contributors (domain data owners) find this confusing, why on Earth would we leave this complexity for data users to figure out and hope they get it right :)

Ideas for solutions

1. Enable defining a default timestamp for every metric
2. Enable querying based on a different timestamp, while automatically adjusting the joins and the naming of the output

[kurtStirewalt]

We also ran into this issue when working in the financial space. In our case, a security transaction like a stock purchase posts at one timestamp but then later settles at some other timestamp. A trading-floor manager wants to view their metrics in terms of the posting timestamp, while an accountant wants to view many of those same metrics in terms of the settled timestamp. In fact, there were several of these different kinds of timestamps that we had to specialize our metrics for in this way.

We solved the problem by defining parameterized metrics, where the parameters were properties that could be supplied at query time to specialize the metric before compiling it.

[mahum-26]

Yes, this is a real and common problem, and you’re not overcomplicating it. The usual best practice is to define a default “recognition timestamp” per metric and strictly prevent mixing metrics with different timestamps unless explicitly aligned, because otherwise edge effects and misleading ratios are inevitable. Supporting optional alternate timestamps with clear validation is the cleanest long-term solution.

[djwaldo]

Vendor input from the ThoughtSpot converter mapping (#285): ThoughtSpot supports custom calendars (month-offset fiscal years, 4-4-5 / 4-5-4 / 5-4-4 retail calendars, custom week starts) but the calendar definition lives outside the model as a connection-scoped object backed by a warehouse table, and only a calendar name reference travels with the model. Mapping that onto OSI today means the reference goes into custom_extensions and the definition is lost entirely, so any fiscal-aware metric silently reverts to Gregorian in every other tool. Strong +1 for a first-class calendar concept; even a minimal "named calendar reference + declared type" would let converters fail loudly instead of silently changing time semantics.