Clustering fields in DirectQuery

Feeding aggregate providers from an external database can result in very large queries to be run on this database. Some databases can have trouble processing such large queries. Clustering fields are a way to break down those queries in multiple parts when feeding Atoti Aggregate providers. You can extends the usage of the clustering fields beyond the feeding with the UseClusteringFields.ALWAYS database settings. Clustering fields are set on a table on DirectQuery side. When feeding an Aggregate Provider, if the computed database query needs to access this table, the query will be split up according to the table clustering fields. If multiple tables are accessed by the query, the clustering fields from all tables are taken in account to split the query. A query clustered by too many fields or by fields with a high cardinality can be divided in lots of sub queries. To avoid creating too many queries on the external database, DirectQuery limits the number of sub queries to 500.
This limit can be changed thanks to the parameter IDatabaseSettings#maxSubQueriesAllowedInMultiStepsQuery(), which can be set for your specific database in XXXDatabaseSettings, e.g.:

BigqueryDatabaseSettings
ClickhouseDatabaseSettings
DatabricksDatabaseSettings
SnowflakeDatabaseSettings
…

final BigqueryDatabaseSettings databaseSettings =
    BigqueryDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final ClickhouseDatabaseSettings databaseSettings =
    ClickhouseDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final DatabricksDatabaseSettings databaseSettings =
    DatabricksDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final GenericJdbcDatabaseSettings databaseSettings =
    GenericJdbcDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final MsSqlDatabaseSettings databaseSettings =
    MsSqlDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final RedshiftDatabaseSettings databaseSettings =
    RedshiftDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final SnowflakeDatabaseSettings databaseSettings =
    SnowflakeDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final SynapseDatabaseSettings databaseSettings =
    SynapseDatabaseSettings.builder().maxSubQueriesAllowedInMultiStepQuery(10).build();

final Application app =
    Application.builder(connector)
        .schema(schema)
        .managerDescription(managerDescription)
        .databaseSettings(databaseSettings)
        .build();

It is recommended to not increase it too much, as it can lead to contention on the external database. If the cardinality of the clustering field (or the set of clustering fields) is lower than the limit, the query will be split up in as many queries as the cardinality of the clustering fields. If the cardinality of the clustering field (or the set of clustering fields) is higher than the limit, the same split will be performed but some queries will be batched together to stay within the limit. By default, the clustering fields are only used for feeding queries as these are the ones using the most resources. This limits the number of queries sent to the external database as “normal” queries are not split up. However it is possible to change this behavior in the database settings:

final BigqueryDatabaseSettings databaseSettings =
    BigqueryDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();

final ClickhouseDatabaseSettings databaseSettings =
    ClickhouseDatabaseSettings.builder()
        .useClusteringFields(UseClusteringFields.ALWAYS)
        .build();

final DatabricksDatabaseSettings databaseSettings =
    DatabricksDatabaseSettings.builder()
        .useClusteringFields(UseClusteringFields.ALWAYS)
        .build();

final GenericJdbcDatabaseSettings databaseSettings =
    GenericJdbcDatabaseSettings.builder()
        .useClusteringFields(UseClusteringFields.ALWAYS)
        .build();

final MsSqlDatabaseSettings databaseSettings =
    MsSqlDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();

final RedshiftDatabaseSettings databaseSettings =
    RedshiftDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();

final SnowflakeDatabaseSettings databaseSettings =
    SnowflakeDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();

final SynapseDatabaseSettings databaseSettings =
    SynapseDatabaseSettings.builder().useClusteringFields(UseClusteringFields.ALWAYS).build();

final Application app =
    Application.builder(connector)
        .schema(schema)
        .managerDescription(managerDescription)
        .databaseSettings(databaseSettings)
        .build();

Examples

Example with one table

Consider the following table SALES.

SALE_ID	DATE	SHOP	PRODUCT	QUANTITY	UNIT_PRICE
S0010	2022-01-31	shop_2	BED_2	3	150
…	…	…	…	…	…

With this table clusterized by dates, the query sum quantities by product is split up in queries of the form sum quantities by product where date equals ....

Example with multiple tables

Now consider this additional table PRODUCTS, storing all the products referenced in the SALES table.

PRODUCT_ID	CATEGORY	SUB_CATEGORY	SIZE	PURCHASE_PRICE	COLOR	BRAND
BED_2	Furniture	Bed	Single	127	red	Mega
…	…	…	…	…	…	…

This table is clusterized by CATEGORY. With a query computing the sum of the margins by product, both the SALES and PRODUCTS table will be accessed, so the two clustering fields DATE and CATEGORY will be used to split the query up.

Example with one table and clustering field cardinality above the limit

Consider the following table SALES.

SALE_ID	DATE	SHOP	PRODUCT	QUANTITY	UNIT_PRICE
S0010	2022-01-31	shop_2	BED_2	3	150
…	…	…	…	…	…

With this table clusterized by dates, the query sum quantities by product is split up in queries of the form sum quantities by product where date equals .... However, as the cardinality of the clustering field DATE is above the limit, each subqueries will be performed with a condition on several dates, in order to stay within the limit of the number of subqueries.

Considerations

Clustering fields and external database clustering

DirectQuery clustering fields can be chosen independently of any database clustering.
Still it can be interesting to match DirectQuery tables clustering fields with the potential clustering already implemented in your external database. This results in better performances for the sub-queries generated.

Clustering fields and Aggregate Provider partitioning

DirectQuery clustering fields can be chosen independently of any configured partitioning for Aggregate Providers. DirectQuery will repartition and reaggregate on the fly if needed.
Still it can be interesting to match DirectQuery tables clustering fields with the partitioning of the existing Aggregate Providers. This coherence results in better performances to avoid the cost of repartitioning.

Clustering fields and aggregate tables

When an aggregate table serves a query, the query is split on the clustering fields of the aggregate table itself, and of the tables it joins, never on those of the granular base table: the query no longer reads the base table, and an aggregate table is often small enough to be read in one query. An aggregate table without clustering fields serves the query in one step, whatever the base table declares. The preliminary distinct query and the sub-queries read the aggregate table directly. An aggregate table is a table of the schema like any other, so its clustering fields can be set with the same customization as for any table, see Set the clustering fields. They can be declared on a column that exists only in the aggregate table. Declare clustering fields on the aggregate table when the sub-queries on that table must be bounded, for instance when a JDBC driver cannot download one large result.

When are clustering fields used

The usage of clustering fields depends on the UseClusteringFields database setting and the type of query. The table below summarizes the different possibilities:

Query type	UseClusteringFields.ALWAYS	UseClusteringFields.FEEDING
discovery	no	no
aggregate provider feeding	yes	yes
Just In Time cube query	yes	no
hierarchy feeding	no	no
ad hoc list/distinct query on database	no	no

Releases and upgrades

Getting started

Concepts

Step 1: Bring your data into Atoti

Step 2: Design your Cube

Step 3: Build an Atoti application

Step 4: Query an Atoti application

Configure Atoti

Secure Atoti

Operate Atoti

Reference

Clustering fields in DirectQuery

Examples

Example with one table

Example with multiple tables

Example with one table and clustering field cardinality above the limit

Considerations

Clustering fields and external database clustering

Clustering fields and Aggregate Provider partitioning

Clustering fields and aggregate tables

When are clustering fields used

​Examples

​Example with one table

​Example with multiple tables

​Example with one table and clustering field cardinality above the limit

​Considerations

​Clustering fields and external database clustering

​Clustering fields and Aggregate Provider partitioning

​Clustering fields and aggregate tables

​When are clustering fields used

Examples

Example with one table

Example with multiple tables

Example with one table and clustering field cardinality above the limit

Considerations

Clustering fields and external database clustering

Clustering fields and Aggregate Provider partitioning

Clustering fields and aggregate tables

When are clustering fields used