Skip to content

For Business Analysts

Welcome to EWE Numera! This guide helps you explore energy trading analytics data for P&L reporting, volume analysis, credit exposure management, and clearing bank reconciliation.

Start Here

  1. Business Glossary - Understand key terms (deals, P&L, volumes, counterparties, credit exposure)
  2. Model Reference - Data dictionary with all customer-facing tables and columns
  3. Dimensional Model - See how data is organized in the star schema
  4. Query Patterns - Copy-paste SQL examples for common analyses

What Data is Available?

The star schema provides dimensions and fact tables covering:

Domain What You Can Analyze
P&L Daily MTM, realized/unrealized P&L by portfolio, counterparty, delivery month
Volumes Scheduled deliveries, buy/sell positions, 15-minute power profiles
Credit Exposure Current exposure, potential exposure, settlement exposure by counterparty
Clearing Statements ABN AMRO and BNP Paribas positions, margins, settlements
Deals Active trades, counterparty exposure, deal lifecycle
Risk Delta exposure by index, VaR by portfolio, stress tests
Prices Historical index prices, settlement prices

Common Tasks

Quick-reference SQL for frequent business analysis. Copy and modify these queries for your needs.

Daily Volumes by Price Type

SELECT
    f.business_date_local AS delivery_date,
    dp.price_type,
    dp.commodity,
    SUM(f.volume) AS total_volume
FROM fct_volume_daily f
JOIN dim_deal_physical dp ON f.dim_deal_physical_id = dp.dim_deal_physical_id
WHERE f.business_date_local >= '2024-01-01'
  AND f.business_date_local < '2024-02-01'
  AND dp.dim_deal_physical_id != 0
GROUP BY f.business_date_local, dp.price_type, dp.commodity
ORDER BY delivery_date, dp.price_type

Fixed vs Floating Exposure by Portfolio

SELECT
    d.portfolio,
    dp.price_type,
    SUM(CASE WHEN d.buy_sell = 'Buy' THEN f.volume ELSE 0 END) AS buy_volume,
    SUM(CASE WHEN d.buy_sell = 'Sell' THEN f.volume ELSE 0 END) AS sell_volume,
    SUM(f.volume) AS net_volume
FROM fct_volume_daily f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp ON f.dim_deal_physical_id = dp.dim_deal_physical_id
WHERE f.business_date_local >= CURRENT_DATE
  AND f.business_date_local < CURRENT_DATE + 30
  AND dp.dim_deal_physical_id != 0
GROUP BY d.portfolio, dp.price_type
ORDER BY d.portfolio, dp.price_type

Exposure to a Specific Index (e.g., TTF)

SELECT
    d.portfolio,
    d.buy_sell,
    dp.proj_index,
    SUM(f.volume) AS total_volume,
    COUNT(DISTINCT f.deal_id) AS deal_count
FROM fct_volume_daily f
JOIN dim_deal_attribute d ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
JOIN dim_deal_physical dp ON f.dim_deal_physical_id = dp.dim_deal_physical_id
WHERE dp.proj_index LIKE '%TTF%'  -- Replace with your index
  AND f.business_date_local >= CURRENT_DATE
  AND dp.dim_deal_physical_id != 0
GROUP BY d.portfolio, d.buy_sell, dp.proj_index
ORDER BY d.portfolio, d.buy_sell

Get Pricing Formula Details for a Deal

SELECT
    deal_id,
    side_id,
    pricing_formula,
    price,
    index_percent,
    index_spread,
    proj_index_name,
    ref_source,
    start_date,
    end_date
FROM fct_deal_commodity_pricing
WHERE deal_id = 12345  -- Replace with your deal ID
ORDER BY side_id, start_date

More Examples

I want to... Go to...
See P&L by portfolio or counterparty Query Patterns - P&L Analysis
Analyze scheduled volumes by location Query Patterns - Volume Analysis
View credit exposure by counterparty Query Patterns - Credit Exposure
Check clearing bank positions Query Patterns - Clearing Statements
Analyze 15-minute power volumes Query Patterns - Power Volumes
Find deals by status or counterparty Query Patterns - Deal Analysis
View delta exposure by index Query Patterns - Risk Analysis
Look up historical prices Query Patterns - Price Data
All price type and commodity pricing queries Price Types & Pricing Guide

Key Concepts

Star Schema Basics

  • Fact tables (fct_*) contain measures: P&L amounts, volumes, exposures, counts
  • Dimension tables (dim_*) provide context: portfolios, counterparties, locations, dates
  • Join facts to dimensions using surrogate keys (e.g., dim_deal_attribute_id)

Important Filters

Always include these filters for accurate business reporting:

-- Only active deals
WHERE tran_status IN ('Validated', 'Matured', 'Amended')

-- Exclude placeholder/unknown rows
WHERE dim_deal_attribute_id != 0

-- Use best available schedule data
WHERE is_best_available = TRUE

Understanding Placeholders

Dimension ID = 0 represents "Unknown" or "Not Applicable". Exclude these when you need complete data:

-- Exclude deals with unknown counterparty
WHERE dim_party_external_bu_le_attribute_id != 0

Credit Exposure Concepts

EWE uses Endur credit risk management to calculate exposures:

Term Description
Current Exposure Mark-to-market exposure at present date
Performance Exposure Expected exposure based on deal performance
Potential Exposure (PEX) Worst-case future exposure
Settlement Exposure Exposure from potential settlement failures
PEX Factor Commodity-specific multiplier for exposure calculation

See Credit Exposure Concepts for detailed explanations.

Clearing Bank Data

EWE maintains accounts with two clearing banks:

  • ABN AMRO: Daily cash statements, transaction details, open positions
  • BNP Paribas: Journal entries, margin calls, purchases/sales, trades

Both banks' data is consolidated in fct_clearing_statement_open_position with standardized column names.

EWE-Specific Models

In addition to the core Numera models, EWE has these customer-specific models:

Model Purpose
fct_credit_exposure Daily credit exposure metrics by counterparty
fct_credit_exposure_month Monthly credit exposure projections
fct_credit_pex_factor PEX factors by commodity
fct_clearing_statement_open_position Unified clearing bank positions
fct_ewe_volume_pwr_by_business_case 15-minute power volumes by business case
dim_party_agreement Party agreement attributes and netting terms

Connecting to the Data

From BI Tools (Power BI, Tableau)

Connect to Snowflake using:

  • Database: NUMERA_EWE_PROD_CORE
  • Schema: NUMERA_DIM_MODEL
  • Role: NUMERA_EWE_PROD_REPORTER
  • Warehouse: NUMERA_PROD_REPORTING

From Python

See Connecting to Data for Python connection setup and examples.

Getting Help

For the full data model reference, see Dimensional Model.