> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Using `original_sql` and `rollup` pre-aggregations effectively

> 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" theme={"dark"}
  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" theme={"dark"}
  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