Dimensional Model¶

This document describes the Kimball-style star schema used in the EWE Numera platform.

Star Schema Overview¶

The dimensional model follows Kimball methodology:

Fact tables contain measurable business events (P&L, volumes, exposures)
Dimension tables provide descriptive context (parties, deals, dates, locations)
Surrogate keys enable efficient joins and handle changing business keys
Placeholder rows (ID = 0) represent unknown or not applicable values

Fact Tables¶

P&L Facts¶

Table	Grain	Key Measures
`fct_pnl`	metric_date + dimensions + delivery_month	pnl_realized_ltd, pnl_unrealized_ltd, pnl_ltd
`fct_pnl_deal`	metric_date + deal + delivery_period	pnl_amount

Volume Facts¶

Table	Grain	Key Measures
`fct_volume`	period + deal side	volume_mwh
`fct_volume_daily`	date + deal side	volume_mwh, minutes_in_period
`fct_schedule`	period + deal + version	volume_abs, is_best_available

Credit Exposure Facts (EWE)¶

Table	Grain	Key Measures
`fct_credit_exposure`	metric_date + exp_line + party	current_exposure, potential_exposure, exposure_limit
`fct_credit_exposure_month`	metric_date + exp_line + party + effective_month	exposure projections with high/low
`fct_credit_pex_factor`	metric_date + commodity	factor

Clearing Statement Facts (EWE)¶

Table	Grain	Key Measures
`fct_clearing_statement_open_position`	metric_date + account + symbol	quantity, volume, ote, variation_margin

Power Volume Facts (EWE)¶

Table	Grain	Key Measures
`fct_ewe_volume_pwr_by_business_case`	date + 15min period + business_case + grid	volume_buy_mw, volume_sell_mw, VWAP
`fct_ewe_volume_pwr_by_business_case_deal`	date + 15min period + deal + business_case + grid	volume_buy_mw, volume_sell_mw, VWAP

Risk Facts¶

Table	Grain	Key Measures
`fct_delta`	metric_date + index + deal + delivery_month	delta_mwh, gamma
`fct_var_pfolio`	metric_date + portfolio	var_95, var_99

Deal Facts¶

Table	Grain	Key Measures
`fct_deal`	deal	tran_id, tran_version, trade_date
`fct_deal_side`	deal + side	side attributes

Dimension Tables¶

Party Dimensions¶

Table	Description	Key Columns
`dim_party_internal`	Internal business units and legal entities	bunit_id, bunit_name, lentity_id, lentity_name
`dim_party_external`	External counterparties	bunit_id, bunit_name, lentity_id, lentity_name
`dim_party_agreement`	Party agreement attributes (EWE)	party_agreement_id, netting flags
`dim_trader`	Trade executors	trader_id, trader_name

Deal Dimensions¶

Table	Description	Key Columns
`dim_deal_attribute`	Core deal attributes	portfolio, instrument, buy_sell, commodity, tran_status
`dim_deal_sub_attribute`	Side-level attributes	param_seq_id, market, fix_float
`dim_tran_info`	Transaction identifiers	deal_id, tran_id, tran_version
`dim_tran_status`	Status values	tran_status

Location Dimensions¶

Table	Description	Key Columns
`dim_delivery_location`	Delivery points	location_name, region, pipeline
`dim_point_of_delivery_location`	POD for schedules	location_name, control_area
`dim_point_of_receipt_location`	POR for schedules	location_name, control_area

Time Dimensions¶

Table	Description	Key Columns
`dim_date`	Calendar dimension	date, day_of_week, month, quarter, year
`dim_metric_date`	Valuation dates	metric_date
`dim_trade_date`	Trade execution dates	trade_date
`dim_delivery_start_date`	Delivery period starts	delivery_start_date
`dim_delivery_end_date`	Delivery period ends	delivery_end_date
`dim_month`	Month reference	month_start_date, month_name

Reference Dimensions¶

Table	Description	Key Columns
`dim_volume_type`	Volume classifications	volume_type
`dim_index_attribute`	Price indices	index_name, gridpoint
`dim_currency`	Currencies	currency_code
`dim_unit`	Units of measure	unit_name
`dim_portfolio`	Portfolio definitions	portfolio_name

Surrogate Keys and Placeholders¶

Surrogate Key Pattern¶

All dimension tables use integer surrogate keys:

dim_deal_attribute_id  -- Surrogate key
dim_deal_attribute_key -- Hash key (for lookups)

Benefits: - Efficient integer joins - Handles changing business keys - Consistent across all dimensions

Placeholder Rows¶

ID = 0 represents "Unknown" or "Not Applicable":

-- Placeholder row in dim_deal_attribute
dim_deal_attribute_id = 0
portfolio = 'Unknown'
instrument = 'Unknown'

Usage:

-- Exclude unknown values
WHERE dim_deal_attribute_id != 0
  AND dim_party_external_bu_le_attribute_id != 0

Common Join Patterns¶

Basic Star Join¶

SELECT
    d.portfolio,
    d.instrument,
    pi.bunit_name AS internal_bu,
    pe.lentity_name AS counterparty,
    SUM(f.pnl_ltd) AS total_pnl
FROM fct_pnl f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
LEFT JOIN dim_party_internal pi
    ON f.dim_party_internal_bu_le_attribute_id = pi.dim_party_internal_bu_le_attribute_id
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND d.dim_deal_attribute_id != 0
GROUP BY 1, 2, 3, 4

Filtering on Dimension Attributes¶

-- Filter on dimension columns, not surrogate keys
WHERE d.portfolio = 'GAS_Trading'
  AND d.instrument LIKE 'GAS-%'
  AND s.tran_status IN ('Validated', 'Matured')

Credit Exposure with Party¶

SELECT
    pe.lentity_name AS counterparty,
    f.current_exposure,
    f.potential_exposure,
    f.exposure_limit
FROM fct_credit_exposure f
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
ORDER BY f.current_exposure DESC

Multiple Date Dimensions¶

SELECT
    td.trade_date,
    dsd.delivery_start_date,
    d.portfolio,
    COUNT(*) AS deal_count
FROM fct_deal f
LEFT JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
LEFT JOIN dim_trade_date td ON f.dim_trade_date_id = td.dim_trade_date_id
LEFT JOIN dim_delivery_start_date dsd ON f.dim_delivery_start_date_id = dsd.dim_delivery_start_date_id
GROUP BY 1, 2, 3

Best Practices¶

Use LEFT JOINs¶

Always use LEFT JOIN from facts to dimensions to preserve all fact records:

-- Good: LEFT JOIN preserves all facts
FROM fct_pnl f
LEFT JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id

-- Bad: INNER JOIN may exclude facts with unknown dimensions
FROM fct_pnl f
INNER JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id

Filter on Dimensions¶

Apply business filters on dimension attributes, not fact foreign keys:

-- Good: Filter on dimension attribute
WHERE d.portfolio = 'GAS_Trading'

-- Avoid: Filter on surrogate key
WHERE f.dim_deal_attribute_id IN (SELECT dim_deal_attribute_id FROM dim_deal_attribute WHERE portfolio = 'GAS_Trading')

Check Placeholders¶

Explicitly handle unknown values:

-- Exclude unknowns
WHERE d.dim_deal_attribute_id != 0

-- Or include them explicitly
WHERE d.dim_deal_attribute_id != 0
   OR d.portfolio = 'Unknown'  -- If you want to see unknowns

Filter Early¶

Apply metric_date and other filters early for performance:

-- Good: Filter on fact table first
WHERE f.metric_date = CURRENT_DATE - 1
  AND d.portfolio = 'GAS_Trading'

-- Less efficient: All joins before filter
-- (optimizer may not push down filters)

Data Layers - Pipeline architecture
EWE Extensions - Customer-specific models
Query Patterns - SQL examples
Model Reference - Table documentation