SAP Ariba Spend & Sourcing Analytics data model
Kimball warehouse for SAP Ariba procurement analytics: a transaction-grain spend fact classifies each invoice/PO line to a UNSPSC commodity and category, carrying spend/addressable/off-contract amounts plus on-contract, preferred-supplier, PO-backed, and maverick flags for spend-visibility and contract-compliance reporting. A sourcing-event fact captures RFI/RFP/auction lots with baseline, awarded, identified, and realized savings and bid participation, using two date roles (publish/award) and an awarded-supplier role-play of the supplier dimension. Supplier is SCD2 with diversity and risk; the contract dimension drives off-contract/maverick compliance analysis.
SAP Ariba Spend Analysis and Strategic Sourcing analytics integrated with S/4HANA procurement, modelled in Snowflake. Centralizes spend visibility by supplier, sourcing category, and UNSPSC commodity, exposing addressable spend, contract compliance, maverick (off-contract) spend, and supplier diversity, alongside Ariba Sourcing RFI/RFP/auction events with identified-vs-realized savings. A transaction-grain spend fact (one classified invoice/PO line) and a sourcing-event fact (one awarded lot) share conformed supplier (SCD2), commodity (UNSPSC), category, cost center, company code, purchasing org, contract, and buyer dimensions. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 107
- columns
- 16
- 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
Transaction-grain classified spend fact with compliance flags
Spend analysis requires drill-down from addressable spend to the individual invoice/PO line; declaring fct_spend at the classified transaction-line grain (one line mapped to a commodity) keeps on-contract, preferred-supplier, PO-backed, and maverick flags additive at the lowest level so maverick and contract-compliance spend roll up to any supplier, category, or commodity.
UNSPSC commodity dimension with a classification hierarchy
Ariba Spend Analysis classifies spend to UNSPSC; modelling commodity as a conformed dimension carrying segment/family/class/commodity levels lets addressable spend aggregate along the standard commodity hierarchy independent of the buyer-facing category taxonomy.
Separate sourcing-event fact for identified vs realized savings
Sourcing events award at a different grain (one awarded lot) than spend transactions, so fct_sourcing_event isolates baseline, awarded, identified, and realized savings and bid participation; keeping it separate avoids mixing event-level savings semantics into the transaction-grain spend fact while sharing conformed dimensions.
Supplier modelled SCD2 with diversity and risk attributes
Diversity classification, risk rating, and qualification status change over time and are reported as-was; type-2 history on the supplier dimension preserves the supplier attributes effective when the spend or award occurred, supporting diversity and risk trend reporting.
Contract dimension to measure off-contract / maverick spend
Contract compliance is the core spend-analysis KPI; a conformed contract dimension joined from the spend fact lets off-contract and maverick spend be quantified against committed contract value and validity windows for SOX-relevant procurement controls.
Tables in the SAP Ariba Spend & Sourcing 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_spend
Grain: one row per classified spend transaction line (invoice/PO line mapped to a commodity)
Transaction-grain classified spend fact — addressable, contract-compliance, and maverick analysis.
| Column | Type | Keys |
|---|---|---|
| spend_key | bigint | PK |
| spend_date_key | bigint | |
| supplier_key | bigint | |
| commodity_key | bigint | |
| category_key | bigint | |
| cost_center_key | bigint | |
| company_code_key | bigint | |
| purchasing_org_key | bigint | |
| contract_key | bigint | |
| buyer_key | bigint | |
| spend_amount | decimal | |
| addressable_spend | decimal | |
| off_contract_spend | decimal | |
| tax_amount | decimal | |
| quantity | decimal | |
| unit_price | decimal | |
| currency | varchar | |
| is_on_contract | boolean | |
| is_preferred_supplier | boolean | |
| is_po_backed | boolean | |
| is_maverick | boolean | |
| spend_classification | varchar |
fct_sourcing_event
Grain: one row per sourcing event / awarded lot
Sourcing-event-grain fact — identified vs realized savings and bid participation.
| Column | Type | Keys |
|---|---|---|
| sourcing_event_key | bigint | PK |
| publish_date_key | bigint | |
| award_date_key | bigint | |
| category_key | bigint | |
| commodity_key | bigint | |
| owner_buyer_key | bigint | |
| awarded_supplier_key | bigint | |
| company_code_key | bigint | |
| event_id | varchar | |
| lot_id | varchar | |
| baseline_spend | decimal | |
| awarded_spend | decimal | |
| identified_savings | decimal | |
| realized_savings | decimal | |
| savings_pct | decimal | |
| suppliers_invited | integer | |
| suppliers_responded | integer | |
| lot_count | integer | |
| event_cycle_days | integer | |
| event_type | varchar | |
| event_status | varchar |
Dimension tables (9)
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 | |
| fiscal_period | varchar | |
| is_business_day | boolean |
dim_supplier
Grain: one row per supplier per SCD2 version (Ariba SM vendor / S/4HANA lifnr)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| supplier_key | bigint | PK |
| supplier_id | varchar | BK |
| supplier_name | varchar | PII |
| country | varchar | |
| region | varchar | |
| diversity_classification | varchar | |
| risk_rating | varchar | |
| is_preferred | boolean | |
| is_qualified | boolean | |
| parent_supplier_id | varchar | |
| is_active | boolean |
dim_commodity
Grain: one row per UNSPSC commodity code
| Column | Type | Keys |
|---|---|---|
| commodity_key | bigint | PK |
| commodity_code | varchar | BK |
| commodity_name | varchar | |
| segment | varchar | |
| family | varchar | |
| class_level | varchar | |
| unspsc_level | varchar |
dim_category
Grain: one row per Ariba sourcing/spend category
| Column | Type | Keys |
|---|---|---|
| category_key | bigint | PK |
| category_code | varchar | BK |
| category_name | varchar | |
| category_l1 | varchar | |
| category_l2 | varchar | |
| category_l3 | varchar | |
| category_manager | varchar | PII |
| is_direct | boolean |
dim_cost_center
Grain: one row per cost center
| Column | Type | Keys |
|---|---|---|
| cost_center_key | bigint | PK |
| cost_center | varchar | BK |
| cost_center_name | varchar | |
| profit_center | varchar | |
| department | varchar |
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 | |
| currency | varchar |
dim_purchasing_org
Grain: one row per purchasing organization
| Column | Type | Keys |
|---|---|---|
| purchasing_org_key | bigint | PK |
| purchasing_org | varchar | BK |
| description | varchar | |
| company_code | varchar |
dim_contract
Grain: one row per procurement contract
| Column | Type | Keys |
|---|---|---|
| contract_key | bigint | PK |
| contract_id | varchar | BK |
| supplier_id | varchar | |
| contract_type | varchar | |
| valid_from | date | |
| valid_to | date | |
| committed_value | decimal | |
| is_auto_renew | boolean | |
| contract_status | varchar |
dim_buyer
Grain: one row per buyer / category manager
| Column | Type | Keys |
|---|---|---|
| buyer_key | bigint | PK |
| buyer_id | varchar | BK |
| buyer_name | varchar | PII |
| category_managed | varchar | |
| region | varchar | |
| is_active | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_spend.spend_date_key → dim_date (many-to-one)
- fct_spend.supplier_key → dim_supplier (many-to-one)
- fct_spend.commodity_key → dim_commodity (many-to-one)
- fct_spend.category_key → dim_category (many-to-one)
- fct_spend.cost_center_key → dim_cost_center (many-to-one)
- fct_spend.company_code_key → dim_company_code (many-to-one)
- fct_spend.purchasing_org_key → dim_purchasing_org (many-to-one)
- fct_spend.contract_key → dim_contract (many-to-one)
- fct_spend.buyer_key → dim_buyer (many-to-one)
- fct_sourcing_event.publish_date_key → dim_date (many-to-one)
- fct_sourcing_event.award_date_key → dim_date (many-to-one)
- fct_sourcing_event.category_key → dim_category (many-to-one)
- fct_sourcing_event.commodity_key → dim_commodity (many-to-one)
- fct_sourcing_event.owner_buyer_key → dim_buyer (many-to-one)
- fct_sourcing_event.awarded_supplier_key → dim_supplier (many-to-one)
- fct_sourcing_event.company_code_key → dim_company_code (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.