This section explains how to get started using DirectQuery to connect to an external Database. For an overview of DirectQuery, please see the DirectQuery Overview section in the User Guide.

Overall Sequence

To connect to a remote Database using DirectQuery you need to complete these steps:

Configure your remote Database in Atoti FRTB’s expected Database format
Set the Atoti FRTB configuration properties
Deploy Atoti FRTB in Horizontal Distribution

Database Schema

Your remote Database must be configured in the same format as Atoti FRTB’s in-memory datastores. This means the same Tables and Field Types will need to be replicated. You can do this by defining the Tables as outlined in the Database documentation or by exposing Views on your Database in the same expected Database format.

All of the Database Tables must be present either as actual Tables or Views on your connected Database.

Required DirectQuery Properties

The following properties must be configured to get started with DirectQuery.

Maven Profile

The frtb-directquery module is not included in the classpath by default. You will need to enable the direct-query Maven Profile to add the frtb-directquery module into the classpath. This will need to be completed when building the JAR file.

application.yaml

The application.yaml file contains specific properties for enabling and connecting to a remote Database.

Property	Default Value	Description
`starter.deployment.type`	`in-memory`	`in-memory` / `direct-query` / `query-node` selects the source of data
`directquery.database.type`	N/A	Defines the Database to connect to

For connecting to a specific Database, see our Supported Databases page.

Supported directquery.database.type

“clickhouse” for a ClickHouse database.
“databricks” for a Databricks database.
“mssql” for a Microsoft SQL database.
“snowflake” for a Snowflake database.

Dates to Include Configuration

The directquery.dates-filter.dates-to-include property is used to specify the dates to include in the DirectQuery data node. This property is required when running DirectQuery.

Deployment Options

We provide two main options to run with DirectQuery:

Option	How to run Atoti FRTB
Operate with some data loaded in-memory and the rest available through DirectQuery	In Horizontal Distribution with in-memory Database
Run purely on DirectQuery remote data	In a Single JVM on DirectQuery only

Limitations

Given that the data nodes are distributed by AsOfDate, no two data nodes can contain the same Partition - meaning that one AsOfDate cannot be present in another node. So to prevent any issues the directQueryDatesToIncludeFilter bean is used to set which dates to include in the DirectQuery data node.

This property must be provided with the same values to both data nodes.

Horizontal Distribution

In Horizontal Distribution you have access to the in-memory tools such as What-If, data updates and Sign-Off for the data loaded in-memory as well as access to a large number of historical dates. When running in Horizontal Distribution you need to run three Nodes:

Query node
In-memory data node
DirectQuery data node

JVM With Query Node

This JVM will consist of the StandardisedApproachCube query node and the CombinedCube query node.

The query node cannot have cubes that are not available in the data nodes.

Start the JVM with the following Spring and Atoti Server properties, either in a property file or as command-line arguments:

Property	Value	Description
`starter.deployment.type`	`query-node`	Activates only the query cubes.
`starter.deployment.transport`	`netty`	Turns on Netty messaging to communicate with nodes in other JVMs.
`dlc.enabled`	`false`	The DLC must be disabled for query nodes as there is no Datastore.
`activeviam.distribution.gossip.router.enable`	`true`	Enables the distribution gossip to allow the Nodes to communicate with each other.
`activeviam.distribution.gossip.router.port`	`16484`	The port on which the gossip host will be started.
`ima.drc.disable`	`true`	IMADRCCube cube must be disabled as it is not currently supported on DirectQuery. As an alternative to `ima.drc.disable`, you can use `ima.disable` to disable all IMA cubes.
`ima.plat-backtesting.disable`	`true`	PLCube must be disabled as it is not currently supported on DirectQuery.
`ima.stress-calibration.disable`	`true`	StressCalibrationCube must be disabled as it is not currently supported on DirectQuery.

JVM With In-Memory Data Node

This JVM will contain the in-memory data node. Start the JVM with the following Spring and Atoti Server properties, either in a property file or as command-line arguments:

Property	Value	Description
`starter.deployment.type`	`direct-query`	In memory cubes.
`starter.deployment.transport`	`netty`	Turns on Netty messaging to communicate with nodes in other JVMs.
`dlc.enabled`	`true`	We need the DLC to populate some of our in-memory datastores.
`ima.drc.disable`	`true`	IMADRCCube cube must be disabled as it is not currently supported on DirectQuery.
`ima.plat-backtesting.disable`	`true`	PLCube must be disabled as it is not currently supported on DirectQuery.
`ima.stress-calibration.disable`	`true`	StressCalibrationCube must be disabled as it is not currently supported on DirectQuery.
`content-service.db.url`	`jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1`	The ContentServer will be managed by the query node, so we specify to use an in-memory ContentServer for this data node.
`content-service.db.hibernate.hbm2ddl.auto`	`create`	Since we are using an in-memory ContentServer, we will allow Atoti Server to automatically generate the correct ContentServer.
`server.port`	`8082`	The exact value does not matter. The port must simply be unique from the query node and other data nodes.
`directquery.database.type`	`snowflake` or `clickhouse` etc	The type of Database to connect to.
`directquery.database.database_type.XXX`	Various Database-specific properties	Any other Database-specific properties required.
`directquery.dates-filter.dates-to-exclude`	Dates loaded in-memory	The AsOfDates that were included in the in-memory data node. When using distribution, two nodes of the same cube cannot contain the same date.

Single JVM

can be run purely on DirectQuery data in a single JVM. By running in a single JVM, the application will only contain DirectQuery data. The configuration properties are as follows:

Property	Value	Description
`starter.deployment.type`	`direct-query`	DirectQuery cubes.
`starter.deployment.transport`	`local`	Local messages to feed the combined cube.
`ima.drc.disable`	`true`	IMADRCCube cube must be disabled as it is not currently supported on DirectQuery.
`ima.plat-backtesting.disable`	`true`	PLCube must be disabled as it is not currently supported on DirectQuery.
`ima.stress-calibration.disable`	`true`	StressCalibrationCube must be disabled as it is not currently supported on DirectQuery.
`directquery.database.type`	`database_type`	The type of Database to connect to.
`directquery.database.database_type.XXX`	Various Database-specific properties	Any other Database-specific properties required.
`directquery.dates-filter`	Filter the dates in the cube	Can limit the application to a subset of dates. If left blank, all dates will be include in the application.
`directquery.dates-filter.dates-to-exclude`	Any optional dates to exclude	The provided dates will be excluded from the cube.
`directquery.dates-filter.dates-to-include`	Limits dates to include	Only the provided dates will be included in the cube.

Single JVM Limitations

Running with a single JVM means running purely on DirectQuery. This will come at the compromise of query performance of Trade-level queries, while also not having access to tools available exclusively in-memory, such as Atoti What-If and Atoti Sign-Off.

Running both an in-memory data node and DirectQuery data node under a single JVM is not currently supported.

​Overall Sequence

​Database Schema

​Required DirectQuery Properties

​Maven Profile

​application.yaml

​Supported directquery.database.type

​Dates to Include Configuration

​Deployment Options

Limitations

​Horizontal Distribution

​JVM With Query Node

​JVM With In-Memory Data Node

​Single JVM

​Single JVM Limitations

Overall Sequence

Database Schema

Required DirectQuery Properties

Maven Profile

application.yaml

Supported directquery.database.type

Dates to Include Configuration

Deployment Options

Horizontal Distribution

JVM With Query Node

JVM With In-Memory Data Node

Single JVM

Single JVM Limitations