DirectQuery answers cube queries by generating SQL and running it against an external database.
Long-running queries can incur significant costs and, if the database is shared between users, can lead to resource contention.
To prevent this, you can set timeouts on queries. The application admin configures these limits; an individual query may request a shorter timeout, but never one that exceeds the configured limit.
There are two distinct configuration objects:
XXXClientSettings (e.g. DatabricksClientSettings) configures how Atoti, acting as a client, interacts with the external database. The limits there apply in a general context (regular query, discovery query, time-travel discovery).XXXDatabaseSettings (e.g. DatabricksDatabaseSettings) configures how the Atoti cube (via its database object) interacts with the external database. Cube related limits are defined there, such as the feeding query timeout.
Summary
| Setting | Configured on | Default | Applies to |
|---|
externalDatabaseQueryTimeout | XXXClientSettings | 5 minutes | Default for all queries |
feedingQueryTimeout | XXXDatabaseSettings | 1 hour | Feeding queries (initial load and refresh) |
What is externalDatabaseQueryTimeout?
externalDatabaseQueryTimeout caps the duration of non-feeding queries.
A cube (MDX) query propagates its own timeout down to the database queries it triggers. The effective timeout for a database query is the minimum of the cube query’s own timeout and externalDatabaseQueryTimeout.For example, if an MDX query is allowed 1 hour but externalDatabaseQueryTimeout is left at its 5-minute default, the generated database query still times out after 5 minutes. If your interactive queries legitimately need longer, raise both this limit and the MDX query timeout.
Raising externalDatabaseQueryTimeout allows queries to run longer on the external database. This can increase cost. The default of 5 minutes is conservative to protect against runaway queries.
BigQuery
ClickHouse
Databricks
Generic JDBC
MS SQL
Redshift
Snowflake
Synapse
final BigqueryClientSettings clientSettings =
BigqueryClientSettings.builder()
.bigQuery(bigquery)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
final ClickhouseClientSettings clientSettings =
ClickhouseClientSettings.builder()
.propertiesV2(properties)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
final DatabricksClientSettings clientSettings =
DatabricksClientSettings.builder()
.connectionProperties(properties)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
final GenericJdbcClientSettings clientSettings =
GenericJdbcClientSettings.builder()
.properties(properties)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
final MsSqlClientSettings clientSettings =
MsSqlClientSettings.builder()
.dataSource(dataSource)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
final RedshiftClientSettings clientSettings =
RedshiftClientSettings.builder()
.properties(properties)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
final SnowflakeClientSettings clientSettings =
SnowflakeClientSettings.builder()
.properties(properties)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
final SynapseClientSettings clientSettings =
SynapseClientSettings.builder()
.dataSource(dataSource)
.externalDatabaseQueryTimeout(Duration.ofMinutes(10))
.build();
What is feedingQueryTimeout?
feedingQueryTimeout applies to feeding queries: the queries run during the initial load to feed the aggregate providers and hierarchies, and during refresh operations.
Raising feedingQueryTimeout allows feeding queries to run longer on the external database. This can increase cost, especially on large datasets.
BigQuery
ClickHouse
Databricks
Generic JDBC
MS SQL
Redshift
Snowflake
Synapse
final BigqueryDatabaseSettings databaseSettings =
BigqueryDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final ClickhouseDatabaseSettings databaseSettings =
ClickhouseDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final DatabricksDatabaseSettings databaseSettings =
DatabricksDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final GenericJdbcDatabaseSettings databaseSettings =
GenericJdbcDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final MsSqlDatabaseSettings databaseSettings =
MsSqlDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final RedshiftDatabaseSettings databaseSettings =
RedshiftDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final SnowflakeDatabaseSettings databaseSettings =
SnowflakeDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final SynapseDatabaseSettings databaseSettings =
SynapseDatabaseSettings.builder().feedingQueryTimeout(Duration.ofHours(2)).build();
final Application app =
Application.builder(connector)
.schema(schema)
.managerDescription(managerDescription)
.databaseSettings(databaseSettings)
.build();
How to set limits on direct database queries
If you issue queries directly against the database through the database query API rather than through the cube, you can set a timeout and a result limit per individual query. See the database queries page, under “Add a timeout or a result limit”.
Time-travel discovery timeout
For databases that support time travel, DirectQuery also runs discovery queries to detect the latest modification timestamp on tables. You can set a specific timeout for these queries. See the Versioning page, under “Time travel discoveries”. 
