---
title: "Using `original_sql` and `rollup` pre-aggregations effectively"
description: 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][ref-cube-sql] to define [cubes][ref-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][ref-preaggs] 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][ref-schema-ref-preaggs-type-origsql]
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`][ref-schema-ref-preaggs-type-origsql] 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][ref-schema-ref-preaggs-use-origsql].

<Warning>

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

</Warning>

<CodeGroup>

```yaml title="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
```

```javascript title="JavaScript"
cube("orders", {
  sql: `<YOUR_EXPENSIVE_SQL_QUERY HERE>`,

  pre_aggregations: {
    base: {
      type: `original_sql`,
      external: false
    },

    main: {
      dimensions: [id, name],
      measures: [count],
      time_dimension: created_at,
      granularity: `day`,
      use_original_sql_pre_aggregations: true
    }
  }
})
```

</CodeGroup>

## Result

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

[ref-schema-ref-preaggs-type-origsql]: /reference/data-modeling/pre-aggregations#original_sql
[ref-schema-ref-preaggs-use-origsql]: /reference/data-modeling/pre-aggregations#use_original_sql_pre_aggregations
[ref-cubes]: /reference/data-modeling/cube
[ref-cube-sql]: /reference/data-modeling/cube#sql
[ref-preaggs]: /docs/pre-aggregations/using-pre-aggregations