Using `original_sql` and `rollup` pre-aggregations effectively

Recipe for materializing expensive cube SQL once with original_sql, then reusing it across rollup pre-aggregations and ad hoc queries.

Use case

You can use the sql parameter to define cubes over arbitrary SQL queries. Sometimes, these queries might be fairly complex and take substantial time to execute. That’s totally okay because you can use pre-aggregations to accelerate queries to such cubes.

However, if you have more than one pre-aggregation that references members of such a cube, its sql expression would have to be executed each time every pre-aggregation is built. This also the case if you run both pre-aggregated and non-pre-aggregated queries against such a cube.

A special original_sql pre-aggregation can help:

• First, it will materialize the results of the sql expression in the data source.
• Then, it will make these results available to other rollup pre-aggregations and non-pre-aggregated queries.

Configuration

We can do this by creating a pre-aggregation of type original_sql on the data source database, and then configuring our existing rollup pre-aggregations to use the original_sql pre-aggregation with the use_original_sql_pre_aggregations property.

Storing pre-aggregations on an internal database requires write-access. Please ensure that your database driver is not configured with readOnly: true.

YAML

cubes:
  - name: orders
    sql: "<YOUR_EXPENSIVE_SQL_QUERY HERE>"

    pre_aggregations:
      - name: base
        type: original_sql
        external: false

      - name: main
        dimensions:
          - id
          - name
        measures:
          - count
        time_dimension: created_at
        granularity: day
        use_original_sql_pre_aggregations: true

Result

With the above data model, the main pre-aggregation is built from the base pre-aggregation.