☰
Navigation :

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 FRTB's expected Database format
  2. Set FRTB configuration properties
  3. Deploy FRTB in Horizontal Distribution

    4. Database Schema

    Your remote Database must be configured in the same format as 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.

    note

    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
    directquery.enabled false Enables / Disables the use of DirectQuery
    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

    • “snowflake” for a Snowflake Database. Maps to the property FRTBConditionConstants.TYPE_SNOWFLAKE.

    frtb.properties

    Within the frtb.properties file there is a property to specify which dates to include for the initial load. This will be used to exclude dates from the DirectQuery dataset.

    Property Default Value Description
    initial-load.business-dates N/A Dates to exclude from the DirectQuery data node

    When running with DirectQuery you may not want to load all data from the Database. Currently, you can only filter the queryable data by AsOfDate. This is done by excluding all dates present in the initial-load.business-dates property.

    Since the in-memory and DirectQuery data nodes cannot contain any of the same data (on an AsOfDate basis), you only need to configure this property once. The in-memory node will then load the specified dates while the DirectQuery data node will exclude them. The initial-load.business-dates is only used to exclude dates from the DirectQuery data node when running in Horizontal Distribution and does not apply to a single JVM setup.

    Deployment Options

    We provide two main options to run with DirectQuery:

    Option How to run 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 initial-load.business-dates property is used to set which dates to load into the in-memory data node, while also specifying to the DirectQuery data node which AsOfDates to exclude.

    note

    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 two JVMs:

    • One JVM which consists of a query node and in-memory data node
    • Another JVM which consists of a DirectQuery data node

    note

    When running in horizontal distribution, the StandardisedApproachCube will only contain data from the in-memory data node. To view the DirectQuery data, you will have to use the CombinedCube. This is because the JVM with DirectQuery data will get attached to the query node under the CombinedCube and thus the DirectQuery data is only visible in the CombinedCube.

    JVM With Query Node And In-Memory Data Node

    This JVM will consist of two distributed nodes; a query node and an in-memory data node.

    You must ensure that the initial-load.business-dates property is defined.

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

    Property Value Description
    spring.profiles.active forceNetty Turns on Netty messaging to communicate with nodes in other JVMs.
    ima.disable true IMA must be disabled as it is not supported in the DirectQuery preview.
    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.
    initial-load.business-dates Dates to load in-memory The AsOfDates to include in the in-memory data node

    JVM With DirectQuery Data Node

    When starting the DirectQuery data node, you must also ensure that the initial-load.business-dates property is defined and in line with the values provided to 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
    spring.profiles.active dist-data-node Configures this instance to be a data node.
    server.port 8081 The exact value does not matter. The port must simply be unique from the query node.
    ima.disable true IMA must be disabled as it is not supported in the DirectQuery preview.
    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.
    directquery.enabled true Enables the use of DirectQuery for this instance.
    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.
    initial-load.business-dates Dates loaded in JVM with in-memory data node. The AsOfDates that were loaded into the in-memory data node.

    Single JVM

    FRTB can be run purely on DirectQuery data in a single JVM.

    By running in a single JVM with DirectQuery only, you can now see the DirectQuery data in the StandardisedApproachCube.

    The configuration properties are as follows:

    Property Value Description
    ima.disable true IMA must be disabled as it is not supported in the DirectQuery preview.
    directquery.enabled true Enables the use of DirectQuery for this instance.
    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.

    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.

    note

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