Alternate week granularity to allow for week starting either Sunday OR Monday and other granularities

[iktl]

Is your feature request related to a problem? Please describe.
We work with a dataset where some customers consider a week differently, where a "week" is starting either Sunday or Monday. Cube hits most of our use cases, but the week granularity option conforms to the standard week observed by Postgres (our current adaptor). So, for some customers, we cannot use a week granularity and this breaks some reporting needs we have.

Describe the solution you'd like
I'd like to be able to utilize a secondary week granularity option to opt out of the default behaviour, and roll up data to a week starting Sunday instead of a week starting Monday. I recognize that Postgres conforms to the ISO standard of a week starting on a Monday, and would want to maintain that as a the default behavior, hence adding week_sunday as an alternate option.

Describe alternatives you've considered
I've been able to patch in support for a week starting Sunday by hotfixing the Postgres adaptor and where Cube validates the incoming query, but I suspect (though have not tested) this will break any option to pre-aggregate.

[paveltiunov]

@iktl Hey Isaac! Thanks for posting! This is really advanced issue. So indeed there's support only for iso week in cube.js. And it isn't only SQL generation but also some data normalization and rolling time series generation. So this code scattered across different components including frontend stuff.

To design solution I think we need to understand use case better: is there only one week granularity for your project which should be customized or should you support two of them simultaneously each for different reports?

[iktl]

Hi Pavel!

Some additional context is that we work with advertising data, some of which comes from Adwords for customers that we work with. We end up with a lot of reporting data stored at a day-level but then report on it at various granularities / timeframes. The issue creeps in where Adwords supports reporting at a week level with some variations as seen here https://developers.google.com/adwords/api/docs/guides/reporting:

• THIS_WEEK_SUN_TODAY
• THIS_WEEK_MON_TODAY
• LAST_WEEK_SUN_SAT

This basically means that we have a 50/50 split of customers who think about weeks (whether it's Sun-Sat or Mon-Sun) completely differently, and we need to be able to report in our systems the same way they report in Adwords. We currently use an in-house reporting solution which lets us define which week variant to use, where users can control which of the two "weeks" they want to use for weekly rollups.

So, it would be fair to say that while running Cube in front of our data, depending on the context for the request coming in we would probably use each week variant in almost equal measure.

[paveltiunov]

@iktl Yeah. That makes sense. So I believe we'd need to extract small package cubejs-time-granularity where we should put all these granularity definitions and then implement this new granularity across all the drivers.

[paveltiunov]

Makes sense to join also minute granularity discussion here as well.

[ifokeev]

@paveltiunov I use something like this to make custom granularity in my cases:

 SELECT
    *,
    COUNT(id) OVER w as trades,
    COUNT(id) FILTER (WHERE side = 'buy') OVER w as buys_count,
    COUNT(id) FILTER (WHERE side = 'sell') OVER w as sells_count,
    FIRST_VALUE(price) OVER w as open,
    LAST_VALUE(price) OVER w as close,
    (date_trunc('hour', to_timestamp( timestamp/1000 )) 
        + date_part('minute', to_timestamp( timestamp/1000 )) :: INT / {self.min_interval} * INTERVAL '{self.min_interval} min')
        as interval_start
FROM
    "{self.table_name}"

WINDOW w AS (
    PARTITION BY date_trunc('hour', to_timestamp( timestamp/1000 )) 
        + date_part('minute', to_timestamp( timestamp/1000 )) :: INT / {self.min_interval} * INTERVAL '{self.min_interval} min'

        ORDER BY timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

so it could be just a minutes property under granularity

[paveltiunov]

@ifokeev Do you mean to allow define SQL for granularity? Something like

timestamp: {
  sql: `timestamp`,
  type: `time`,
  granularities: {
    minute: {
      sql: `date_trunc('minute', ${CUBE}.timestamp)`
    }
  }
}

[ifokeev]

@paveltiunov sorry for the delay response.

I have to notice we have a time dimensions format https://cube.dev/docs/query-format#time-dimensions-format and it's kinda syntax sugar over just dimensions with type time (and daterange filter in playground).
So my logic here just to allow user create own time dimensions with custom granularity property.

Your example is Ok, but maybe it's better to hide sql inside the drivers (but it will take more time to implement).

In conclusion: let's start with your example

[ifokeev]

UP. It's a very nice feature to have the compact view for dimensions with type time. Now I have to write:

    announcedDate: {
      sql: `${TABLE}."announcedDate"`,
      type: `time`
    },
    announcedDateMonth: {
      sql: `date_trunc('month', ${TABLE}."announcedDate")`,
      type: `time`
    },
    announcedDateWeek: {
      sql: `date_trunc('week', ${TABLE}."announcedDate")`,
      type: `time`
    },
    ...

I don't use timeDimensions because need to control granularity myself