Support more options for rollingWindow leading/trailing values

[bentesha]

At the moment, rollingWindow leading and trailing parameters supports values in the following format: (-?\d+) (minute|hour|day|week|month|year) which can be used to define the window size to the right and left of the offset.

While this works OK in many scenarios, there are circumstances in which the correct value for leading/trailing parameter cannot be determined at schema creation.

For example, let's say you want to calculate the total rolling revenue since the beginning of the last month to today. To achieve this, you will need to know the query's date range (which is only available at query creation) so that you can precisely calculate the number of days to set to the trailing parameter.

{
  offset: 'start',
  trailing: '? day' // how many days to the start of the previous month?
}

To overcome such limitations, additional options can be added to enable specifying beginning/ending of a period, e.g start/end of month, year, week, n months ago etc.

One way would be to add additional options such as:

{
  offset: 'start',
  trailing: 'start of 1 month ago'
}

Or something similar could be attained by introducing a modifier after a pipe symbol, to specify the start/end of a period as follow

{
  offset: 'start',
  trailing: '1 month | start of month'
}

The period part can optionally be omitted:

{
  offset: 'start',
  leading: '| end of year' // calculate running total until the period's year-end
}

My suggestions might not be the best way to implement this feature. But I do hope they serve well to explain how this improvement can be implemented.

I am open to your ideas and suggestions.

Thanks

[paveltiunov]

@bentesha Hey Benedict! Thanks for posting this! It's a great use case! So I believe it'll be very hard to describe all the possible cases using just interval literals and logic. I'd suggest to go with arbitrary sql definitions for such rolling intervals. For example for start of the month it'll be:

{
  offset: 'start',
  trailing: {
    sql: `date_trunc('month', ${START_DATE})`
  }
}

What do you think?

[bentesha]

@paveltiunov This would be a lot more powerful. I have never looked into CubeJs source code. How hard would it be for someone new to CubeJs to accomplish this? Would love to create a PR

[paveltiunov]

@bentesha I believe not that hard for this particular change. There's only one place which controls SQL generated for trailing and leading intervals:
https://github.com/cube-js/cube.js/blob/0744b132b15cd4c285bcc53b0d9f5916297f54c1/packages/cubejs-schema-compiler/adapter/BaseQuery.js#L218
https://github.com/cube-js/cube.js/blob/0744b132b15cd4c285bcc53b0d9f5916297f54c1/packages/cubejs-schema-compiler/adapter/BaseQuery.js#L224

evaluateSql should be used there. As an usage example you can use this one:
https://github.com/cube-js/cube.js/blob/0744b132b15cd4c285bcc53b0d9f5916297f54c1/packages/cubejs-schema-compiler/adapter/BaseQuery.js#L1170

START_DATE and END_DATE should be passed using parametrizedContextSymbols(): https://github.com/cube-js/cube.js/blob/0744b132b15cd4c285bcc53b0d9f5916297f54c1/packages/cubejs-schema-compiler/adapter/BaseQuery.js#L1331

Mappings for context symbols: https://github.com/cube-js/cube.js/blob/a230b5a9a826971d41602c25629a55e5a68be9f9/packages/cubejs-schema-compiler/compiler/CubeSymbols.js#L5

evaluateSymbolSqlWithContext() can be used to pass START_DATE and END_DATE to parametrizedContextSymbols:
https://github.com/cube-js/cube.js/blob/0744b132b15cd4c285bcc53b0d9f5916297f54c1/packages/cubejs-schema-compiler/adapter/BaseQuery.js#L1046

For example like this:
https://github.com/cube-js/cube.js/blob/0744b132b15cd4c285bcc53b0d9f5916297f54c1/packages/cubejs-schema-compiler/adapter/BaseQuery.js#L645

[bentesha]

@paveltiunov Thanks for the info. I will look into the source code over the weekend. Will keep you posted

[bentesha]

Hi! @paveltiunov
Inside rollingWindowDateJoinCondition function, how can we determine the name of the cube, which is required by evaluateSql?

Let me know if below is the correct way to evaluate sql

In CubeSymbols.js

CONTEXT_SYMBOLS = {
  ...,
  START_DATE: 'startDate',
  END_DATE: 'endDate'
}

In rollingWindowDateJoinCondition function

const fn = () => this.evaluateSql(cubeName, trailingInterval.sql)
const evaluatedSql = this.evaluateSymbolSqlWithContext(fn, { startDate, endDate })

[paveltiunov]

@bentesha This is really great question! I believe you'd need to change rollingWindowDateJoinCondition signature and add this param. It's available as a cube() method of BaseMeasure: https://github.com/cube-js/cube.js/blob/70280990a17c622799ae0d5b0f1342fe951321ec/packages/cubejs-schema-compiler/adapter/BaseMeasure.js#L90

[bentesha]

@paveltiunov thanks for the help. I managed to resolve it.
Can you explain to me how to properly register a symbol?

I have START_DATE and END_DATE added in CONTEXT_SYMBOLS in CubeSymbols.js but I think I need to do a bit more for it to resolve.

{
  ...,
  START_DATE: 'startDate',
  END_DATE: 'endDate'
}

I am getting an error START_DATE is not defined

I am passing a context with startDate and endDate values

this.evaluateSymbolSqlWithContext(fn, { startDate, endDate })

What is the rationship between symbols and context variables? I guess under the CONTEXT_SYMBOLS object, key is the name of the symbol, and value is the name of the context variable associated with it?

[paveltiunov]

@bentesha That's right! The only thing is left is to pass it in parametrizedContextSymbols():
https://github.com/cube-js/cube.js/blob/0744b132b15cd4c285bcc53b0d9f5916297f54c1/packages/cubejs-schema-compiler/adapter/BaseQuery.js#L1331

You can do that by accessing safeEvaluateSymbolContext() inside parametrizedContextSymbols().

[bentesha]

@paveltiunov thanks! Will give it a shot

[bentesha]

@paveltiunov
The sql `date_trunc('month', ${START_DATE})` gets compiled into function

() => `date_trunc('month', ${START_DATE})`

Which results into an error START_DATE is not defined when the function is called

I am not sure what I am missing. How am I supposed to make the compiler aware of the START_DATE variable, so that it is added to the function signature:

(START_DATE) => `date_trunc('month', ${START_DATE})`

Which part of CubeJs control this?

[paveltiunov]

@bentesha Great question! CubePropContextTranspiler is responsible for that: https://github.com/cube-js/cube.js/blob/4402a85febc2eac7c08d8d72ab863b1ac13e4c74/packages/cubejs-schema-compiler/compiler/CubePropContextTranspiler.js#L38

If you defined START_DATE in CONTEXT_SYMBOLS it should work. It also transpiles only sql fields. So please make sure your signature is like this:

fromBegginingOfMonth: {
  sql: `amount`,
  type: `sum`,
  rollingWindow: {
    offset: 'start',
    trailing: {
      sql: `date_trunc('month', ${START_DATE})`
    }
  }
}

[bentesha]

@paveltiunov Thanks. Wil look into it

[bentesha]

Hello @paveltiunov
The CubeSymbols.resolveSymbol method expects resolved symbol values to be of object types. See below line:

https://github.com/cube-js/cube.js/blob/b183f4b242d9db7c987a7dea38b1fc6b2b24dd3b/packages/cubejs-schema-compiler/compiler/CubeSymbols.js#L117

This would not work if the symbol resolved to a string value. As in our case, we expect START_DATE and END_DATE to resolve to a string value i.e TIMESTAMP($0$) for example. This would cause this line to throw an error. Arbitrary properties cannot be added to a primitive type.

To work this around, I replaced START_DATE and END_DATE with INTERVAL symbol whose value is passed as an object with properties startDate and endDate:

interval = {
  startDate: fromDate,
  endDate: toDate
}

This changes our measure definition to something like below:

fromBegginingOfMonth: {
  sql: `amount`,
  type: `sum`,
  rollingWindow: {
    offset: 'start',
    trailing: {
      sql: `date_trunc('month', ${INTERVAL.startDate})`
    }
  }
}

Though I would have loved the variable to be just DATE, resolving to either start date or end date depending on the value of offset.

fromBegginingOfMonth: {
  sql: `amount`,
  type: `sum`,
  rollingWindow: {
    offset: 'start',
    trailing: {
      sql: `date_trunc('month', ${DATE})`
    }
  }
}

Let me know what you think

[paveltiunov]

@bentesha Yeah. I see. INTERVAL solution looks good to me. Let's stick with it. DATA is nice shortcut but I believe it's great to always have both startDate and endDate in context.