☰
Navigation :

DirectQuery REST service

DirectQuery REST service

The DirectQuery REST service allows for incremental refreshing of the remote database as well as validating the Schema while the application is running.

The REST service is available at: host:port/frtb-starter/directquery/ and contains the following components:

Security configuration

The security configuration is handled by the DQRestServiceSecurityConfig bean defined in the SecurityConfig. The default security configuration limits the use of this service to only the ADMIN role.

Application refresh

The application can be refreshed at any point in time to ensure the DirectQuery aggregates are in sync with the remote database. For more information about this process, see the Atoti Server’s documentation for Data Refresh.

When the application starts, aggregation queries are executed and the results are stored the in-memory. This allows the application to execute queries without needing to run queries on the remote database.

Out-of-the-box, the aggregates are defined to just exclude the Trade level. This is defined in the DirectQueryAggregateProviderConfig class which means trade-level queries have to be executed on the remote database.

If new data is added to the remote database, the application does not reflect these new values. To get the application in sync with the remote database, a refresh operation must be performed.

Optionally an incremental-refresh can be set up to only refresh the data that has changed since the last refresh. This is done by specifying a RefreshRequestDTO in the request body of the /refresh endpoint.

/refresh endpoint

URL: host:port/frtb-starter/directquery/refresh

Request Type: POST

Body: optional RefreshRequestDTO

RefreshRequestDTO parameters

The RefreshRequestDTO contains two subcomponents, of which only one can be specified at a time: TemplateDTO template and List<TableUpdateDTO> tableUpdates. If neither component is specified, a full refresh is performed.

TemplateDTO parameters

The TemplateDTO is used to specify a pre-configured template for the incremental refresh operation. This allows users to specify the changes that have occurred in the remote database, without needing to know the schema of the remote database.

Field Type Description
templateName String The name of the pre-configured Template to use.
changeType ChangeType The type of change on the database. Example: [ADD_ROWS, REMOVE_ROWS, MIXED_CHANGES]
fieldValues Map<String, Object[]> Map of Template Field to new values after the change.

List<TableUpdateDTO> tableUpdates parameters

The tableUpdates is used to specify the exact changes that have occurred in the remote database. This allows you to specify the tables and fields that have been updated, added, or removed without having a pre-configured template.

Field Type Description
changeType ChangeType The type of change on the database. Example: [ADD_ROWS, REMOVE_ROWS, MIXED_CHANGES]
tableName String The local table name of the table the change occurred on.
fields List<FieldUpdateDTO> List of fields that have been updated within the table that has been changed.
FieldUpdateDTO parameters

Specifies the field that has been updated and the new values for that field.

FieldName Type Description
fieldName String Name of the local field in the table that has been changed.
values Object[] The new values for this Field after the change.

JSON response

The response indicates the total time in Milliseconds the refresh operation took to complete.

Example response:

{
    "refreshTimeInMilliseconds": 12345
}

Schema validation

A schema validation check can be performed at any point in time and does not affect the application.

The validation simply runs some checks against the remote database to ensure tables are readable and that there are no duplicate keys in any of the database tables.

If the remote database schema is bad / invalid, this can result in significant performance issues. A schema with duplicate keys can result in longer than normal aggregation / application refresh times.

/validateSchema endpoint

URL: host:port/frtb-starter/directquery/validateSchema

Request Type: GET

Response

The response contains:

  • If there were any errors during the validation check
  • What the errors were
  • The SQL to reproduce the failed check
  • How long the validation check took

Example response:

{
    "errors": [],
    "totalTimeInMilliseconds": 12345,
    "valid": true
}

Aggregate tables

Aggregate tables can be complex to set up and populate manually. The setup outlined here shows how you can use this REST service to generate the SQL statement to create and populate your aggregate tables.

To export the SQL to create aggregate tables, DirectQuery must be enabled, and the application must be running connected to the desired database. Then you can use the following endpoints to help create and configure the aggregate tables to be used with Atoti FRTB.

/aggregate-table-definitions endpoint

URL: host:port/frtb-starter/directquery/aggregate-table-definitions

Request Type: GET

Response

A mapping of cube names -> in-memory aggregate provider name -> remote aggregate table name.

The mapping is constructed as follows:

  • cube names: For each enabled cube (that contains an aggregate provider), the following will be populated:
    • in-memory aggregate provider name: This is the name of the in-memory aggregate provider. If no name is specified, it will be LEAF-Provider-N where N is a unique int.
    • remote aggregate table name: The name of the remote direct query table. If the directquery.aggregate-tables configuration properties are defined, then the remote table name from these properties will be returned, otherwise a database-compatible name will be generated in the format: "cubeName" + "inMemoryProviderName" + "AggregateTable".

The response contains SQL queries for creating ("sqlForCreation") and populating ("sqlForFeeding") the aggregate tables.

Below is an example response with the default Atoti FRTB:

{
  "aggregateProviderToAggregateTableDefinitions" : {
    "InternalModelApproachCube" : [ {
      "inMemoryAggregateProviderName" : "LEAF-Provider-0",
      "remoteAggregateTableName" : "INTERNAL_MODEL_APPROACH_CUBE_LEAF_PROVIDER_0_AGGREGATE_TABLE",
      "sqlForCreation" : "CREATE TABLE `ATOTI`.`FRTB_6_0`.`INTERNAL_MODEL_APPROACH_CUBE_LEAF_PROVIDER_0_AGGREGATE_TABLE` ...",
      "sqlForFeeding" : "INSERT INTO `ATOTI`.`FRTB_6_0`.`INTERNAL_MODEL_APPROACH_CUBE_LEAF_PROVIDER_0_AGGREGATE_TABLE` SELECT ..."
    }, {
      "inMemoryAggregateProviderName" : "LEAF-Provider-1",
      "remoteAggregateTableName" : "INTERNAL_MODEL_APPROACH_CUBE_LEAF_PROVIDER_1_AGGREGATE_TABLE",
      "sqlForCreation" : "CREATE TABLE `ATOTI`.`FRTB_6_0`.`INTERNAL_MODEL_APPROACH_CUBE_LEAF_PROVIDER_1_AGGREGATE_TABLE` ...",
      "sqlForFeeding" : "INSERT INTO `ATOTI`.`FRTB_6_0`.`INTERNAL_MODEL_APPROACH_CUBE_LEAF_PROVIDER_1_AGGREGATE_TABLE` SELECT ..."
    } ],
    "StandardisedApproachCube" : [ {
      "inMemoryAggregateProviderName" : "LEAF-Provider-2",
      "remoteAggregateTableName" : "STANDARDISED_APPROACH_CUBE_LEAF_PROVIDER_2_AGGREGATE_TABLE",
      "sqlForCreation" : "CREATE TABLE `ATOTI`.`FRTB_6_0`.`STANDARDISED_APPROACH_CUBE_LEAF_PROVIDER_2_AGGREGATE_TABLE` ...",
      "sqlForFeeding" : "INSERT INTO `ATOTI`.`FRTB_6_0`.`STANDARDISED_APPROACH_CUBE_LEAF_PROVIDER_2_AGGREGATE_TABLE` SELECT ..."
    } ],
    "FRTBCombinedCube" : [ ]
  }
}