SAP Treasury & Risk Management Analytics data model
Kimball warehouse for SAP TRM Transaction Manager: a flow-grain cash-flow fact (VTBFHAPO — interest/principal/fee flows with due and value dates) for liquidity and maturity-ladder analysis, plus a position-snapshot fact (VTBFHA position valuation per date) carrying book value, market value, NPV, mark-to-market and unrealized P&L for IFRS-9 valuation. Flow ids are degenerate dimensions; the financial-transaction deal dimension is SCD2 to preserve as-was deal terms; counterparty and portfolio dimensions drive exposure reporting and dual local / position-currency measures support SOX- and IFRS-auditable treasury valuation.
SAP Treasury and Risk Management (FIN-FSCM-TRM) Transaction Manager analytics migrated from SAP TRM / S/4HANA into Snowflake. Models the financial-transaction cash flow (VTBFHAPO) at flow grain and the financial-transaction position valuation (VTBFHA) at position-per-snapshot-date grain over conformed financial-transaction (deal, SCD2), product-type, counterparty (business partner), house-bank (FSCM Cash Management), company-code, and portfolio dimensions, with dual local and position-currency measures for liquidity, maturity-ladder, mark-to-market, and exposure reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 9
- tables
- 92
- columns
- 14
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Flow-grain cash-flow fact for liquidity and maturity-ladder reporting
SAP TRM records each financial transaction's cash flows as individual VTBFHAPO rows with due and value dates; declaring fct_transaction_flow at flow grain lets interest/principal/fee amounts roll up by date into liquidity forecasts and maturity ladders without losing flow detail.
Periodic position-snapshot fact for mark-to-market valuation
Position book vs market value, NPV, and unrealized P&L are valuation states as of a date, not events; a periodic snapshot fact at one row per financial transaction per snapshot date is the Kimball pattern for balances/positions and supports time-series mark-to-market analysis.
SCD2 on the financial-transaction (deal) dimension
Deal terms — status, interest rate, maturity, trader, transaction type — change over a transaction's life; type-2 history preserves the as-was deal attributes required for comparable, SOX-auditable treasury and valuation reporting.
Counterparty and portfolio dimensions for exposure analysis
Treasury risk reporting aggregates exposure by counterparty (business partner) and by portfolio / treasury desk; conforming both dimensions across the cash-flow and position facts lets exposure and limit monitoring drill across liquidity and valuation consistently.
Dual local and position-currency measures for IFRS/SOX auditability
TRM amounts exist in both the transaction/position currency and the company-code local currency; carrying both flow_amount_local and flow_amount_position_currency (and the position currency) preserves the translation basis required for IFRS-9 valuation and SOX audit trails without re-deriving FX at query time.
Tables in the SAP Treasury & Risk Management Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (2)
fct_transaction_flow
Grain: one row per financial-transaction cash flow (VTBFHAPO flow grain)
Flow-grain TRM cash-flow fact (VTBFHAPO) for liquidity and maturity-ladder reporting.
| Column | Type | Keys |
|---|---|---|
| transaction_flow_key | bigint | PK |
| flow_due_date_key | bigint | |
| value_date_key | bigint | |
| financial_transaction_key | bigint | |
| product_type_key | bigint | |
| counterparty_key | bigint | |
| house_bank_key | bigint | |
| company_code_key | bigint | |
| portfolio_key | bigint | |
| flow_id | varchar | |
| flow_amount_local | decimal | |
| flow_amount_position_currency | decimal | |
| nominal_amount | decimal | |
| interest_amount | decimal | |
| principal_amount | decimal | |
| fee_amount | decimal | |
| position_currency | varchar | |
| local_currency | varchar | |
| flow_type | varchar | |
| flow_direction | varchar | |
| is_planned | boolean | |
| is_actual | boolean |
fct_position_snapshot
Grain: one row per financial transaction / position per snapshot date (VTBFHA valuation)
Position-per-date snapshot fact (VTBFHA) for mark-to-market valuation and unrealized P&L.
| Column | Type | Keys |
|---|---|---|
| position_snapshot_key | bigint | PK |
| snapshot_date_key | bigint | |
| financial_transaction_key | bigint | |
| product_type_key | bigint | |
| counterparty_key | bigint | |
| company_code_key | bigint | |
| portfolio_key | bigint | |
| book_value | decimal | |
| market_value | decimal | |
| nominal_amount | decimal | |
| accrued_interest | decimal | |
| npv | decimal | |
| mark_to_market_pnl | decimal | |
| unrealized_pnl | decimal | |
| clean_price | decimal | |
| dirty_price | decimal | |
| currency | varchar | |
| valuation_class | varchar | |
| is_hedge | boolean |
Dimension tables (7)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_business_day | boolean | |
| is_period_end | boolean |
dim_financial_transaction
Grain: one row per financial transaction per SCD2 version (VTBFHA deal)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| financial_transaction_key | bigint | PK |
| deal_number | varchar | BK |
| transaction_category | varchar | |
| transaction_type | varchar | |
| contract_date | date | |
| settlement_date | date | |
| maturity_date | date | |
| interest_rate | decimal | |
| deal_status | varchar | |
| trader | varchar | PII |
| is_active | boolean |
dim_product_type
Grain: one row per TRM product type
| Column | Type | Keys |
|---|---|---|
| product_type_key | bigint | PK |
| product_type | varchar | BK |
| description | varchar | |
| instrument_class | varchar | |
| product_category | varchar | |
| is_otc | boolean |
dim_counterparty
Grain: one row per business partner / counterparty
| Column | Type | Keys |
|---|---|---|
| counterparty_key | bigint | PK |
| counterparty | varchar | BK |
| name | varchar | PII |
| country | varchar | |
| counterparty_type | varchar | |
| credit_rating | varchar | |
| is_internal | boolean |
dim_house_bank
Grain: one row per house bank / bank account (FSCM Cash Management)
| Column | Type | Keys |
|---|---|---|
| house_bank_key | bigint | PK |
| house_bank | varchar | BK |
| bank_name | varchar | |
| bank_account_id | varchar | |
| bank_country | varchar | |
| iban_masked | varchar | PII |
| is_main_account | boolean |
dim_company_code
Grain: one row per SAP company code
| Column | Type | Keys |
|---|---|---|
| company_code_key | bigint | PK |
| company_code | varchar | BK |
| company_name | varchar | |
| country | varchar | |
| local_currency | varchar |
dim_portfolio
Grain: one row per treasury portfolio
| Column | Type | Keys |
|---|---|---|
| portfolio_key | bigint | PK |
| portfolio | varchar | BK |
| description | varchar | |
| treasury_desk | varchar | |
| strategy | varchar | |
| risk_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_transaction_flow.flow_due_date_key → dim_date (many-to-one)
- fct_transaction_flow.value_date_key → dim_date (many-to-one)
- fct_transaction_flow.financial_transaction_key → dim_financial_transaction (many-to-one)
- fct_transaction_flow.product_type_key → dim_product_type (many-to-one)
- fct_transaction_flow.counterparty_key → dim_counterparty (many-to-one)
- fct_transaction_flow.house_bank_key → dim_house_bank (many-to-one)
- fct_transaction_flow.company_code_key → dim_company_code (many-to-one)
- fct_transaction_flow.portfolio_key → dim_portfolio (many-to-one)
- fct_position_snapshot.snapshot_date_key → dim_date (many-to-one)
- fct_position_snapshot.financial_transaction_key → dim_financial_transaction (many-to-one)
- fct_position_snapshot.product_type_key → dim_product_type (many-to-one)
- fct_position_snapshot.counterparty_key → dim_counterparty (many-to-one)
- fct_position_snapshot.company_code_key → dim_company_code (many-to-one)
- fct_position_snapshot.portfolio_key → dim_portfolio (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.