Aggregate Tables - Atoti Hub

DirectQuery is using the external database to feed some aggregate providers and answer some queries. This can result in some large queries with heavy load on the database, and if the same cube is started multiple times, the same query can be repeated. Aggregate tables are a way to cache some aggregated data in the external database to make some queries faster and cheaper.

How does it work

Aggregate Tables are tables added in the external database as a table fed from aggregated data. These tables typically denormalize fields from joined tables into group-by columns, but can also preserve joins to keep the aggregate table smaller while still accessing fields from other tables at query time. Whenever possible, DirectQuery will query the Aggregate Table instead of the base Schema for a more optimized query: For instance, consider a Sales table containing sales such as this one:

Id	Date	Product	Buyer	Quantity	Price
1	2023-01-01	P1	Buyer_A	100.0	15.5
2	2023-01-01	P1	Buyer_B	100.0	16.5
3	2023-01-01	P2	Buyer_A	100.0	30
4	2023-01-01	P2	Buyer_B	100.0	31
5	2023-01-02	P1	Buyer_A	100.0	15.5
6	2023-01-02	P1	Buyer_B	100.0	15.5
7	2023-01-02	P1	Buyer_A	100.0	18
8	2023-01-03	P1	Buyer_B	100.0	18
9	2023-01-03	P2	Buyer_A	100.0	35
10	2023-01-03	P2	Buyer_B	100.0	40

It is possible to aggregate the sum of the Quantity per Date and Product in the external database into an other table agg_sales:

date	product	sum_of_quantity	count_of_rows
2023-01-01	P1	200.0	2
2023-01-01	P2	200.0	2
2023-01-02	P1	300.0	3
2023-01-03	P1	100.0	1
2023-01-03	P2	200.0	2

If this table is added as an Aggregate Table in DirectQuery, it will be used whenever possible instead of the original Sales table. For instance when asking for the SUM(Quantity) per Product in the Sales table, DirectQuery will retrieve the SUM(sum_of_quantity) per product in agg_sales. As the agg_sales is smaller and already aggregated the result will be faster to compute and will potentially avoid a huge scan of the database, saving both time and compute costs.

Use cases

There are a few classic use cases for using an Aggregate Table:

Feeding an aggregate provider faster by pre-aggregating over the provider fields
Having an “external database aggregate provider”, i.e. being able to answer some queries from the pre-aggregated data in the external database but without having to store additional data in-memory.
Feeding some hierarchies faster

Supported aggregations

The following aggregation function can be used in an Aggregate Table:

Sum
Min
Max
Average
Sum product
Count
Long
Short
Gross
Square sum

Custom Aggregations

To use a custom aggregation in an aggregate table, you need to define the corresponding user defined aggregation.
You can also register its corresponding re-aggregation function.
This re-aggregation function will be used to compute the aggregates at a coarser granularity than the one stored in the aggregate table.

Examples

Create a simple Aggregate Table to feed an aggregate provider

Let’s create a new table that will pre-aggregate the quantity (in the tutorial sales table) per Date and Product:

CREATE OR REPLACE TABLE `tutorial`.SALES_BY_PRODUCT (
  PRODUCT STRING NOT NULL,
  QUANTITY_SUM FLOAT64 NOT NULL,
  CONTRIBUTORS_COUNT INT64 NOT NULL
);