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.