FAQs

Why is pre-aggregate defined inside a dataset? Can I just define it in the model?

PreAggregate is defined in the dataset because it can reference fields from multiple different models.
Thus, it needs to be defined in a dataset where it can use the dataset's relationships to join those models.

I expected the query to use the pre-aggregated table, but it ended up using the original one.

There are many reasons for this issue. Here are the most common reasons:

• The tables in the query are finer-grained in granularity than the pre-aggregated tables that you defined in the dataset.
  • For example, you query Count total transactions by hour, but you just have pre-aggregated tables at day and week levels, which have higher granularity than hour. So the raw table will be used instead of the pre-aggregated table.
• You defined the pre-aggregated tables at the right level of granularity already, but you did not trigger the persistence. That is why your pre-aggregated tables are not there when you need them.

In any case, please check out the Executed Query which includes the exact reason and other debugging details.

I expected the query to use pre-aggregate at this level of granularity because it is more optimal, but it turned out to use another one.

In the current version, if there are multiple pre-aggregates eligible for a query, the (eligible) pre-aggregate that is defined first in the dataset will be chosen to substitute into the query.

We recommend that you put the coarser-grained pre-aggregates before the finer-grained pre-aggregates in the dataset configuration. (e.g. “month” first, then “week”, then “day”)

How can I use my existing AQL measure in PreAggregate measure?

Currently, Holistics does not allow putting an AQL measure directly inside a PreAggregate.measure definition. But instead, you can pre-aggregate by "decomposing" that AQL measure.

For example, the transactions model has an AQL measure named count_transactions:

Model transactions {
  measure count_transactions {
    type: 'number'
    definition: @aql count(transactions.id);;
  }
}

If we try to use transactions.count_transactions in a PreAggregate, we will see an error:

Dataset ecommerce {
  //... other settings
  models: [transactions]
  pre_aggregates: {
    agg_transactions: PreAggregate {
      // ...other settings
      measure count_transactions {
        for: ref('transactions', 'count_transactions') // ❌ DO NOT use this
        aggregation_type: 'count'
      }
      persistence: FullPersistence {
        schema: 'persisted'
      }
    },
  }
}

Invalid PreAggregate: `count_transactions` uses "count" aggregation
but `transactions.count_transactions` is not a dimension.

Instead, we can decompose the measure into count + ref('transactions', 'id'):

Dataset ecommerce {
  //... other settings
  models: [transactions]
  pre_aggregates: {
    agg_transactions: PreAggregate {
      // ...other settings
      measure count_transactions {
        for: ref('transactions', 'id') // ✅ DO use this
        aggregation_type: 'count'
      }
      persistence: FullPersistence {
        schema: 'persisted'
      }
    },
  }
}

Decomposing Measures and putting the base measures inside PreAggregate definitions will make your PreAggregate more robust.
For example, a sum PreAggregate can be used to answer queries for:

• The Sum itself
• Running Sum
• Sum with Period Comparison
• etc.

What if my AQL measure composes of multiple AQL aggregations?

Answer: As long as you can decompose your AQL measure into the supported aggregation types, you can pre-aggregate it.

For example, if you have an AQL measure that has the formular @aql sum(model.a) * sum(model.b), you can decompose it and make 2 measures in your Pre-Aggregate:

measure sum_a {
  for: ref('model', 'a')
  aggregation_type: 'sum'
}
measure sum_b {
  for: ref('model', 'b')
  aggregation_type: 'sum'
}

What if my measure is a SQL measure?

We would strongly recommend you to convert your SQL measure into an AQL measure (i.e. using @aql syntax instead of @sql syntax):

// in Model definition
measure sum_x {
  label: 'Sum of X'
  type: 'number'
  definition: @aql sum(model.x);;
}

// in PreAggregate definition
measure sum_x {
  for: ref('model', 'x')
  aggregation_type: 'sum'
}

(This is the recommended setup that we described earlier.)

Otherwise, you can still use aggregation_type: 'custom', but Metric awareness features won't apply and Holistics will always treat your measure as a non-additive measure.

// in Model definition
measure sum_x {
  label: 'Sum of X'
  type: 'number'
  definition: @sql SUM({{ x }});;
}

// in PreAggregate definition
measure sum_x {
  for: ref('model', 'sum_x')
  aggregation_type: 'custom'
}

How could I handle timezone when querying with pre-aggregated tables?

Currently, PreAggregates always use the organization's timezone.

Therefore, Aggregate Awareness only works with queries/explorations/dashboards that are also using the organization's timezone.
If the query/dashboard timezone is different from the organization's timezone, the engine won't be able to apply any PreAggregate.

In later releases of Aggregate Awareness, we will allow configuring the timezone of PreAggregates.

Other references

• Tutorial with more examples Using Aggregate Awareness.
• Discuss more on Community Post.

Closed Beta

Aggregate Awareness is now in closed beta. Request beta access and try it out.