SA ETL for SBM Delta
This topic covers the ETL specifics for the Delta files. It provides an overview of ETL for SBM Delta, starting with a dataflow diagram for SBM Delta. A table shows you the fields included in each “SBM” row of a Delta Sensitivity CSV Input Data file, indicating which stores these fields are copied to during the ETL process.
Dataflow diagram
Vectorization
The following table provides information about the vectors employed within the CSV Input files for each risk class:
Risk Class | Vector Information |
---|---|
- GIRR - CSR non-Sec - CSR Sec CTP - CSR Sec non-CTP Commodity |
The indices of the vectors are the vertices. Vectorization is applied for both tenors and sensitivity values. |
- Equity - FX |
Fields are always single-value single-tenor for a Delta sensitivity. |
Normalization
Delta-relevant stores
The stores that are relevant for Delta are:
Stores |
---|
TradeBase Store |
Delta Store |
RiskFactorDescription Store |
UnderlyingDescription Store |
FX Buckets Store |
GIRR Buckets Store |
Mapping of SBM Delta CSV file fields onto the stores that they populate
Field from CSV file OR ColumnCalculator (if the CSV file field is null) |
TradeBase Store |
Delta Store |
RiskFactor Description Store |
Underlying Description Store | FX Buckets Store |
GIRR Buckets Store |
Key Fields for Vector Creation |
---|---|---|---|---|---|---|---|
AsOfDate | (AsOfDate) | (AsOfDate) | (AsOfDate) | (AsOfDate) | (AsOfDate) | (AsOfDate) | |
TradeId | (TradeId) | (TradeId) | |||||
DeltaCcy | (Ccy) | ||||||
DeltaSensitivities (vector-valued field) |
(Delta Sensitivities) | ||||||
RiskClass | (Risk Class) | (Risk Class) | (Risk Class) | (Risk Class) | |||
RiskMeasure | (Risk Measure) | (Risk Measure) | (Risk Measure) | ||||
SensitivityDates (vector-valued field) |
(SensitivityDates) | ||||||
RiskFactor OR RiskFactor ColumnCalculator | (RiskFactor) | #(RiskFactor) | #(RiskFactor) | #(RiskFactor) | |||
Type | (RiskFactorType) | (GIRR Curve Type) | |||||
GIRRCcy | (GIRR Ccy) | (Bucket) | |||||
Underlying | (Underlying) | (Underlying) | (Bucket) | (Curve) | |||
CSRQuality | (CSRQuality) | ||||||
CSRSector | (CSRSector) | ||||||
EquityEconomy | (EquityEconomy) | ||||||
EquityMarketCap | (EquityMarketCap) | ||||||
EquitySector | (EquitySector) | ||||||
CmtyLocation | (CmtyLocation) | ||||||
FXCounterCurrency | (FXCounterCurrency) | ||||||
Optionality | (Optionality) | ||||||
ALWAYS UnderlyingFxCcy ColumnCalculator | (UnderlyingFxCcy) |
Column Calculators and Tuple Publishers
RiskFactorColumnCalculator
Risk Class | Creation Method for RiskFactor |
---|---|
Commodity | Risk Factor field value is created by concatenating Underlying, CmtyGrade and CmtyLocation, using ‘space’ as a delimiter |
GIRR | Risk Factor field value is created by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter |
Equity | Risk Factor field value is created by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter |
CSR non-Sec | Risk Factor field value is created by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter |
CSR Sec CTP | Risk Factor field value is created by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter |
CSR Sec non-CTP | Risk Factor field value is created by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter |
FX | If FXCounterCurrency: - is empty - Risk Factor field value is created by using Underlying - is populated - Risk Factor field value is created by concatenating Underlying and FXCounterCurrency, using a configurable delimiter |
The Tuple Publisher and Publisher classes
The function of the TuplePublisher and its associated Publisher classes is to separate data in the incoming file row according to its relevance to particular stores and applying ETL logic to the incoming rows:
Tuple Publisher Class | Publisher Class | Function |
---|---|---|
RiskClassTuplePublisher | Splits fields according to the following criteria: - fields relevant to DeltaPublisher OR VegaPublisher OR CurvaturePublisher - fields relevant to MarketDataPublisher (same one for all risk classes) - fields relevant to StaticDataPublisher classes for GIRR Buckets and FX Buckets. If multiple sensitivities are held on the same line, the line is de-multiplexed here. |
|
DeltaPublisher | DeltaPublisher publishes to Delta and TradeBase stores | |
RiskFactorDescriptionPublisher | RiskFactorDescriptionPublisher publishes to the RiskFactorDescription store | |
UnderlyingPublisher | UnderlyingPublisher publishes to the UnderlyingDescription store | |
StaticDataPublisher | StaticDataPublisher is a type that allows publishing non-duplicated rows to a given store. Specific instances define the store (fxBucketsPublisher, girrBucketsPublisher). |