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.

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

Documentation Index

​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

​When are clustering fields used