Endur Concepts¶

This document explains key Endur trading system concepts and how they map to the Numera data model.

Overview¶

EWE uses Openlink Endur as its Energy Trading and Risk Management (ETRM) system. Understanding Endur concepts helps you: - Interpret the dimensional model correctly - Understand data relationships and hierarchies - Navigate between Endur screens and Numera reports - Troubleshoot data discrepancies

Deal Structures¶

Deal Lifecycle and Identifiers¶

Endur deals have multiple identifiers that serve different purposes:

Identifier	Description	Persistence	Example Use
`deal_id`	Persistent deal identifier	Survives amendments	Track a deal over its lifetime
`tran_id`	Transaction identifier	Unique per version	Reference specific transaction
`tran_version`	Amendment sequence number	Increments with changes	Track amendment history
`ins_num`	Endur internal instrument number	Unique per transaction	Endur system reference

Key Principle: The deal_id remains constant throughout a deal's life. When a deal is amended, a new tran_id is created with incremented tran_version, but deal_id stays the same.

-- Track deal amendments over time
SELECT
    deal_id,
    tran_id,
    tran_version,
    tran_status,
    trade_date
FROM dim_tran_info
WHERE deal_id = 12345
ORDER BY tran_version

Deal Sides (Multi-Leg Deals)¶

A single deal may have multiple "sides" representing different legs or settlement terms. Each side has a param_seq_id identifier.

Common Multi-Side Scenarios: - Swaps: Buy one commodity, sell another - Spread trades: Different delivery periods or locations - Option strategies: Multiple strikes or maturities - Pipeline transport: Receipt and delivery legs

-- View all sides of a deal
SELECT
    deal_id,
    param_seq_id AS side_id,
    ds.market,
    ds.commodity,
    ds.fix_float,
    ds.proj_index
FROM fct_deal_side f
JOIN dim_deal_sub_attribute ds
    ON f.dim_deal_sub_attribute_id = ds.dim_deal_sub_attribute_id
WHERE deal_id = 12345
ORDER BY param_seq_id

Dimensions: - dim_deal_attribute: Deal-level attributes (portfolio, buy_sell, instrument) - dim_deal_sub_attribute: Side-level attributes (per param_seq_id)

Transaction Status¶

Deals progress through various statuses during their lifecycle:

Status	Description	Include in Reporting?
New	Recently created, not yet validated	No
Validated	Approved and active	Yes
Amended	Modified from original terms	Yes
Matured	Delivery completed, final settlements	Yes
Cancelled	Terminated before completion	No
Closeout	Force-terminated (e.g., default)	Depends on use case

Standard Filter for Business Reporting:

WHERE tran_status IN ('Validated', 'Matured', 'Amended')

This excludes: - New deals not yet approved - Cancelled deals that never settled - Test transactions

-- Active deal count by status
SELECT
    tran_status,
    COUNT(DISTINCT deal_id) AS deal_count
FROM dim_deal_attribute
GROUP BY tran_status
ORDER BY tran_status

Portfolio Hierarchies¶

Portfolio Structure¶

In Endur, portfolios are organizational units for grouping trades for P&L and risk reporting.

Typical EWE Portfolio Structure:

Business Unit
    └── Portfolio (e.g., GAS_Trading)
            └── Sub-Portfolio (optional)
                    └── Book (optional)

In Numera: Portfolios are captured in dim_deal_attribute.portfolio.

-- P&L by portfolio
SELECT
    portfolio,
    SUM(pnl_realized_ltd) AS realized_pnl,
    SUM(pnl_unrealized_ltd) AS unrealized_pnl,
    SUM(pnl_ltd) AS total_pnl
FROM fct_pnl f
JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE metric_date = CURRENT_DATE - 1
  AND d.dim_deal_attribute_id != 0
GROUP BY portfolio
ORDER BY total_pnl DESC

Internal Business Units and Legal Entities¶

EWE's internal organization is captured in the party dimension:

Level	Description	Field
Business Unit	Operational trading desk	`bunit_name`
Legal Entity	Legal contracting entity	`lentity_name`

-- P&L by internal business unit
SELECT
    pi.bunit_name AS internal_bu,
    pi.lentity_name AS legal_entity,
    SUM(f.pnl_ltd) AS total_pnl
FROM fct_pnl f
JOIN dim_party_internal pi
    ON f.dim_party_internal_bu_le_attribute_id = pi.dim_party_internal_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
GROUP BY pi.bunit_name, pi.lentity_name
ORDER BY total_pnl DESC

Position and P&L Concepts¶

Mark-to-Market (MTM) Valuation¶

Endur calculates MTM valuations using: - Forward curves: Future price expectations - Discount curves: Time value of money - Volatility surfaces: Option pricing

In Numera: MTM results appear in fct_pnl as pnl_unrealized_ltd.

Realized vs Unrealized P&L¶

Type	Description	When Recognized	Endur Field
Realized	Cash settled	At settlement/payment	`pnl_realized_ltd`
Unrealized	MTM on open positions	Daily revaluation	`pnl_unrealized_ltd`
Total	Sum of realized + unrealized	Always	`pnl_ltd`

Example: - You buy gas at €20/MWh for January delivery - Current market price is €25/MWh - Unrealized P&L: +€5/MWh - When January arrives and settles at €25/MWh: - Realized P&L: +€5/MWh - Unrealized P&L: €0

-- Realized vs unrealized P&L by portfolio
SELECT
    d.portfolio,
    SUM(f.pnl_realized_ltd) AS realized,
    SUM(f.pnl_unrealized_ltd) AS unrealized,
    SUM(f.pnl_ltd) AS total,
    ROUND(SUM(f.pnl_unrealized_ltd) / NULLIF(SUM(f.pnl_ltd), 0) * 100, 1) AS pct_unrealized
FROM fct_pnl f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND d.dim_deal_attribute_id != 0
GROUP BY d.portfolio
ORDER BY total DESC

Delivery Month Bucketing¶

P&L is bucketed by delivery month - the month when commodity delivery occurs.

Why This Matters: - Forward exposure visibility - Risk management by time period - Hedging strategy validation

-- P&L by delivery month (forward curve)
SELECT
    delivery_month,
    d.portfolio,
    SUM(f.pnl_ltd) AS total_pnl
FROM fct_pnl f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND f.delivery_month >= DATE_TRUNC('month', CURRENT_DATE)
  AND f.delivery_month < DATEADD('month', 12, DATE_TRUNC('month', CURRENT_DATE))
  AND d.dim_deal_attribute_id != 0
GROUP BY delivery_month, d.portfolio
ORDER BY delivery_month, d.portfolio

Simulation Results and UDSRs¶

User-Defined Stored Results (UDSRs)¶

Endur runs batch simulations to calculate metrics like: - P&L (profit and loss) - Credit exposure - Delta (price sensitivity) - VaR (value at risk)

Results are exported as UDSRs (User-Defined Stored Results) and loaded into Numera.

Key UDSR Identifiers: - sim_run_id: Unique simulation run - sim_version: Version within run (for reruns) - scenario_id: Scenario being calculated (base case, stress tests) - metric_date: As-of date for the calculation

Data Flow: UDSR to Dimensional Model¶

Endur Batch Simulation
    ↓
UDSR Export
    ↓
SAT Tables (full history)
sat_endur_sim_result_udsr_*
    ↓
STG Tables (schema applied)
stg_endur_sim_result_udsr_*
    ↓
PIT Tables (point-in-time, latest per metric_date)
pit_endur_*
    ↓
Dimensional Tables (customer-facing)
fct_pnl, fct_credit_exposure, fct_delta, etc.

Latest Simulation Selection: The PIT (Point-In-Time) layer automatically selects the latest simulation run for each metric date:

-- Example PIT logic (simplified)
SELECT *
FROM sat_endur_sim_result_udsr_pnl
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY metric_date, deal_id, delivery_month
    ORDER BY sim_run_id DESC, sim_version DESC
) = 1

This ensures: - No duplicates in dimensional tables - Always the latest calculation results - Consistent metric_date view

Simulation Schedule¶

Typical EWE simulation schedule: - EOD (End of Day): Main daily valuation run - Intraday: Ad-hoc runs as needed - Month-end: Additional validation runs

Checking Simulation Freshness:

-- Latest available metric date in P&L
SELECT MAX(metric_date) AS latest_pnl_date
FROM fct_pnl

-- Latest simulation run details
SELECT
    metric_date,
    sim_run_id,
    sim_version,
    COUNT(*) AS record_count
FROM pit_endur_pnl
WHERE metric_date = (SELECT MAX(metric_date) FROM pit_endur_pnl)
GROUP BY metric_date, sim_run_id, sim_version

Instrument Types¶

Endur categorizes trades by instrument type:

Instrument	Description	Physical/Financial	Typical Use
GAS-PHYS	Physical gas delivery	Physical	Supply contracts
POWER-PHYS	Physical power delivery	Physical	Power trading
GAS-FIN	Gas financial derivative	Financial	Hedging, speculation
POWER-FIN	Power financial derivative	Financial	Hedging, speculation
PWR Index	Index-linked power	Physical	Spot market exposure
SWAP	Commodity swap	Financial	Basis trading

-- Deal count by instrument type
SELECT
    instrument,
    commodity,
    COUNT(DISTINCT deal_id) AS deal_count,
    COUNT(DISTINCT CASE
        WHEN tran_status IN ('Validated', 'Matured') THEN deal_id
    END) AS active_deal_count
FROM dim_deal_attribute
GROUP BY instrument, commodity
ORDER BY instrument, commodity

Risk Book Mappings¶

In Endur, risk books organize deals for risk calculations.

In Numera: Risk books are typically reflected in: - Portfolio dimension - Internal business unit - Deal attributes

Risk metrics (delta, VaR) are aggregated by these dimensions:

-- Delta exposure by portfolio (risk book view)
SELECT
    d.portfolio,
    ia.index_name,
    SUM(f.delta_mwh) AS total_delta
FROM fct_delta f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_index_attribute ia ON f.dim_index_attribute_id = ia.dim_index_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND f.delivery_month >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY d.portfolio, ia.index_name
ORDER BY d.portfolio, ABS(total_delta) DESC

Endur-to-Numera Mapping Summary¶

Endur Concept	Numera Table/Column	Notes
Deal ID	`dim_deal_attribute.deal_id`	Persistent identifier
Transaction ID	`dim_tran_info.tran_id`	Version-specific
Transaction Version	`dim_tran_info.tran_version`	Amendment sequence
Deal Side	`fct_deal_side.param_seq_id`	Multi-leg identifier
Portfolio	`dim_deal_attribute.portfolio`	P&L reporting unit
Instrument	`dim_deal_attribute.instrument`	Product type
Business Unit	`dim_party_internal.bunit_name`	Operational unit
Legal Entity	`dim_party_internal.lentity_name`	Legal entity
Transaction Status	`dim_deal_attribute.tran_status`	Lifecycle status
UDSR Results	`fct_pnl`, `fct_credit_exposure`, etc.	Simulation outputs
Simulation Run	PIT tables: `sim_run_id`, `sim_version`	Latest run selected

Common Endur-Numera Questions¶

Q: Why do I see multiple rows for the same deal?¶

A: Likely causes: 1. Multiple sides: Check param_seq_id - multi-leg deals have multiple sides 2. Multiple delivery months: P&L bucketed by delivery month 3. Dimension fanout: Joining incorrectly (use LEFT JOIN from facts)

-- Check if deal has multiple sides
SELECT deal_id, COUNT(*) AS side_count
FROM fct_deal_side
WHERE deal_id = 12345
GROUP BY deal_id

Q: Why doesn't my P&L match Endur?¶

A: Check these factors: 1. Metric date: Are you using the same as-of date? 2. Transaction status filter: Include Validated, Matured, Amended 3. Simulation run: Numera uses latest; Endur may show different run 4. Portfolio mapping: Verify correct portfolio in filter 5. Reval type: MTM vs Settlement valuations

Q: How do I find the latest deal version?¶

A: Numera dimensional tables automatically show current state. For historical versions:

-- Latest version of a deal
SELECT
    deal_id,
    tran_id,
    MAX(tran_version) AS latest_version
FROM dim_tran_info
WHERE deal_id = 12345
GROUP BY deal_id, tran_id

Q: What is the difference between dim_deal_attribute and dim_deal_sub_attribute?¶

A: - dim_deal_attribute: Deal-level attributes (portfolio, buy_sell, instrument) - dim_deal_sub_attribute: Side-level attributes for multi-leg deals (param_seq_id)

Most queries use dim_deal_attribute. Use dim_deal_sub_attribute only when analyzing individual deal sides.

Best Practices¶

Always filter by tran_status: Include only Validated, Matured, Amended for business reporting
Use deal_id for tracking: Track deals over time with persistent deal_id
Understand delivery bucketing: P&L and risk are bucketed by delivery period
Check metric_date freshness: Verify latest available date before reporting
Left join dimensions: Always LEFT JOIN from fact to dimension to avoid data loss

Business Glossary - Trading term definitions
Dimensional Model - Star schema design
Data Layers - UDSR to dimensional flow
Model Reference - Table and column documentation
Query Patterns - SQL examples