Matching queries with pre-aggregations

​

Eligible pre-aggregations

• 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

• 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

• 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.

​

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

cubes:
  - name: sales
    # ...

    pre_aggregations:
      - name: rolling
        measures:
          - total
          - r3_amount
          - ytd_amount
        dimensions:
          - account
          - product
        time_dimension: date
        granularity: month

cube(`sales`, {
  // ...

  pre_aggregations: {
    rolling: {
      measures: [total, r3_amount, ytd_amount],
      dimensions: [account, product],
      time_dimension: date,
      granularity: `month`
    }
  }
});

​

Matching multi-fact and multi-stage queries

​

Troubleshooting

​

Common pitfalls

• Most commonly, a query would not match a pre-aggregation because they contain non-additive measures.
  See this recipe for workarounds.
• If a query uses any time zone other than UTC, please check the section on matching time dimensions and the scheduled_refresh_time_zones configuration option.