About Hinted Aggregate Tables

Hinted aggregates are a type of user-defined aggregate table; you determine whether to allow the AtScale engine to create such aggregate tables on query datasets. You also control the definition of a hinted aggregate with the SQL statement that defines the corresponding query data set. The join keys, measures, and degenerate dimensions that the SQL statement references will be included in the hinted aggregate table for the query dataset. If you change the references in that SQL statement, the definition of the hinted aggregate changes and is put into effect when the instance of the aggregate table is rebuilt.

To allow the AtScale engine to create hinted aggregate tables for datasets in a model, you need to edit the model's underlying SML to include the allow_aggregates and create_hinted_aggregate properties. For more information, see the AtScale SML Object Documentation on GitHub. The aggregates will be created once the catalog has been deployed.

When the AtScale engine creates a hinted aggregate table on a query dataset, some queries that are run against the query dataset can run more efficiently against the hinted aggregate table instead. This can save some queries from requiring a fully materialized query dataset.

This feature is not a form of a materialized view that you may be familiar with from a traditional RDBMS; AtScale will not write the query dataset results to a table. Instead, AtScale writes an aggregate table using the join keys, degenerate dimensions, and measures derived from the query dataset. The resulting aggregate table can be much smaller than the results of the query dataset.

For example, take the following query dataset: :

SELECT Prod_SKU, Color, Sum(Sales) FROM Sales GROUP BY 1, 2

In this example, where the model contains a color dimension and Sales measure, enabling the hinted aggregate feature will create an aggregate consisting of Color and Sum(Sales), which contain significantly fewer rows than the source query dataset results contain.

Hinted aggregate tables can be built incrementally. As when you allow incremental aggregate tables to be built for fact datasets, you specify an incremental indicator and a grace period. For more information, see About Incremental Rebuilds.

As you can for other types of aggregate table, you can monitor the use of hinted aggregates, the success or failure of builds of hinted aggregates, and other information about them from the Aggregates page.

note

Unhandled NULL values in key columns will result in incomplete aggregate tables and unexpected query results. See the Custom Empty Member feature for more details.

Partitioned Hinted Aggregates

AtScale applies partitioning when the hinted aggregate includes key columns that match keys on the model's partitioning keys list. The hinted aggregate will have partitioning applied as per the Model level settings. This behavior can be disabled by setting the configuration option aggregates.create.partition.hintedAggregate.enabled to false. The default value for this setting is true.

For more information, see Partitioned System-Defined Aggregate Tables.