Skip to content

How to Join Dimensional Views for Filtered Queries

The generic numera-core 15m views expose surrogate-key columns (dim_deal_attribute_id, dim_party_*_id, dim_point_of_*_location_id, dim_tran_info_id, dim_deal_financial_id) so you can join to dimensional context — portfolio, counterparty, instrument, location.

This guide is a cookbook of filter patterns. Each works on fct_volume_pwr_15m (and the _deal variant); floating-volume patterns work on fct_volume_pwr_floating_15m analogously.

Always filter by date

Every example assumes a bounded WHERE f.delivery_date_utc ... clause. Without one, the view computes the dense gap-filled grid over the entire dim_period range — slow. See Filter and aggregate efficiently.

Filter by portfolio

portfolio is on dim_deal_attribute.

select
    f.delivery_date_utc,
    f.begin_utc,
    f.business_case,
    f.grid,
    sum(f.volume_buy_mw)  as volume_buy_mw,
    sum(f.volume_sell_mw) as volume_sell_mw
from fct_volume_pwr_15m f
join dim_deal_attribute da
    on f.dim_deal_attribute_id = da.dim_deal_attribute_id
where da.portfolio = 'PWR_ST_PF_Netting'
  and f.delivery_date_utc between '2026-04-01' and '2026-04-30'
group by 1, 2, 3, 4
order by 1, 2, 3, 4

For a list of portfolios:

where da.portfolio in ('PWR_ST_PF_Netting', 'PWR_DA_BOOK')

Filter by instrument

instrument, instrument_sub_type, commodity are also on dim_deal_attribute.

select ...
from fct_volume_pwr_15m f
join dim_deal_attribute da using (dim_deal_attribute_id)
where da.instrument = 'PWR Physical'
  and f.delivery_date_utc between '2026-04-01' and '2026-04-30'

For floating volumes, instrument_sub_type = 'PWR Index' is already filtered upstream — don't re-apply it.

Filter by buy/sell

buy_sell is on dim_deal_attribute and is meaningful only on the _deal variants — the business-case-grain views already sum across both sides into volume_buy_mw / volume_sell_mw.

-- Sell-only deals last week, at deal grain
select
    f.delivery_date_utc, f.deal_id, f.business_case, f.grid,
    sum(f.volume_sell_mw) as volume_sell_mw,
    avg(f.sell_vwap_price_eur_per_mwh) as avg_sell_price
from fct_volume_pwr_15m_deal f
join dim_deal_attribute da using (dim_deal_attribute_id)
where da.buy_sell = 'Sell'
  and f.delivery_date_utc between dateadd('day', -7, current_date) and current_date
group by 1, 2, 3, 4

Don't double-filter on the business-case-grain views

On fct_volume_pwr_15m (no _deal), filtering by da.buy_sell = 'Sell' excludes any (business_case, grid) combination whose contributing deals all happen to be tagged 'Sell' at the deal-attribute level — which is not what most users mean. Stick to deal-grain when filtering on buy/sell, or filter on the volume_*_mw columns directly (e.g. where volume_sell_mw > 0).

External counterparty / legal entity comes from sat_party_external_bu_le_attribute via dim_party_external_bu_le_attribute_id.

select
    f.delivery_date_utc, f.begin_utc, f.business_case, f.grid,
    pe.lentity_name as counterparty,
    sum(f.volume_buy_mw)  as volume_buy_mw,
    sum(f.volume_sell_mw) as volume_sell_mw
from fct_volume_pwr_15m f
join sat_party_external_bu_le_attribute pe
    on f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
where pe.lentity_name = 'EEX AG'
  and f.delivery_date_utc between '2026-04-01' and '2026-04-30'
group by 1, 2, 3, 4, 5

For internal BU / legal entity, use sat_party_internal_bu_le_attribute and the dim_party_internal_bu_le_attribute_id column.

Filter by physical location

For grid-level filtering, just use the grid column. For finer-grained physical location filtering, join dim_point_of_delivery_location or dim_point_of_receipt_location.

-- Buy volumes by point of receipt for TenneT
select
    f.delivery_date_utc,
    f.begin_utc,
    por.location_name as point_of_receipt,
    sum(f.volume_buy_mw) as volume_buy_mw
from fct_volume_pwr_15m f
join dim_point_of_receipt_location por
    on f.dim_point_of_receipt_location_id = por.dim_point_of_receipt_location_id
where por.power_control_area = 'TenneT'
  and f.delivery_date_utc between '2026-04-01' and '2026-04-07'
group by 1, 2, 3

The sell-side equivalent uses dim_point_of_delivery_location_id.

Replicate the EWE BU rule

The four short-term desk EWE wrappers apply this filter at the data layer. To reproduce on the generic view:

with deals_in_scope as (
    select dim_deal_attribute_id
    from dim_deal_attribute
    where 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')
)
select
    f.delivery_date_utc, f.begin_utc, f.business_case, f.grid,
    sum(f.volume_buy_mw)  as volume_buy_mw,
    sum(f.volume_sell_mw) as volume_sell_mw
from fct_volume_pwr_15m f
join deals_in_scope d on f.dim_deal_attribute_id = d.dim_deal_attribute_id
where f.delivery_date_utc between '2026-04-01' and '2026-04-30'
group by 1, 2, 3, 4

The deal-grain wrapper (fct_ewe_volume_pwr_by_business_case_deal) uses a stricter filter — drop the OR ... PWR_ST_PF_Netting clause. See Migration how-to: Pattern B.

Multi-dimension filter

Combine joins as needed. Example: short-term desk activity for portfolio PWR_ST_PF_Netting at deal grain.

select
    f.delivery_date_utc,
    f.deal_id,
    f.business_case,
    f.grid,
    da.portfolio,
    pe.lentity_name as counterparty,
    sum(f.volume_buy_mw)  as volume_buy_mw,
    sum(f.volume_sell_mw) as volume_sell_mw,
    avg(f.buy_vwap_price_eur_per_mwh)  as avg_buy_vwap,
    avg(f.sell_vwap_price_eur_per_mwh) as avg_sell_vwap
from fct_volume_pwr_15m_deal f
join dim_deal_attribute da
    on f.dim_deal_attribute_id = da.dim_deal_attribute_id
left join sat_party_external_bu_le_attribute pe
    on f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
where da.portfolio = 'PWR_ST_PF_Netting'
  and f.delivery_date_utc between '2026-04-01' and '2026-04-30'
group by 1, 2, 3, 4, 5, 6

Pitfalls

Don't filter on tran_status

It's already filtered upstream to Validated / Matured. Adding a tran_status predicate either does nothing or eliminates valid rows depending on how you write it.

Avoid dim_deal_attribute_id = 0

Surrogate key 0 is the SCD "unknown" placeholder. Most queries should exclude it:

where da.dim_deal_attribute_id != 0

This is automatic if you use an INNER JOIN da since the unknown row is typically filtered out by your WHERE clause anyway, but make the rule explicit for LEFT JOINs.

Aggregating VWAP

AVG(buy_vwap_price_eur_per_mwh) over multiple periods double-counts low-volume slots. Re-derive from underlying volumes:

sum(f.volume_buy_mw * f.buy_vwap_price_eur_per_mwh) / nullif(sum(f.volume_buy_mw), 0)

See Concepts: VWAP. Avoid dividing SUM(volume_x_price) by SUM(volume_buy_mw) — that returns one-quarter of the true VWAP because volume_buy_mw = volume_mwh × 4 and the raw volume_mwh is not exposed on the view.