Exposure grain — book required; for MTM and VaR reports.
CREATE TABLE curated_fct_murex_position AS
SELECT
p.position_id,
p.book_id,
p.instrument_id,
p.position_date,
p.quantity,
p.market_value
FROM raw_murex_position p
WHERE p.book_id IS NOT NULL;
Curated dim counterparty / trade fact
Counterparty without name/LEI in the default mart; trade without trader cleartext.
CREATE TABLE curated_dim_murex_counterparty AS
SELECT
counterparty_id,
counterparty_type
-- omit lei, name from default analytics dims
FROM raw_murex_counterparty;
CREATE TABLE curated_fct_murex_trade AS
SELECT
t.deal_id,
t.trade_date,
t.value_date,
t.product_type,
t.notional_amount,
t.currency,
t.counterparty_id,
t.book_id,
t.status,
CASE WHEN t.status = 'live' THEN TRUE ELSE FALSE END AS is_live
-- omit trader_id from default analytics facts
FROM raw_murex_trade t
WHERE t.book_id IS NOT NULL;
Curated measure SELECTs
Notional outstanding, daily P&L, unmatched confirmations — adapt period filters.
-- Notional outstanding by book
SELECT
book_id,
SUM(notional_amount) AS notional_outstanding
FROM curated_fct_murex_trade
WHERE is_live = TRUE
GROUP BY book_id;
-- Daily P&L by book
SELECT
book_id,
SUM(pnl_amount) AS daily_pnl
FROM raw_murex_risk_result
WHERE as_of_date = :as_of_date
GROUP BY book_id;
-- Unmatched confirmations (settlement risk)
SELECT COUNT(*) AS unmatched_confirmations
FROM raw_murex_confirmation_meta
WHERE match_status <> 'matched';
Tools
Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.