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.

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

YAML

JavaScript

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.

Accelerating non-additive measures Schema Generation