Time-granularity Awareness

Holistics can identify the pre-aggregated table at the right time granularity level based on the option that you configure in the pre-aggregate (minute, hour, day, week, month, quarter, year)

For example, in the quick example above, you can make use of the agg_transactions at the day level to get the total transactions count at the week or month level, etc.

These queries would use pre-aggregated table agg_transactions at day level and take additional time to aggregate from day to week or month (although it is still faster than using the raw table).

However, if you use data at the week level frequently and want to optimize the query time faster, try to create another pre-aggregated table at coarser-grained granularity (e.g., agg_transactions_week)

Map it with the transactions model so that the engine will be aware of it when possible

Dataset ecommerce {
  //... other settings
  models: [
    transactions
  ]

  pre_aggregates: {
    // BEGIN NEW CODE
    agg_transactions_week: PreAggregate {
      dimension created_at_week {
        for: r(transactions.created_at),
        time_granularity: "week"
      }
      //... other pre-aggregate settings
    },
    // END NEW CODE

    agg_transactions: PreAggregate {
      //... pre-aggregate settings
    },
  }
}

For now, when you query the total weekly transactions count, it will use the pre-aggregated table agg_transactions_week leading to faster results than just using agg_transactions table.

NOTICE

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”)