Getting Started with DirectQuery

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:

1. Configure your remote Database in Atoti FRTB's expected Database format
2. Set the Atoti FRTB configuration properties
3. 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.

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.
• “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.

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.

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.
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.
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	in-memory	In memory cubes.
starter.deployment.transport	netty	Turns on Netty messaging to communicate with nodes in other JVMs.
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	8081	The exact value does not matter. The port must simply be unique from the query node and other data nodes
initial-load.business-dates	Dates to load in-memory	The AsOfDates to include in the in-memory data node

JVM With DirectQuery Data Node

This JVM will contain the DirectQuery data node.

When starting the DirectQuery data node, you must also ensure that the Dates to Include configuration is defined and does not include any dates that exist in the in-memory data node.

Start the DirectQuery data node with the following Spring and Atoti Server properties, either in a property file or as command-line arguments in addition to the required DirectQuery properties.

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.
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

FRTB 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 What-If and Sign-Off.