Skip to main content

Microsoft Azure Synapse

Time-travel support​

Time-travel is not supported by DirectQuery for Azure Synapse. This comes from the underlying storage backing external tables in Azure Synapse data lake. Plain files do not support snapshots, and so does not support Azure Synapse.
This means that updating that applications based on this Database are subject to data desynchronization in case of changes in the Database.
Read this page to learn more about the visible effects of this desynchronization.

Vector supports​

Only multi-rows vectors and multi-columns vectors are supported by Azure Synapse.

Azure Synapse uses Microsoft Transact-SQL dialect. Though this dialect offers a basic array type, it does not offer powerful aggregation functions to work with them.

Gotchas​

Field nullability​

Azure Synapse, when used with Serverless pools, can only define fields with nullable types.
While this is not a problem in itself, it can conflict with the rule in Atoti cubes that all levels must be based on non-nullable values. This does not create an issue of any sort as the local model is updated behind the scene, assigning a default value based on the type.
It has a side effect on the query performance, as DirectQuery must convert on-the-fly null values to their assigned default values, as well as adding extra conditions during joins to handle null values on both sides.

Connection pool configuration​

Due to some limitations of the Synapse JDBC driver, a connection pool is use on top of the driver to manage the connections to the database. DirectQuery uses the HikariCP connection pool.

It is possible to tune this connection pool by specifying a configuration file and providing its path with the system properties hikaricp.configurationFile (via -Dhikaricp.configurationFile=path/to/hikari.properties).

Not all the parameters can be set that way, as some are set directly by DirectQuery.

The associated file format is: 

maximumPoolSize=31
idleTimeout=15000
minimumIdle=17

Pool size​

maximumPoolSize: if not set, the pool size is configured automatically by the DirectQuery connector, based on the number of CPU cores available.

Connection timeout​

idleTimeout: the maximum time a connection can be idle before being closed. This parameter is not set by DirectQuery and defaults to the Hikari default.

Minimum idle connections​

minimumIdle: the minimum number of idle connections that the pool will maintain. This parameter is not set by DirectQuery and defaults to the Hikari default.