Dimensional Models¶
This page documents the core dimensional models from numera-core that form the foundation of the EWE Numera platform.
P&L Models¶
fct_pnl¶
Aggregated P&L across all dimensions.
Purpose: Primary source for P&L reporting by portfolio, counterparty, and delivery month.
Grain: metric_date + dimension keys + delivery_month
| Column | Type | Description |
|---|---|---|
metric_date |
DATE | Valuation date |
dim_deal_attribute_id |
INTEGER | FK to dim_deal_attribute |
dim_party_internal_bu_le_attribute_id |
INTEGER | FK to dim_party_internal |
dim_party_external_bu_le_attribute_id |
INTEGER | FK to dim_party_external |
dim_tran_status_id |
INTEGER | FK to dim_tran_status |
delivery_month |
DATE | Delivery period (month) |
pnl_realized_ltd |
NUMBER | Realized P&L (life-to-date) |
pnl_unrealized_ltd |
NUMBER | Unrealized P&L (life-to-date) |
pnl_ltd |
NUMBER | Total P&L (life-to-date) |
pnl_ltd_base |
NUMBER | P&L in base currency |
Example Query:
SELECT
d.portfolio,
SUM(f.pnl_realized_ltd) AS realized,
SUM(f.pnl_unrealized_ltd) AS unrealized,
SUM(f.pnl_ltd) AS total
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
fct_pnl_deal¶
Deal-level P&L detail.
Purpose: Drill-down to individual deal P&L for analysis.
Grain: metric_date + deal_id + delivery_period
| Column | Type | Description |
|---|---|---|
metric_date |
DATE | Valuation date |
deal_id |
INTEGER | Deal identifier |
delivery_start_date |
DATE | Delivery period start |
delivery_end_date |
DATE | Delivery period end |
pnl_amount |
NUMBER | P&L amount |
Volume Models¶
fct_volume¶
Aggregated volumes by period and deal.
Purpose: Volume analysis across portfolios and locations.
Grain: period + deal_attribute + location
| Column | Type | Description |
|---|---|---|
dim_deal_attribute_id |
INTEGER | FK to dim_deal_attribute |
dim_delivery_location_id |
INTEGER | FK to dim_delivery_location |
start_time_utc |
TIMESTAMP | Period start (UTC) |
end_time_utc |
TIMESTAMP | Period end (UTC) |
volume_mwh |
NUMBER | Volume in MWh |
fct_volume_daily¶
Daily aggregated volumes.
Purpose: Daily volume summaries for reporting.
Grain: date + deal_attribute
| Column | Type | Description |
|---|---|---|
business_date_local |
DATE | Business date |
dim_deal_attribute_id |
INTEGER | FK to dim_deal_attribute |
volume_mwh |
NUMBER | Daily volume |
minutes_in_period |
INTEGER | Minutes included |
fct_schedule¶
Operational delivery schedules.
Purpose: Detailed schedule data for operations and nominations.
Grain: period + deal + version
| Column | Type | Description |
|---|---|---|
dim_deal_attribute_id |
INTEGER | FK to dim_deal_attribute |
dim_point_of_delivery_location_id |
INTEGER | FK to POD location |
dim_point_of_receipt_location_id |
INTEGER | FK to POR location |
start_time_utc |
TIMESTAMP | Period start (UTC) |
end_time_utc |
TIMESTAMP | Period end (UTC) |
start_time_local |
TIMESTAMP | Period start (local) |
end_time_local |
TIMESTAMP | Period end (local) |
volume_abs |
NUMBER | Absolute volume |
is_best_available |
BOOLEAN | Best available flag |
deal_id |
INTEGER | Deal identifier |
tran_id |
INTEGER | Transaction identifier |
Important: Always filter WHERE is_best_available = TRUE for operational reporting.
Example Query:
SELECT
DATE(f.start_time_local) AS delivery_date,
d.portfolio,
dl.location_name,
SUM(f.volume_abs) AS total_volume
FROM fct_schedule f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_point_of_delivery_location dl ON f.dim_point_of_delivery_location_id = dl.dim_point_of_delivery_location_id
WHERE f.start_time_utc >= CURRENT_DATE
AND f.start_time_utc < CURRENT_DATE + 7
AND f.is_best_available = TRUE
GROUP BY 1, 2, 3
ORDER BY delivery_date, d.portfolio
Risk Models¶
fct_delta¶
Delta exposure by index and delivery period.
Purpose: Analyze price sensitivity across indices and portfolios.
Grain: metric_date + index + deal + delivery_month
| Column | Type | Description |
|---|---|---|
metric_date |
DATE | Valuation date |
dim_index_attribute_id |
INTEGER | FK to dim_index_attribute |
dim_deal_attribute_id |
INTEGER | FK to dim_deal_attribute |
delivery_month |
DATE | Delivery period |
delta_mwh |
NUMBER | Delta in MWh |
gamma |
NUMBER | Gamma (second derivative) |
Example Query:
SELECT
ia.index_name,
d.portfolio,
SUM(f.delta_mwh) AS total_delta
FROM fct_delta f
JOIN dim_index_attribute ia ON f.dim_index_attribute_id = ia.dim_index_attribute_id
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
GROUP BY ia.index_name, d.portfolio
ORDER BY ABS(total_delta) DESC
fct_var_pfolio¶
Value at Risk by portfolio.
Purpose: Portfolio-level risk metrics.
Grain: metric_date + portfolio
| Column | Type | Description |
|---|---|---|
metric_date |
DATE | Valuation date |
dim_portfolio_id |
INTEGER | FK to dim_portfolio |
var_95 |
NUMBER | 95% confidence VaR |
var_99 |
NUMBER | 99% confidence VaR |
Deal Models¶
fct_deal¶
Current deal inventory.
Purpose: Active deal lookup and analysis.
Grain: deal
| Column | Type | Description |
|---|---|---|
dim_deal_attribute_id |
INTEGER | FK to dim_deal_attribute |
dim_tran_status_id |
INTEGER | FK to dim_tran_status |
dim_party_external_bu_le_attribute_id |
INTEGER | FK to dim_party_external |
dim_trade_date_id |
INTEGER | FK to dim_trade_date |
deal_id |
INTEGER | Deal identifier |
tran_id |
INTEGER | Transaction identifier |
tran_version |
INTEGER | Amendment version |
fct_deal_side¶
Deal side details.
Purpose: Multi-leg deal analysis.
Grain: deal + side
| Column | Type | Description |
|---|---|---|
deal_id |
INTEGER | Deal identifier |
param_seq_id |
INTEGER | Side identifier |
dim_deal_sub_attribute_id |
INTEGER | FK to dim_deal_sub_attribute |
Dimension Tables¶
dim_deal_attribute¶
Core deal attributes.
| Column | Type | Description |
|---|---|---|
dim_deal_attribute_id |
INTEGER | Surrogate key |
deal_id |
INTEGER | Deal identifier |
portfolio |
VARCHAR | Portfolio name |
instrument |
VARCHAR | Product type |
buy_sell |
VARCHAR | Direction |
commodity |
VARCHAR | Commodity type |
market |
VARCHAR | Market classification |
tran_status |
VARCHAR | Transaction status |
dim_party_internal¶
Internal parties (business units and legal entities).
| Column | Type | Description |
|---|---|---|
dim_party_internal_bu_le_attribute_id |
INTEGER | Surrogate key |
bunit_id |
INTEGER | Business unit ID |
bunit_name |
VARCHAR | Business unit name |
lentity_id |
INTEGER | Legal entity ID |
lentity_name |
VARCHAR | Legal entity name |
dim_party_external¶
External counterparties.
| Column | Type | Description |
|---|---|---|
dim_party_external_bu_le_attribute_id |
INTEGER | Surrogate key |
bunit_id |
INTEGER | Business unit ID |
bunit_name |
VARCHAR | Business unit name |
lentity_id |
INTEGER | Legal entity ID |
lentity_name |
VARCHAR | Legal entity name |
dim_tran_status¶
Transaction status values.
| Column | Type | Description |
|---|---|---|
dim_tran_status_id |
INTEGER | Surrogate key |
tran_status |
VARCHAR | Status value |
dim_delivery_location¶
Delivery locations.
| Column | Type | Description |
|---|---|---|
dim_delivery_location_id |
INTEGER | Surrogate key |
location_name |
VARCHAR | Location name |
region |
VARCHAR | Geographic region |
pipeline |
VARCHAR | Gas pipeline |
control_area |
VARCHAR | Power control area |
dim_index_attribute¶
Price indices.
| Column | Type | Description |
|---|---|---|
dim_index_attribute_id |
INTEGER | Surrogate key |
index_name |
VARCHAR | Index name |
gridpoint |
VARCHAR | Gridpoint identifier |
Related Documentation¶
- Credit Exposure Models - EWE credit models
- Clearing Statements - Clearing bank models
- Power Volumes - Power trading models
- Query Patterns - SQL examples