Skip to main content
Since pre-aggregations contain a condensed representation of the data from the upstream data source (rather than a copy of that data), Cube needs to ensure that fulfilling a query with a pre-aggregation is possible and doing so will produce correct results. If there’s no matching pre-aggregation, Cube will fall back to querying the upstream data source, unless the rollup-only mode is enabled.
If you don’t know why a query doesn’t match a pre-aggregation, check common pitfalls first.

Eligible pre-aggregations

Cube goes through the following steps to determine if there are any pre-aggregations matching a query:
  • Members (e.g., dimensions, measures, etc.) are extracted from the query. If the query contains members of a view, they are substituted by respective members of cubes where they are defined. It means that pre-aggregations defined for cube members would also match queries with view members. There’s no need to define additional pre-aggregations for views.
  • Cube looks for pre-aggregations in all cubes that define members in the query.
  • Pre-aggregations are tested in the order they are defined in the data model file. However, rollup pre-aggregations are tested before original_sql pre-aggregations.
  • The first pre-aggregation that matches a query is used.

Matching algorithm

Cube goes through the following steps to determine whether a query matches a particular eligible pre-aggregation:
See the details for each step:
  • Is query leaf-measure additive? Cube checks that all leaf measures in the query are additive. If the query contains calculated measures (e.g., measures defined as {sum} / {count}), then referenced leaf measures will be checked for additivity.
  • Does every member of the query exist in the pre-aggregation? Cube checks that the pre-aggregation contains all dimensions, filter dimensions, and leaf measures from the query. switch dimensions are an exception: they don’t have to be included in the pre-aggregation.
  • Are any query measures multiplied in the cube’s data model? Cube checks if any measures are multiplied via a one_to_many relationship between cubes in the query.
  • Does the query specify granularity for its time dimension? Cube checks that the time dimension granularity is set in the query.
  • Are query filter dimensions included in its own dimensions? Cube checks that all filter dimensions are also included as dimensions in the query.

Matching time dimensions

There are extra considerations that apply to matching time dimensions.
  • Time dimension and granularity in the query together act as a dimension. If the date range isn’t aligned with granularity, a common granularity is used. This common granularity is selected using the greatest common divisor across both the query and pre-aggregation. For example, the common granularity between hour and day is hour because both hour and day can be divided by hour.
  • The query’s granularity’s date range must match the start date and end date from time dimensions. For example, when using a granularity of month, the values should be the start and end days of the month, i.e., ['2020-01-01T00:00:00.000', '2020-01-31T23:59:59.999']; when the granularity is day, the values should be the start and end hours of the day, i.e., ['2020-01-01T00:00:00.000', '2020-01-01T23:59:59.999']. Date ranges are inclusive, and the minimum granularity is second. By default, this is ensured via the allow_non_strict_date_range_match parameter of pre-aggregations: it allows to match non-strict date ranges and is set to true by default.
  • The time zone in the query must match the time zone of a pre-aggregation. You can configure a list of time zones that pre-aggregations will be built for using the scheduled_refresh_time_zones configuration option.
If a query specifies a custom granularity for its time dimension, then a matching pre-aggregation with the same custom granularity will be used even if there is also an matching pre-aggregation with a default granularity (e.g., day or month).
Provide the date range via timeDimensions rather than an inDateRange filter. A date range expressed as a filter is applied as a generic dimension filter, so it matches only when that time dimension is also listed in the pre-aggregation’s dimensions — the granularity matching rules above don’t apply to it.

Matching ungrouped queries

There are extra considerations that apply to matching ungrouped queries:
  • The pre-aggregation should include primary keys of all cubes involved in the query.
  • If multiple cubes are referenced in the query, the pre-aggregation should include only members of these cubes.

Matching switch dimensions

A switch dimension holds a predefined set of values rather than data from the upstream data source, and case measures dispatch on the selected value. Because its values are known from the data model, a pre-aggregation does not need to include a switch dimension to match a query that uses one: the selected value is applied over the pre-aggregation scan. Leaving the switch dimension out keeps the pre-aggregation small — including it multiplies the rows by every value in the set:
Pre-aggregations that do include the switch dimension keep matching as well, so existing definitions are unaffected. Matching is unaffected by how the switch dimension is modeled across cubes. If a query returns more rows than expected, that’s a modeling concern — see case measures — and pre-aggregations accelerate that result rather than preventing it.
switch dimensions and case measures are powered by Tesseract, the next-generation data modeling engine. In versions before v1.7.0, it was not enabled by default.

Matching multi-fact and multi-stage queries

A query can decompose into multiple subqueries — for example, a query over a multi-fact view runs a subquery per fact, and a query with multi-stage measures runs a subquery per stage. Cube matches a pre-aggregation to each subquery independently, so a single query can be served by several pre-aggregations at once — one per subquery — rather than requiring a single pre-aggregation that covers the whole query. Matching is all-or-nothing across the query, though: if any subquery can’t be served, the pre-aggregations matched for the others are dropped too and the whole query runs against the upstream data source. A common cause is a pre-aggregation keyed on its own cube’s time dimension while the query groups by another cube’s — the two are equal by the join condition, but matching only considers members the pre-aggregation stores. Key every pre-aggregation on the time dimension the query groups by. Results stay correct either way; only the acceleration is lost.
Matching separate pre-aggregations to multi-fact and multi-stage subqueries is powered by Tesseract, the next-generation data modeling engine. In versions before v1.7.0, it was not enabled by default.

Matching time-shift measures

Cube can serve a time-shift measure from a rollup pre-aggregation that stores the referenced measure and the time dimension: it shifts the stored time dimension values by the interval. A time shift on a time dimension of a calendar cube cannot be applied this way. For such a time shift, add the time-shift measure to the measures of the rollup:
Each row of revenue_by_day holds the revenue of the prior fiscal year for its day and status. A query with the same dimensions and granularity matches the rollup. Cube can also roll the stored values up, e.g., to the month granularity or without status. This requires that the time-shift measure meets all of these conditions:
  • Its sql references one measure, e.g., {revenue}. An expression, e.g., {revenue} * 2, does not roll up.
  • Its type is number, string, time, boolean, sum, min, max, or avg.
  • It has no grain, filter, filters, case, or rolling_window parameters, and no group_by, reduce_by, or add_group_by parameters.
  • The measure that it references is additive and has no rolling_window parameter. That measure can also be another time-shift measure that meets these conditions.
Cube rolls the stored values up with the aggregation of the referenced measure. For example, a number time-shift measure that references a max measure rolls up with max. To roll the stored values up, the query must group or filter by each time dimension in the rollup (in time_dimension or dimensions) that the time shift applies to. A time shift with time_dimension applies to that time dimension. A time shift without time_dimension, e.g., a named time shift, applies to all time dimensions in the rollup. If the query does not use one of them, Cube does not read the stored values of the time-shift measure.

Troubleshooting

If you’re not sure why a query does not match a pre-aggregation, try to identify the part of the query that prevents it from matching. You can do that by removing measures, dimensions, filters, etc. from your query until it matches. Then, refer to the matching algorithm and common pitfalls to understand why that part was an issue.

Common pitfalls