> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-igor-core-668-docs-pre-agg-matching-switch-dims.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Matching queries with pre-aggregations
> When executing a query, Cube will try to match and fulfill it with the best available pre-aggregation.
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][ref-rollup-only-mode]
is enabled.
If you don't know why a query doesn't match a pre-aggregation, check
[common pitfalls](#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][ref-views], 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](#matching-algorithm) 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][ref-leaf-measures] in the query are [additive][ref-measure-additivity].
If the query contains [calculated measures][ref-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](#matching-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][ref-schema-joins-rel] 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][wiki-gcd]
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`][ref-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`][ref-conf-scheduled-refresh-time-zones]
configuration option.
If a query specifies a [custom granularity][ref-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`][ref-time-dimensions-format] rather
than an [`inDateRange` filter][ref-in-date-range]. 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][ref-ungrouped-queries]:
* The pre-aggregation should include [primary keys][ref-primary-key] 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][ref-switch] holds a predefined set of values rather than
data from the upstream data source, and [`case` measures][ref-case] 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:
```yaml title="YAML" theme={"dark"}
cubes:
- name: sales
# ...
pre_aggregations:
- name: rolling
measures:
- total
- r3_amount
- ytd_amount
dimensions:
- account
- product
time_dimension: date
granularity: month
```
```javascript title="JavaScript" theme={"dark"}
cube(`sales`, {
// ...
pre_aggregations: {
rolling: {
measures: [total, r3_amount, ytd_amount],
dimensions: [account, product],
time_dimension: date,
granularity: `month`
}
}
});
```
Pre-aggregations that *do* include the `switch` dimension keep matching as well,
so existing definitions are unaffected.
`switch` dimensions and `case` measures are powered by Tesseract, the
[next-generation data modeling engine][link-tesseract]. In versions before
v1.7.0, it was not enabled by default.
When `case` measures span multiple cubes, **define the `switch` dimension on a
single shared cube joined into every fact cube**, rather than defining a separate
one on each cube. All `case` measures then dispatch on the same dimension, so one
filter resolves every measure. With a `switch` dimension per cube, a single
filter pins only one of them; the others fall back to their full set of values,
and each value emits its own row — so the query returns extra rows.
Pre-aggregations match either way, so they accelerate that result rather than
preventing it.
## Matching multi-fact and multi-stage queries
A query can decompose into multiple subqueries — for example, a query over a
[multi-fact view][ref-multi-fact-views] runs a subquery per fact, and a query
with [multi-stage measures][ref-multi-stage] 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][link-tesseract]. In versions before v1.7.0, it was not enabled by default.
## 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](#matching-algorithm) and [common
pitfalls](#common-pitfalls) to understand why that part was an issue.
### Common pitfalls
* Most commonly, a query would not match a pre-aggregation because they contain
[non-additive measures][ref-measure-additivity].
See [this recipe][ref-non-additive-recipe] for workarounds.
* If a query uses any time zone other than `UTC`, please check the section on
[matching time dimensions](#matching-time-dimensions) and the
[`scheduled_refresh_time_zones`][ref-conf-scheduled-refresh-time-zones]
configuration option.
[ref-rollup-only-mode]: /docs/pre-aggregations/using-pre-aggregations#rollup-only-mode
[ref-schema-joins-rel]: /reference/data-modeling/joins#relationship
[wiki-gcd]: https://en.wikipedia.org/wiki/Greatest_common_divisor
[ref-measure-additivity]: /reference/data-modeling/measures#type
[ref-leaf-measures]: /reference/data-modeling/measures#type
[ref-calculated-measures]: /docs/data-modeling/overview#4-using-calculated-measures
[ref-non-strict-date-range-match]: /reference/data-modeling/pre-aggregations#allow_non_strict_date_range_match
[ref-non-additive-recipe]: /recipes/pre-aggregations/non-additivity
[ref-conf-scheduled-refresh-time-zones]: /reference/configuration/config#scheduled_refresh_time_zones
[ref-ungrouped-queries]: /reference/core-data-apis/queries#ungrouped-query
[ref-primary-key]: /reference/data-modeling/dimensions#primary_key
[ref-custom-granularity]: /reference/data-modeling/dimensions#granularities
[ref-views]: /docs/data-modeling/views
[ref-multi-fact-views]: /docs/data-modeling/multi-fact-views
[ref-multi-stage]: /docs/data-modeling/measures#multi_stage
[ref-switch]: /reference/data-modeling/dimensions#type
[ref-case]: /reference/data-modeling/measures#case
[ref-time-dimensions-format]: /reference/core-data-apis/rest-api/query-format#time-dimensions-format
[ref-in-date-range]: /reference/core-data-apis/rest-api/query-format#indaterange
[link-tesseract]: https://cube.dev/blog/introducing-tesseract