Skip to content

Reference: EWE Wrapper Views

Column-level reference for the 9 EWE-specific 15-minute power volume views.

All views in this section:

  • Are views (not tables). Don't materialise them — see Gap-filling.
  • Are gated by the time_series.15m feature flag.
  • Apply tran_status IN ('Validated', 'Matured') upstream.
  • Guarantee 96 rows per (combo, delivery_date_utc). Gap rows return 0 for volume and price columns.
  • Require consumers to filter by delivery_date_utc or begin_utc for partition pruning.

For the generic numera-core equivalents, see Numera-core views. For migration, see How to migrate from EWE views.

Short-term desk (filtered)

fct_ewe_volume_pwr_by_business_case

Fixed-price 15-minute power volumes aggregated by business case and grid for the short-term desk.

Path: dbt/models/dimensional/short_term_desk/fct_ewe_volume_pwr_by_business_case.sql

Grain: business_case + grid + begin_utc

Business rules:

  • tran_status IN ('Validated', 'Matured')
  • external_business_unit IN ('ECC INTD BU', 'NORD POOL AS BU') OR (external_business_unit = 'EWE TR SHORT TERM BU' AND external_portfolio = 'PWR_ST_PF_Netting')

Columns:

Column Type Description
delivery_date_utc DATE Delivery date (UTC). Sourced from dim_period.start_date; always populated.
begin_utc TIMESTAMP_TZ 15-minute period start (UTC)
end_utc TIMESTAMP_TZ 15-minute period end (UTC)
begin_cet TIMESTAMP_TZ 15-minute period start (Europe/Berlin)
end_cet TIMESTAMP_TZ 15-minute period end (Europe/Berlin)
grid VARCHAR Power control area
business_case VARCHAR Trading strategy/desk
volume_buy_mw NUMBER Buy volume (MW). 0 on gap rows.
volume_sell_mw NUMBER Sell volume (MW). 0 on gap rows.
buy_vwap_price_eur_per_mwh NUMBER Volume-weighted average buy price (EUR/MWh). 0 when no buy volume.
sell_vwap_price_eur_per_mwh NUMBER Volume-weighted average sell price (EUR/MWh). 0 when no sell volume.

Tests:

  • unique_combination_of_columns(business_case, grid, begin_utc)
  • period_coverage_96 on combo (business_case, grid)
  • period_continuity on combo (business_case, grid)
  • vwap_implies_volume
  • Range checks on volumes and prices (warn only)

fct_ewe_volume_pwr_by_business_case_deal

Deal-level drill-down of the short-term desk fixed-price view.

Path: dbt/models/dimensional/short_term_desk/fct_ewe_volume_pwr_by_business_case_deal.sql

Grain: business_case + grid + deal_id + dim_deal_attribute_id + begin_utc

Business rules:

  • tran_status IN ('Validated', 'Matured')
  • external_business_unit IN ('ECC INTD BU', 'NORD POOL AS BU')

The BU clause is tighter than the parent view — no portfolio carve-out for EWE TR SHORT TERM BU.

Additional columns (beyond the parent):

Column Type Description
deal_id INTEGER Endur deal identifier
dim_deal_attribute_id INTEGER FK to dim_deal_attribute for portfolio/instrument context

Tests: parent set, with combo extended to (business_case, grid, deal_id, dim_deal_attribute_id).

fct_ewe_volume_pwr_floating_by_business_case

Index-priced (floating) 15-minute power volumes for the short-term desk.

Path: dbt/models/dimensional/short_term_desk/fct_ewe_volume_pwr_floating_by_business_case.sql

Grain: business_case + grid + internal_business_unit + external_business_unit + power_index_name + begin_utc

Business rules:

  • tran_status IN ('Validated', 'Matured')
  • instrument_sub_type = 'PWR Index'

Notice this view filters by instrument type, not BU. Cross-BU floating volumes are in scope as long as the instrument is PWR Index.

Columns (replaces VWAP columns with index price; adds BU + index name):

Column Type Description
delivery_date_utc DATE Delivery date (UTC)
begin_utc / end_utc TIMESTAMP_TZ Period bounds (UTC)
begin_cet / end_cet TIMESTAMP_TZ Period bounds (Europe/Berlin)
grid VARCHAR Power control area
business_case VARCHAR Trading strategy/desk
internal_business_unit VARCHAR Internal BU name
external_business_unit VARCHAR External BU name
power_index_name VARCHAR Index name (e.g. PWR EPEX_DE_INTRADAY 15min)
volume_buy_mw NUMBER Buy volume (MW). 0 on gap rows.
volume_sell_mw NUMBER Sell volume (MW). 0 on gap rows.
power_index_price_eur_per_mwh NUMBER Index price (EUR/MWh): the index's published 15-minute price for the period. For a monthly index, the month's value in every period of the month. 0 on gap rows and where the index has no price for that delivery day.

Index price:

  • The price comes from the index's own reference source, which is set on the index definition in Endur. Each index has one reference source, so the price is the same for every row on that index and period.
  • The price is looked up from core_endur_index_pricing_15m_daily when you query, by index and delivery day. It therefore always shows the current price that Numera holds for that index and delivery day. When an index price is revised, the new value appears the next time you query.
  • The price is a single value per index and period. It is not summed or weighted across the deals or business units in the row.
  • Some indices have no 15-minute prices, only monthly values. For example, the monthly market-value indices PWR_MMW_Wind_OnShore_DE.EUR, PWR_MMW_Solar_DE.EUR and PWR_MMW_Wind_OffShore_DE.EUR have none. Rows on these indices show the month's value in every period: the published value once it is out, Endur's forward value before that. See How to query floating index volumes.

Tests: unique_combination_of_columns and period_coverage_96 / period_continuity on the full 5-column combo. vwap_implies_volume does not apply (no VWAP).

fct_ewe_volume_pwr_floating_by_business_case_deal

Deal-level drill-down of the floating view.

Path: dbt/models/dimensional/short_term_desk/fct_ewe_volume_pwr_floating_by_business_case_deal.sql

Grain: parent grain + deal_id + dim_deal_attribute_id.

Business rules: same as parent.

Additional columns: deal_id, dim_deal_attribute_id.

Index price: power_index_price_eur_per_mwh follows the same rules as the parent view. It comes from the index's own reference source, it is looked up when you query, and it is 0 on gap rows and where the index has no price (monthly indices carry their monthly value). Every deal on the same index and period shows the same price.

Controlling (unfiltered)

The controlling variants drop the short-term desk's BU/portfolio filter. They still apply tran_status IN ('Validated', 'Matured') upstream.

fct_ewe_volume_pwr_by_business_case_unfiltered

Same column shape as fct_ewe_volume_pwr_by_business_case but with no BU clause — all business units included.

Path: dbt/models/dimensional/controlling/fct_ewe_volume_pwr_by_business_case_unfiltered.sql

Grain: business_case + grid + begin_utc

Use case: cross-BU controlling reports where the short-term desk's BU lens would hide relevant activity.

Note: numeric columns are cast to FLOAT.

fct_ewe_volume_pwr_by_business_case_deal_unfiltered

Deal-level variant of *_unfiltered. Adds deal_id.

Path: dbt/models/dimensional/controlling/fct_ewe_volume_pwr_by_business_case_deal_unfiltered.sql

Grain: business_case + grid + deal_id + begin_utc

fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered

Like *_unfiltered but with internal and external BU breakouts surfaced as columns. Use when controlling needs a BU-level view in a single query.

Path: dbt/models/dimensional/controlling/fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered.sql

Grain: business_case + grid + internal_business_unit + external_business_unit + begin_utc

Additional columns:

Column Type Description
internal_business_unit VARCHAR Internal BU name from sat_party_internal_bu_le_attribute
external_business_unit VARCHAR External BU name from sat_party_external_bu_le_attribute

Quarterly controlling

The quarterly variants are thin select * views over their core_*__real intermediate. They retain dim_deal_attribute_id so consumers can join dim_deal_attribute for portfolio/instrument filtering — useful for the controlling quarterly cycle.

fct_volume_pwr_quarter_fixed_price

Path: dbt/models/dimensional/controlling/fct_volume_pwr_quarter_fixed_price.sql

Grain: business_case + grid + dim_deal_attribute_id + begin_utc

Business rules: tran_status IN ('Validated', 'Matured'). No BU filter.

Columns: same shape as the short-term desk fixed-price views plus dim_deal_attribute_id.

fct_volume_pwr_quarter_fixed_price_deal

Path: dbt/models/dimensional/controlling/fct_volume_pwr_quarter_fixed_price_deal.sql

Grain: parent grain + deal_id.

Test coverage at a glance

View unique_combo period_coverage_96 period_continuity vwap_implies_volume
fct_ewe_volume_pwr_by_business_case ✓ ✓ ✓ ✓
fct_ewe_volume_pwr_by_business_case_deal ✓ ✓ ✓ ✓
fct_ewe_volume_pwr_floating_by_business_case ✓ ✓ ✓ —
fct_ewe_volume_pwr_floating_by_business_case_deal ✓ ✓ ✓ —
fct_ewe_volume_pwr_by_business_case_unfiltered not_null only — — —
fct_ewe_volume_pwr_by_business_case_deal_unfiltered not_null only — — —
fct_ewe_volume_pwr_by_business_case_with_bu_unfiltered not_null only — — —
fct_volume_pwr_quarter_fixed_price not_null only — — —
fct_volume_pwr_quarter_fixed_price_deal not_null only — — —

The controlling and quarterly variants share the upstream pipeline with the fully-tested short-term desk wrappers, so they inherit the gap-fill correctness from the same intermediates.