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.

cube('Orders', {
  sql: `<YOUR_EXPENSIVE_SQL_QUERY HERE>`,

  preAggregations: {
    base: {
      type: `originalSql`,
      external: false,
    },

    main: {
      dimensions: [CUBE.id, CUBE.name],
      measures: [CUBE.count],
      timeDimension: CUBE.createdAt,
      granularity: `day`,
      useOriginalSqlPreAggregations: true,
    },
  },

  ...,
})

Result

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