Highlights

info

Holistics Aggregate Awareness includes industry-standard features, but we go further, making it easier and more effective for analysts to use.

Reusable Configurations When Defining Multiple Pre-aggregates

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, 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.

PreAggregate aggr_movie_ratings_daily {
  dimension timestamp {
    for: ref('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: ref('ratings', 'timestamp')
      time_granularity: 'month'
    }
  })
  aggr_movie_ratings_weekly: aggr_movie_ratings_daily.extend({
    dimension timestamp {
      for: ref('ratings', 'timestamp')
      time_granularity: 'week'
    }
  })
  aggr_movie_ratings_daily: aggr_movie_ratings_daily
}

Check out the full example here.

Join Awareness (aka. Smart Join Substitution)

In many other BI platforms, Aggregate Awareness is limited to working with dimensions within the pre-aggregated tables only.

Let’s say we have these two tables: ratings and movies. If we want to "Count of Ratings by Movie Title (or Movie Genre)", Holistics can answer a wider range of questions using just a single pre-aggregate with movie_id

PreAggregate aggr_movie_ratings_count {
  dimension movie_id {
    for: ref('movies', 'id')
  }
  measure count_rating { ... }
  persistence: { ... }
}

Holistics Aggregate Awareness is aware of the relationships in your dataset and, hence, can perform smart join substitution

“Count of ratings by Movie Title”:

Querying “Count of ratings by Movie Genre” also utilizes the same pre-aggregate:

More examples and explanations can be found here.

Metric Awareness

Rather than being limited to basic aggregations, Holistics is also aware of 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:

Built-in Persistence (aka Table Materialization)

In some BI platforms, analysts have to manually persist their pre-aggregated tables, often relying on external tools like dbt to write the data back to the database. With Holistics, we've simplified this process by offering built-in persistence directly within the platform.

Persistence can be triggered in a few different ways:

• Manual Trigger: Analysts can manually persist tables by clicking a button in the UI, making it easy to refresh data on demand.
• Scheduled Refresh: Use Holistics' built-in scheduling system to automate persistence, ensuring your tables are always up-to-date without any manual intervention.
• API Trigger: For more advanced workflows, integrate persistence with your existing orchestration tools via our upcoming API trigger feature.

In terms of persistence modes, Holistics supports both full refresh and incremental refresh (upsert), giving analysts the flexibility to optimize depending on the nature of the table’s data.

Additionally, the platform automatically cleans up stale tables, so you don’t have to worry about managing outdated data.

With Holistics, analysts have the option to full-cycle manage the pre-aggregates within the tool itself, rather than relying on external data transformation tools.