Query Parameters

Is your feature request related to a problem? Please describe.

Given some records with a date field, we want to do aging of the records with regards to the current date or another specified anchor date.
Example:

• Given a Patient table, with a birthDate time field, we would like to be able to group Patients by age slices (0-18 years, 19-30 years, etc.), and be able to filter specifically on this age or age group. Example code:

ageGroup: {
      sql: `
      CASE
       -- replace NOW() calls with the query param
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, NOW()) <= 18 THEN '0-18'
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, NOW()) <= 30 THEN '19-30'
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, NOW()) <= 45 THEN '31-45'
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, NOW()) <= 60 THEN '46-60'
        ELSE '61+'
      END
      `,
      type: `string`
    },

• Given a Charges & Payments table, we want to be able to categorize the uncollected Charges based on the aging of the Charge (0-30 days, 31-60 days, etc.) and be able to filter for a certain category in order to produce a drilled-down version of the aging category with respect to which Patient has outsanding Charges within the said category.

Describe alternatives you've considered

A potential solution is to precalculate the various differences between any two dates of interest and store the result in a materialized table, "CalendarAging" table.

In the first case, the CalendarAging table has a number of records equal to the cross join of the past 100 years worth of dates with the past ~ up to 5 years of last dates, for a total of ~67 million records. The sheer number of produced records could be enough to reduce the usability of such a solution both performance and costs wise (depending on the rates & frequency of access).

Pavel pointed out that the standard solution in the industry is to reduce the number of possible use cases available to end-users by creating a much smaller, finite set of cases. Example: "age at the end of current year" or "age at the end of 2015".

Describe the solution you'd like

As Pavel pointed out on DM in Slack, query parameters break the principle of invariant data representation leveraged by OLAP cubes, thereby undermining the ability to create pre-aggregations. However, there are multiple use cases where this may be an important feature to have nevertheless, as Pavel will discuss below.

An optimal solution would allow us to reference query parameters in the dimension & measure definitions, as follows:

ageGroup: {
      sql: `
      CASE
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, {{params.anchorDate}}) <= 18 THEN '0-18'
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, {{params.anchorDate}}) <= 30 THEN '19-30'
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, {{params.anchorDate}}) <= 45 THEN '31-45'
        WHEN TIMESTAMPDIFF(YEAR, ${Patient}.birth_date, {{params.anchorDate}}) <= 60 THEN '46-60'
        ELSE '61+'
      END
      `,
      type: `string`
    },

Interesting questions to consider:

• How can this feature be documented in such a way that the developers are aware of the potential performance costs of using parameters OR are aware of how to leverage parameters in a way that will enable Cube.js to build a key based on params which will be used in pre-aggregation?
• Should the query params format require that the type of the values passed be explicitly defined?
• Should the query params format allow objects and deeply nested properties?
• Should the query params functionality allow more complex use of templated languages such as mustache or should it be reserved to simple use-cases such as SQL native parameters (named, positional)?

There you go!
Thanks again for the good work and may Cube.js keep shining! 🚀