☰
Navigation :

Overrides with DirectQuery

Overrides with DirectQuery

This section explains how to define overrides in Atoti FRTB using DirectQuery and also how to modify the relevant database tables.

Overrides allow for shifting data for a specific parameter set into a specific location.

There are two steps to defining overrides with DirectQuery:

  1. Define the override in the override table.
  2. Add the new override members to the base table.

Step 1: Define override in the override Table

Simply add a row in the override table.

Step 2: Add the new override member to the Base Table

Next, add data into the override table’s base table. Details are in the Override Base Table section for each override table in the Database chapter.

Ensure the following:

  • shift members exist in the base table, otherwise Atoti will not be able to shift the data as it cannot see the shift member of the shift member’s hierarchy.

  • The OVERRIDE KEY FIELD field of the base table is populated with a custom string in the format: “memberToShift::memberValue”.
    The memberToShift and memberValue come from the override’s definition.

Example

This example is based on the reference override defined in the input file: frtb-starter/src/test/resources/data/2018-09-28/Sensitivities/samples/Underlying_Desc_Overrides.csv

In this example we perform the following steps:

  1. Define the override.
  2. Populate the Underlying Description Overrides table.
  3. Populate the override base Underlying Description table.

Define the Override

Here is a simple example where we want to shift our “Carbon Emissions Trading” Underlying of the “Commodity” RiskClass into Bucket “3a” for the “CRR3” Parameter Set.

Our desired override is thus as follows:

Underlying Risk Class ParameterSet Bucket
Carbon Emissions Trading Commodity CRR3 3a

Populate the Override Table

We first start by defining the override in the appropriate override table. For this example we are defining the override in the Underlying Description Override table. We also need to define an AsOfDate that the override is effective for. Here we use an AsOfDate of "2018-09-28" (the latest date in the provided reference data).

Our table contains the following data (table is transposed for readability due to the large number of columns):

Underlying Description Overrides Field Not Null Value
UNDERLYING Yes Carbon Emissions Trading
RISK_CLASS Yes Commodity
PARAMETER_SET Yes CRR3
BUCKET 3a
CSRQUALITY
CSRSECTOR
CSRRATING
EQUITY_MARKET_CAP
EQUITY_ECONOMY
EQUITY_SECTOR
POOL
ATTACHMENT
DETACHMENT
AS_OF_DATE Yes 2018-09-28

Populate the Override Base Table

We now inject some data into the override table’s base table. We can find the base table in the Override Base Table section for the Underlying Description Overrides.
The base table is the Underlying Description table.

The members we want to shift our override into have to exist in the base table, otherwise Atoti will not be able to shift the data. We can make Atoti aware of our new member by adding the new member to the base table.

We must generate an entry in the Underlying Description table in the following sequence:

Define Override Parameters Base Table Fields

For Underlying Description overrides our parameter fields can be found in Override Parameters:

Override Parameter Underlying Description Field
OVERRIDE KEY FIELD UNDERLYING
OVERRIDE DATE FIELD AS_OF_DATE

Create the Override Tuples

We can see in the Create Base Store Tuples section that the following fields are the override’s fields to create tuples for:

Underlying Description Override Field
PARAMETER_SET
BUCKET
CSRQUALITY
CSRSECTOR
CSRRATING
EQUITY_MARKET_CAP
EQUITY_ECONOMY
EQUITY_SECTOR
POOL
ATTACHMENT
DETACHMENT

We can define the override tuples by going through each field in our override definition:

Field Tuple to generate
UNDERLYING
RISK_CLASS
 These do not exist in the Underlying Description override Fields, so we can skip them.
PARAMETER_SET See below.
BUCKET See below.
CSRQUALITY
CSRSECTOR
CSRRATING
EQUITY_MARKET_CAP
EQUITY_ECONOMY
EQUITY_SECTOR
POOL
ATTACHMENT
DETACHMENT		 These are not defined, so we can skip them.
AS_OF_DATE Does not exist in the Underlying Description override Fields, so we can skip it.
PARAMETER_SET

The ParameterSet field does exist in the Underlying Description override Fields, so we generate the following tuple:

Underlying Description Override Field Value
OVERRIDE KEY FIELD ParameterSet::CRR3
OVERRIDE DATE FIELD
ParameterSet CRR3
Bucket
CSRQuality
CSRRating
EquityMarketCap
EquityEconomy
EquitySector
Pool
Attachment
Detachment
CSRSector
BUCKET

The Bucket field does exist in the Underlying Description override Fields, so we generate the following tuple:

Underlying Description Override Field Value
OVERRIDE KEY FIELD Bucket::3a
OVERRIDE DATE FIELD
ParameterSet
Bucket 3a
CSRQuality
CSRRating
EquityMarketCap
EquityEconomy
EquitySector
Pool
Attachment
Detachment
CSRSector

Map Override Tuples to Base Table

We can now map the override tuples to base table entries. We use the following to map the override tuples back to the Underlying Description table:

note

There is no mapping for the ParameterSet field

Underlying Description Override Field Underlying Description Field Note
OVERRIDE KEY FIELD UNDERLYING
OVERRIDE DATE FIELD AS_OF_DATE
PARAMETER_SET No mapping exists
BUCKET BUCKET
CSRQUALITY CSRQUALITY
CSRSECTOR CSRSECTOR
CSRRATING CSRRATING
EQUITY_MARKET_CAP EQUITY_MARKET_CAP
EQUITY_ECONOMY EQUITY_ECONOMY
EQUITY_SECTOR EQUITY_SECTOR
POOL POOL
ATTACHMENT ATTACHMENT
DETACHMENT DETACHMENT
RISK_CLASS No mapping exists
GIRR_CURVE_TYPE No mapping exists
GIRR_CCY No mapping exists
UNDERLYING_FXORIGINAL_CCY No mapping exists

By using the above mapping we generate the following entries for the Underlying Description table. We use “N/A” for the RISK_CLASS as it is a non-nullable field:

Underlying Description Field Not Null Entry 1 Entry 2
AS_OF_DATE Y 2018-09-28 2018-09-28
UNDERLYING Y ParameterSet::CRR3 Bucket::3a
RISK_CLASS Y “N/A” “N/A”
BUCKET 3a
GIRR_CURVE_TYPE
GIRR_CCY
CSRQUALITY
CSRSECTOR
CSRRATING
EQUITY_MARKET_CAP
EQUITY_ECONOMY
EQUITY_SECTOR
POOL
ATTACHMENT
DETACHMENT
UNDERLYING_FXORIGINAL_CCY