Overrides with DirectQuery

This section explains how to define overrides in 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: sample-data/data/2018-09-28/Sensitivities/samples/Underlying_Desc_Overrides.csv This example performs 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

This simple example shifts the “Carbon Emissions Trading” Underlying of the “Commodity” RiskClass into Bucket “3a” for the “CRR3” Parameter Set. The desired override is thus as follows:

​

Populate the override table

Start by defining the override in the appropriate override table. For this example, the override is defined in the Underlying Description Override table. An AsOfDate that the override is effective for must also be defined. This example uses an AsOfDate of "2018-09-28" (the latest date in the provided reference data). The table contains the following data (table is transposed for readability due to the large number of columns):

​

Populate the override base table

Next, inject some data into the override table’s base table. The base table can be found in the Override Base Table section for the Underlying Description Overrides.
The base table is the Underlying Description table. The members to shift the override into have to exist in the base table, otherwise Atoti will not be able to shift the data. Atoti can be made aware of the new member by adding the new member to the base table. An entry must be generated in the Underlying Description table in the following sequence:

• Define the Override Parameters fields from the base store.
• Create the Override Tuples.
• Map the override tuples back to the base table using the Override Fields to Base Table Fields Mapping.

​

Define override parameters base table fields

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

​

Create the override tuples

The Create Base Store Tuples section shows that the following fields are the override’s fields to create tuples for: The override tuples can be defined by going through each field in the override definition:

PARAMETER_SET

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

BUCKET

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

​

Map override tuples to base table

Now map the override tuples to base table entries. Use the following to map the override tuples back to the Underlying Description table: Using the above mapping generates the following entries for the Underlying Description table. “N/A” is used for the RISK_CLASS as it is a non-nullable field: