Cube.js: Dynamic Measures using FILTER_PARAMS ERROR when calculated from Pre-Aggregations

[ferrants]

Describe the bug
Non-Additive measures that can be derived from a roll-up Additive or Leaf Additive measures should be derivable from a Pre-aggregate table query. measures that have filters that can be determined by the Pre-aggregate's Dimensions should be applied at query-time, not rolled into the preaggregate. Measure filters using FILTER_PARAMS that are in the Pre-aggregate's dimensions should use the Pre-aggregate table to calculate them.

To Reproduce

With this Cube Definition:

cube(`SearchResults`, {

    sql: `SELECT * FROM search_result`,
    measures: {
        count: {
            sql: `${CUBE}.raw_count`,
            type: `sum`,
        },
        count_first_half: {
            sql: `${CUBE.count}`,
            type: `number`,
            filters: [
                {
                    sql: `${CUBE.search_time} < ${FILTER_PARAMS.SearchResults.search_time.filter((from, to) => {
                        return `${from}::timestamp + (${to}::timestamp - ${from}::timestamp) / 2`
                    })}`,
                },
            ],
        },
        count_second_half: {
            sql: `${CUBE.count}`,
            type: `number`,
            filters: [
                {
                    sql: `${CUBE.search_time} >= ${FILTER_PARAMS.SearchResults.search_time.filter((from, to) => {
                        return `${from}::timestamp + (${to}::timestamp - ${from}::timestamp) / 2`
                    })}`,
                },
            ],
        },
        count_change: {
            sql: `${CUBE.count_second_half} - ${CUBE.count_first_half}`,
            type: `number`,
        },
        count_pct_change: {
            sql: `
                (${CUBE.count_second_half} - ${CUBE.count_first_half}) * 1.0
                / ${CUBE.count_first_half}
            `,
            type: `number`,
        },
    },

    dimensions: {
        id: {
            sql: `id`,
            type: `string`,
            primaryKey: true,
        },

        asin: {
            sql: `asin`,
            type: `string`,
        },

        search_time: {
            sql: `search_time`,
            type: `time`,
        },

        keyword_id: {
            sql: `keyword_id`,
            type: `string`,
        },
    },

    preAggregations: {
        my_preagg: {
            type: `rollup`,
            measureReferences: [CUBE.count],
            dimensionReferences: [CUBE.keyword_id, CUBE.asin],
            timeDimensionReference: CUBE.search_time,
            granularity: `day`,
        },
    },
})

And Query:

{
  "measures": [
    "SearchResults.count",
    "SearchResults.count_first_half"
  ],
  "timeDimensions": [
    {
      "dimension": "SearchResults.search_time",
      "dateRange": "This week"
    }
  ],
  "dimensions": [
    "SearchResults.keyword_id"
  ],
  "filters": []
}

Results in SQL:

SELECT
  "search_results__keyword_id" "search_results__keyword_id",
  sum("search_results__count") "search_results__count",
  CASE
    WHEN (
      "search_results".search_time < $ 1 :: timestamp + ($ 2 :: timestamp - $ 3 :: timestamp) / 2
    ) THEN sum("search_results__count")
  END "search_results__count_first_half"
FROM
  cubejs.search_results_my_preagg
WHERE
  (
    "search_results__search_time_date" >= ($ 4 :: timestamptz :: timestamptz AT TIME ZONE 'UTC')
    AND "search_results__search_time_date" <= ($ 5 :: timestamptz :: timestamptz AT TIME ZONE 'UTC')
  )
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10000

Results in the error

Error: Error: relation "search_results" does not exist

Expected behavior

1. There should be no error. The measure filter "search_results".search_time should actually be "search_results__search_time_date"
2. The measure filters are respected and the preaggregate is used (this seems to be the case)

Screenshots

Version:

@cubejs-backend/postgres-driver: 0.11.11
@cubejs-backend/server: 0.11.16
@cubejs-backend/serverless: 0.11.16
@cubejs-backend/serverless-aws: 0.11.16

Additional context
Add any other context about the problem here.

[ferrants]

@paveltiunov suggested:

The only way here is to put these comparison definitions in cube SQL to pre-aggregate it I think.

Because the time range is dynamic, based on FILTER_PARAMS the data saved in the preaggregation wouldn't be usable. The raw numbers stored in the preaggregation should be able to determine the correct resulting value for these metrics.

I have also attempted joining back on the same cube or an extended cube so I can join back on the same data and specify different timeDimension.dateRange, but you can't join two preaggregate tables together, so we wouldn't get the performance gains we are looking for. I am going to create a separate github issue feature request for joining preaggregate tables together.

[paveltiunov]

@ferrants Hey Matt! Thanks for posting this! This is really interesting use case. Theoretically we can check for references used in a measure and if those are included in rollup and also Leaf Measure Additive we can allow to calculate such measure using rollup. From this perspective it's just a bug. On other hand there will be #103 issue fix which will prohibit such measures from using rollups because it's really hard to check if some custom FILTER_PARAMS usage is aligned with rollup granularity or not.

So for now we can tag this as a bug and even fix it. In general this behavior for your specific use case will be unreliable though. From our experience most of this comparison stuff is made in post processing and it's probably simpler route to go. Given that you have rollup in place performance shouldn't be a problem. Would love to hear your thoughts here.

[ferrants]

@paveltiunov That makes sense and doing it post-processing is something I've considered. Here are my hold-ups with post-processing:

• I want to be able to sort and filter on differences between the timeframes. Something like, "show me all that improved by more than 5%" or "show me the ones that changed the most, in descending order". Post-processing wouldn't really work for this.
• Even if I'm not trying to use the difference measure in ordering or filtering, I want to report the change. If I'm post-processing, I'd need to run two queries in series. One to get the objects, the next query I'd need to filter to those same objects, which is a challenge. I could return all values for each and then join in-memory, but we would bump up against the 50k response limit and most of the time we're showing 10 - 100 records, so our limit is actually much lower. This would add a lot of overhead.
• It should be possible with a single SQL query, so I'm maybe getting held up on doing it with a single query

Regarding #103, and including if the FILTER_PARAMS are in the rollup, I don't think a user should need to define the measures with FILTER_PARAMS as ones in measureReferences in the rollup. If the measure queried has all the dependent dimensions and measures defined in the rollup, I think I should be able to query the rollup for that data. For example:

my_preagg: {
            type: `rollup`,
            measureReferences: [CUBE.count],
            dimensionReferences: [CUBE.keyword_id, CUBE.asin],
            timeDimensionReference: CUBE.search_time,
            granularity: `day`,
        },

This has the count and the search_time references, which are all the count_first_half needs to be calculated. If I query for count_first_half, i should use this preaggregate.

[paveltiunov]

@ferrants Gotcha. If you want to filter on that then it's definitely need to be done in SQL.

I agree that we should support Leaf Measure Additive rollup optimization whenever possible. And in this case there's no problem with this usage. We just need to fix this bug. The thing here is #103 caused by granularity align. For example if you pass same date to from and to in

${from}::timestamp + (${to}::timestamp - ${from}::timestamp) / 2

you'll find it to be the 12 PM this date. If daily rollup will be used in this case there will be result discrepancy between query with and w/o rollup. It's because such timestamp won't be aligned with daily granularity of rollup and in fact it requires hour granularity.

[ferrants]

@paveltiunov , I took a crack at fixing this issue in https://github.com/cube-js/cube.js/pull/273
I still have a bit more work to do in there, but if you could look and give me any feedback, that'd be appreciated!