Overrides with DirectQuery

Documentation Index

Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt

Use this file to discover all available pages before exploring further.

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

​

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):

​

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 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 our parameter fields can be found in Override Parameters:

​

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: We can define the override tuples by going through each field in our 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

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