Metric Awareness

Introduction

Rather than being limited to basic aggregations, Holistics is also aware of and leverages pre-aggregates to calculate more complex metrics.

Let’s say we have this report of Count of Ratings by Year:

Now, what if we want the Running Count (instead of just Count) of Ratings?

• In many BI platforms, it requires you to write a complicated SQL and set up a new pre-aggregate for this Running Count metric.

• In Holistics, it is as simple as adding a single AQL formula:

  public_ratings.count_all_ratings | running_total(public_ratings.timestamp)

Aggregate Awareness will automatically use the count pre-aggregate that we already have, effectively lifting out the heaviest computation (aggregation) in our report query.

Measures Types in Pre-aggregates

KNOWLEDGE CHECKPOINT

Learn more about Additive and Non-additive Measures

Depending on the measure type being used in pre-aggregate and whether or not you re-aggregate the measure in the current query, the pre-aggregated tables will be hit or missed.

Additive measures

• Aggregate awareness can be used to re-aggregate with these types of additive measures

  • sum
  • count
  • min
  • max

• For example, a pre-aggregate of "Sum Revenue by Month" can also be used for the query "Sum Revenue by Year".

Non-additive measures

• Pre-aggregated non-additive measures can only match the queries that use the exact same dimensions/granulariy. Here are some non-additive measures:
  • count distinct
  • median
  • stddev
  • variance
  • avg
• For example, a pre-aggregate of "Count Distinct User by Month" cannot be used for the query "Count Distinct User by Year".
• In the case of avg, you can actually split (decompose) it into two additive measures: sum and count. If your PreAggregate has sum and count measures, Aggregate Awareness can automatically pick up those measures to calculate the avg.

// For `avg`

// ❗ Instead of pre-aggregating `avg` directly
measure avg_a {
  for: r(model.a)
  aggregation_type: 'avg'
}

// ✅ Pre-aggregate on `sum` and `count
measure sum_a {
  for: r(model.a)
  aggregation_type: 'sum'
}
measure count_a {
  for: r(model.a)
  aggregation_type: 'count'
}