Microsoft SQL Server

This section explains how to connect to a Microsoft SQL Server Database. Atoti Market Risk uses DirectQuery to connect to the database, for a full list of features and support, please refer to Atoti’s Microsoft SQL page.

Overall Sequence

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

1. Configure your remote Database in Atoti Market Risk's expected Database format
2. Set the Atoti Market Risk configuration properties
3. Deploy Atoti Market Risk in Horizontal Distribution

Database Schema

Required DirectQuery Properties

DirectQuery only supports scalar mode for the Sensitivities Cube. In addition, the Summary Cubes can’t be activated with DirectQuery, so they have to be disabled:

mr:
  enable:
    data-model:
      # Set the sensitivity cube to scalar mode
      scalar-sensitivities: true
    cubes:
      # Disable all summary cubes
      var-summary: false
      pnl-summary: false
      sensi-summary: false

MSSQL Configuration

Properties for enabling and connecting to a remote database can be defined using either the YAML or standard property formats. These properties can be used in .yaml and .properties files, or on the command line.

# MSSQL Database Connection Parameters
directquery:
  enabled: true
  withAdjustments: false
  database:
    type: mssql
    mssql:
      username: YOUR_USERNAME
      password: YOUR_PASSWORD
      port: YOUR_PORT
      hostName: YOUR_HOST
      database: YOUR_DATABASE
      schema: YOUR_SCHEMA
      encrypt: false
      trustServerCertificate: true
      connectRetryCount: 5

The above properties populate the MSSQLSpringProperties java class.

username

Full property name: directquery.database.mssql.username

Sets the user name that will be used when connecting to SQL Server.

password

Full property name: directquery.database.mssql.password

Sets the password that will be used when connecting to SQL Server.

port

Full property name: directquery.database.mssql.port

Sets the TCP-IP port number used when opening a socket connection to SQL Server.

hostName

Full property name: directquery.database.mssql.hostName

Hostname of where the MSSQL database is running. For example, when running locally we can specify “localhost”.

database

Full property name: directquery.database.mssql.database

The Microsoft SQL Database you will be connecting to.

schema

Full property name: directquery.database.mssql.schema

The Schema within the specified Database to use. This Schema should match Atoti Market Risk’s expected Database Structure either by having the same Table structure or through the use of views.

encrypt

Full property name: directquery.database.mssql.encrypt

Set to true if the Secure Sockets Layer (SSL) encryption is enabled between the client and the SQL Server, false otherwise.

trustServerCertificate

Full property name: directquery.database.mssql.trustServerCertificate

Set to true if the SSL certificate should be automatically trusted when the communication layer is encrypted using SSL, false otherwise.

connectRetryCount

Full property name: directquery.database.mssql.connectRetryCount

The maximum number of attempts to re-establish a broken connection.

Deployment Options

We provide two main options to run with DirectQuery:

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

mr.data-load.initial-business-dates

For both Combined in-memory query node and DirectQuery data node, set the mr.data-load.initial-business-dates property.

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.

JVM With Query Node And In-Memory Data Node

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

Start the JVM by specifying the following parameters to the Atoti Market Risk application, either in a .properties file or through command-line arguments (add -D before each property).

See Activating and de-activating cubes for details about the enabling/disabling of cubes.

# Disable cubes
mr.enable.cubes.<cube>: false

# Set MR to run this JVM with a query node and in-memory data node
spring.profiles.active=dist-data-node,dist-query-node

JVM With DirectQuery Data Node

Once the JVM with the query and in-memory data node is running, you can start a second JVM with the DirectQuery data node with the following configuration properties either in a .properties file or through command-line arguments (add -D before each property).

# Set MR to run as a data node and to connect to the second JVM
spring.profiles.active=dist-data-node

# Use a different port from the second JVM
server.port=10011

# Use a different Content Server for this JVM
mr.application.content-service.db.url=jdbc:h2:mem:content_service;DB_CLOSE_DELAY=-1
mr.application.content-service.db.hibernate.hbm2ddl.auto=create

# Disable cubes
mr.enable.cubes.<cube>: false

# Enable and configure the MSSQL DirectQuery Database
directquery.enabled=true
directquery.database.type=mssql
directquery.database.mssql.username=database_username
directquery.database.mssql.password=database_password
directquery.database.mssql.port=1433
directquery.database.mssql.hostName=database_host
directquery.database.mssql.database=mssql_database
directquery.database.mssql.schema=mssql_database_schema

Single JVM

You can run a single JVM consisting of only DirectQuery.

By running in a single JVM with DirectQuery only, you can now see the DirectQuery data in the VaR/ES cube.

Properties

To run the Single JVM node you only need to configure the MSSQL Properties and disable cubes and run the application as normal. There are no distributed nodes to configure when running in a single JVM.

Here is an example of the properties to use:

See Activating and de-activating cubes for details about the enabling/disabling of cubes.

# Disable cubes
mr.enable.cubes.<cube>: false

# Enable and configure the MSSQL DirectQuery Database
directquery.enabled=true
directquery.database.type=mssql
directquery.database.mssql.username=database_username
directquery.database.mssql.password=database_password
directquery.database.mssql.port=1433
directquery.database.mssql.hostName=database_host
directquery.database.mssql.database=mssql_database
directquery.database.mssql.schema=mssql_database_schema

Single JVM Limitations

By running DirectQuery with a single JVM you will be running purely on DirectQuery. This will come at the compromise of query performance of Trade level queries while also not having access to in-memory only tools such as WhatIf and SignOff.

Reference MSSQL Database

Atoti Market Risk ships with an example MSSQL database that can be used for testing. This database runs inside a docker container. It is located within the directory: mr-directquery/src/test/resources/databases/mssql/.