# Build multiple pre-aggregates using AML Extend

> How to use AML Extend to conveniently build multiple PreAggregates with different time granularities

## Introduction

When setting up [Aggregate Awareness](/docs/aggregate-awareness/quick-start), it is a common need to create different PreAggregates for different _time granularities_ so that you can configure more _efficient_ persistence pipelines. For example:
* A PreAggregate with time granularity `month` only needs to be persisted once a month.
* While PreAggregate with time granularity `week` needs to be persisted once a week.

To conveniently generate multiple PreAggregates for different time granularities, you can leverage AML Reusability!  

<figure class='video-container'>
  <iframe
      src="https://www.youtube.com/embed/iyMLCsY2D-c?si=WfI_fQMlYhK1w_qt"
      frameborder="0"
      allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture; fullscreen;"
      allowfullscreen
  >
  </iframe>
</figure>

## Without reusability

Here's how you define it without reusability.

```aml title="movie_rating_analysis.dataset.aml"
Dataset movie_rating_analysis {
  ...

  pre_aggregates: {
    aggr_movie_ratings_monthly: PreAggregate {
      dimension timestamp {
        for: r(public_ratings.timestamp)
        time_granularity: 'month'
      }
      measure highest_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'max'
      }
      measure lowest_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'min'
      }
      measure sum_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'sum'
      }
      
      persistence: IncrementalPersistence {
        schema: 'persisted'
        incremental_column: 'timestamp'
      }
    },

    aggr_movie_ratings_weekly: PreAggregate {
      dimension timestamp {
        for: r(public_ratings.timestamp)
        time_granularity: 'week'
      }
      measure highest_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'max'
      }
      measure lowest_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'min'
      }
      measure sum_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'sum'
      }
      
      persistence: IncrementalPersistence {
        schema: 'persisted'
        incremental_column: 'timestamp'
      }
    },
    
    aggr_movie_ratings_daily: PreAggregate {
      dimension timestamp {
        for: r(public_ratings.timestamp)
        time_granularity: 'day'
      }
      measure highest_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'max'
      }
      measure lowest_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'min'
      }
      measure sum_rating {
        for: r(public_ratings.rating)
        aggregation_type: 'sum'
      }
      
      persistence: IncrementalPersistence {
        schema: 'persisted'
        incremental_column: 'timestamp'
      }
    }
  }

}
```

As shown in this example, we have to repeat many things: `persistence`, `highest_rating`, `lowest_rating`, `sum_rating`.  

* If we want to add more measures in the future, we have to add 3 times.
* If we want to create more pre-aggregates in, for example, `year`, we again have to repeat almost everything.

## Refactoring using AML Extend

Now let's refactor them for better reusability using [AML Extend](/reference/aml/extend).

We can update the above codes using 2 steps:

**Step 1**: Pick one PreAggregate (e.g. `aggr_movie_ratings_daily`) and turn it into a variable.

:::info Notes
* To declare a variable, you need to do it _outside_ of your `Dataset` declaration.
* You can also declare this variable in a separate file!
:::

```aml title="movie_rating_analysis.dataset.aml"
PreAggregate aggr_movie_ratings_daily {
  dimension timestamp {
    for: r(public_ratings.timestamp)
    time_granularity: 'day'
  }
  measure highest_rating {
    for: r(public_ratings.rating)
    aggregation_type: 'max'
  }
  measure lowest_rating {
    for: r(public_ratings.rating)
    aggregation_type: 'min'
  }
  measure sum_rating {
    for: r(public_ratings.rating)
    aggregation_type: 'sum'
  }
  
  persistence: IncrementalPersistence {
    schema: 'persisted'
    incremental_column: 'timestamp'
  }
}

Dataset movie_rating_analysis {
  ...
}
```

**Step 2**: Create other PreAggregates by _extending_ the variable we just created.

```aml title="movie_rating_analysis.dataset.aml"
Dataset movie_rating_analysis {
  ...

  pre_aggregates: {
    aggr_movie_ratings_monthly: aggr_movie_ratings_daily.extend({
      dimension timestamp {
        for: r(public_ratings.timestamp)
        time_granularity: 'month'
      }
    }),
    aggr_movie_ratings_weekly: aggr_movie_ratings_daily.extend({
      dimension timestamp {
        for: r(public_ratings.timestamp)
        time_granularity: 'week'
      }
    }),
    aggr_movie_ratings_daily: aggr_movie_ratings_daily
  }

}
```

Just like that, we reduced 66 lines of code into 35 lines of code, making it **more maintainable** and **more readable** at the same time.

[AML Extend](/reference/aml/extend) has made this so convenient!

<!-- 

## Reusable Configurations When Defining Multiple Pre-aggregates {#reusability}

Setting up Aggregate Awareness in BI tools often involves building multiple pre-aggregated tables with similar configurations. This can be a maintenance hassle. With Holistics' [AML language](/as-code/amql), pre-aggregated tables can be built and extended, simplifying the process.

In the example below, monthly and weekly aggregates are built by extending the existing daily aggregate and modifying only the time granularity.

```aml
PreAggregate aggr_movie_ratings_daily {
  dimension timestamp {
    for: r(ratings.timestamp)
    time_granularity: 'day'
  }
  dimension timestamp { ... }
  measure highest_rating { ... }
  measure lowest_rating { ... }
  measure count_rating { ... }
  persistence: { ... }
}

pre_aggregates: {
  aggr_movie_ratings_monthly: aggr_movie_ratings_daily.extend({
    dimension timestamp {
      for: r(ratings.timestamp)
      time_granularity: 'month'
    }
  })
  aggr_movie_ratings_weekly: aggr_movie_ratings_daily.extend({
    dimension timestamp {
      for: r(ratings.timestamp)
      time_granularity: 'week'
    }
  })
  aggr_movie_ratings_daily: aggr_movie_ratings_daily
}
```

Check out the [full example here](/as-code/aml/use-cases/build-multiple-pre-aggregates).

-->