Skip to content

EWE Extensions

This document describes customer-specific models and extensions in the EWE Numera implementation.

Overview

EWE extends the core Numera platform with:

  • Credit Exposure Models: Daily and monthly exposure tracking
  • Clearing Statement Models: ABN AMRO and BNP Paribas integration
  • Power Volume Models: 15-minute granularity power trading analysis
  • Party Extensions: Agreement attributes and netting terms

Credit Exposure Models

Data Source

Credit exposure data originates from Endur's credit risk management module, exported via UDSRs (User-Defined Stored Results) from batch simulation runs.

Model Chain

EWE Credit Model Chain

Key Models

fct_credit_exposure

Daily credit exposure metrics by counterparty and exposure line.

Business Purpose: Monitor credit risk utilization against limits.

Key Columns: - metric_date - Valuation date - dim_party_external_bu_le_attribute_id - Link to counterparty - exp_line_id - Exposure line identifier - current_exposure - MTM exposure - potential_exposure - Worst-case exposure - exposure_limit - Credit limit

Example Query: 

SELECT
    pe.lentity_name AS counterparty,
    f.current_exposure,
    f.exposure_limit,
    f.current_exposure / NULLIF(f.exposure_limit, 0) * 100 AS utilization_pct
FROM fct_credit_exposure f
JOIN dim_party_external pe USING (dim_party_external_bu_le_attribute_id)
WHERE f.metric_date = CURRENT_DATE - 1
ORDER BY utilization_pct DESC

fct_credit_exposure_month

Monthly forward projections of credit exposure.

Business Purpose: Plan for future credit utilization and collateral needs.

Additional Columns: - effective_month - Projection month - potential_exposure_high/low - Range scenarios - pot_performance_exposure_high/low - Performance scenarios

fct_credit_pex_factor

PEX (Potential Exposure) factors by commodity.

Business Purpose: Understand commodity-specific volatility in exposure calculations.

EWE-Specific Logic

Legal Entity Mapping: EWE has specific business rules where some exposures involving two legal entities are measured against only one. The mapping layer (map_endur_exposure_party_attribute) excludes legal_entity_id = 20684 for this purpose.

Clearing Statement Models

Data Sources

EWE receives clearing data from two banks:

Bank Data Types
ABN AMRO Daily cash movement, cash summaries, purchases/sales, transactions, open positions
BNP Paribas Journal entries, margin calls, P&S, trades, open positions

Model Chain

EWE Clearing Model Chain

Key Model

fct_clearing_statement_open_position

Unified view of clearing bank open positions.

Business Purpose: Consolidated view of exchange-traded positions across clearing banks.

Unified Columns (standardized across both banks): - metric_date - Statement date - account - Clearing account - exchange - Exchange code - symbol - Contract symbol - buy_sell - Direction ('Buy' or 'Sell') - quantity - Number of contracts - volume - Total volume - ote - Open trade equity - settlement_price - Current settlement price - variation_margin - Daily margin movement

Party Dimension Linkage: - ABN positions link to 'ABN AMRO CLEARING BANK N.V.' - BNP positions link to 'BNP PARIBAS S.A.'

Example Query: 

SELECT
    pe.bunit_name AS clearing_bank,
    f.exchange,
    f.symbol,
    SUM(f.quantity) AS total_contracts,
    SUM(f.ote) AS total_ote
FROM fct_clearing_statement_open_position f
JOIN dim_party_external pe USING (dim_party_external_bu_le_attribute_id)
WHERE f.metric_date = CURRENT_DATE - 1
GROUP BY 1, 2, 3
ORDER BY total_ote DESC

Feature Flag

Clearing statement models are controlled by the feed.clearing_statements feature flag. If disabled, these models are not built.

Power Volume Models

Data Source

Power volume models aggregate 15-minute time series data from the core fct_volume_15m model.

Key Models

fct_ewe_volume_pwr_by_business_case

Aggregated 15-minute power volumes by business case and grid.

Business Purpose: Analyze short-term power trading performance by strategy.

Key Columns: - delivery_date_utc - Delivery date - begin_utc, end_utc - 15-minute period (UTC) - begin_cet, end_cet - 15-minute period (CET) - grid - Power control area - business_case - Trading strategy/desk - volume_buy_mw, volume_sell_mw - Volumes in MW - buy_vwap_price_eur_per_mwh, sell_vwap_price_eur_per_mwh - VWAP prices

Aggregation Logic: - Groups across all deals - Filters to tran_status IN ('Validated', 'Matured') - Uses ARRAY_CONSTRUCT for 96 time slots per day

Example Query: 

SELECT
    delivery_date_utc,
    business_case,
    SUM(volume_buy_mw) AS total_buy,
    SUM(volume_sell_mw) AS total_sell,
    SUM(volume_buy_mw) - SUM(volume_sell_mw) AS net_position
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY 1, 2
ORDER BY business_case

fct_ewe_volume_pwr_by_business_case_deal

Same as above but retains individual deal_id for deal-level analysis.

Unfiltered Variants

For controlling purposes, unfiltered versions exist that include all business units: - fct_ewe_volume_pwr_by_business_case_unfiltered - fct_ewe_volume_pwr_by_business_case_deal_unfiltered

Floating Power Volume Models

For index-linked power trades (instrument_sub_type = 'PWR Index'), floating variants provide volumes with the associated power index prices:

fct_ewe_volume_pwr_floating_by_business_case

Business Purpose: Analyze index-linked power trading with associated index prices.

Key Columns: - delivery_date_utc - Delivery date - begin_utc, end_utc - 15-minute period (UTC) - begin_cet, end_cet - 15-minute period (CET) - grid - Power control area - business_case - Trading strategy/desk - internal_business_unit, external_business_unit - Party names - power_index_name - Index name (e.g., EPEX Spot) - volume_buy_mw, volume_sell_mw - Volumes in MW - power_index_price_eur_per_mwh - Index price

Example Query: 

SELECT
    delivery_date_utc,
    business_case,
    power_index_name,
    SUM(volume_buy_mw) AS total_buy,
    SUM(volume_sell_mw) AS total_sell,
    AVG(power_index_price_eur_per_mwh) AS avg_index_price
FROM fct_ewe_volume_pwr_floating_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY 1, 2, 3
ORDER BY business_case

fct_ewe_volume_pwr_floating_by_business_case_deal

Same as above but retains individual deal_id for deal-level analysis.

Feature Flag

Power volume models are controlled by the time_series.15m feature flag.

Party Extensions

dim_party_agreement

Party agreement attributes including netting terms.

Business Purpose: Track contractual netting arrangements for credit exposure management.

Key Columns: - party_agreement_id - Agreement identifier - party_agreement_name - Agreement name - has_close_out_netting - Close-out netting enabled - has_payment_netting - Payment netting enabled - collateral_agreement_id - Linked collateral agreement - has_cross_commodity_netting - Cross-commodity netting enabled

Example Query: 

SELECT
    party_agreement_name,
    has_payment_netting,
    has_close_out_netting,
    has_cross_commodity_netting
FROM dim_party_agreement
WHERE has_close_out_netting = TRUE

dim_party_agreement_attribute

Simplified view of party agreement attributes, used for dimensional joins.

Archive Models

E17 Archive

Historical data from the legacy E17 data warehouse extraction system.

Purpose: Historical reference and validation.

Key Models: - e17_user_dw_udsr_credit_exposures - Historical credit exposures - e17_user_dw_udsr_party_data - Historical party data - e17_pnl_details_2023_2024 - P&L archive

Feature Flag: repdb_archive

Monitoring Models

fct_data_arrival

Tracks data freshness across sources.

Business Purpose: Monitor that source data is arriving on schedule.

Data Monitored: - Endur simulation results - ABN clearing statements - BNP clearing statements

Example Query: 

SELECT *
FROM fct_data_arrival
WHERE metric_date >= CURRENT_DATE - 7
ORDER BY metric_date DESC

Feature Flag Summary

Flag Models Controlled
feed.clearing_statements All clearing statement models
time_series.15m Power volume models
repdb_archive E17 archive models