Using originalSql and rollup Pre-aggregations Effectively

Use case

For cubes that are built from an expensive SQL query, we can optimize pre-aggregation builds so that they don't have to re-run the SQL query.

Configuration

We can do this by creating a pre-aggregation of type originalSql on the source (also known as internal) database, and then configuring our existing rollup pre-aggregations to use the originalSql pre-aggregation with the useOriginalSqlPreAggregations property.

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.