Procurement Spend Analytics data model
Kimball warehouse for procurement: a transaction-grain PO-line fact, a goods-receipt fact (OTIF and quality measures), and an atomic spend fact (savings and spend-under-management) over conformed supplier/material/category/buyer/cost-center/contract dimensions. Supplier tax IDs are masked.
Source-to-pay procurement and spend analytics covering purchase-order commitments, goods receipts, and atomic invoiced spend across a multi-currency, multi-entity supply base. A Kimball star schema with a conformed SCD2 supplier dimension and material, category, buyer, cost-center, and contract dimensions supporting spend-under-management, maverick-spend, contract-compliance, OTIF delivery, and supplier-quality analysis. Supplier tax IDs are classified restricted and masked. 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
- 118
- columns
- 20
- 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
Three transaction-grain facts for the procurement processes
Purchasing (commitment), receiving (fulfilment), and spend (realization) are distinct business processes at different atomic grains; Kimball recommends one fact per process so each can be analyzed and drilled-across via conformed dimensions.
Dedicated atomic spend fact for spend-under-management
Procurement KPIs (addressable spend, savings, maverick rate) require the lowest realized-spend atom; modeling spend separately from PO commitments avoids double-counting and supports non-PO spend that never had a purchase order.
SCD2 on supplier and material dimensions
Supplier tier, risk rating, and material commodity classification change over time; type-2 history preserves the as-was attributes so spend can be re-aggregated under the classification in force when the transaction occurred.
Contract dimension to measure compliance
Linking each PO line and spend row to a contract lets the model distinguish on-contract from maverick spend, a primary category-management lever, using a conformed dimension rather than a fact attribute.
Tables in the Procurement Spend 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 (3)
fct_purchase_order_line
Grain: one row per purchase-order line
Transaction-grain PO line fact — the atomic commitment of procurement spend.
| Column | Type | Keys |
|---|---|---|
| purchase_order_line_key | bigint | PK |
| date_key | bigint | |
| supplier_key | bigint | |
| material_key | bigint | |
| category_key | bigint | |
| buyer_key | bigint | |
| cost_center_key | bigint | |
| contract_key | bigint | |
| currency_key | bigint | |
| need_by_date_key | bigint | |
| po_number | varchar | |
| po_line_number | integer | |
| ordered_quantity | decimal | |
| unit_price | decimal | |
| line_amount | decimal | |
| tax_amount | decimal | |
| exchange_rate | decimal | |
| is_contract_backed | boolean | |
| is_maverick | boolean | |
| po_line_status | varchar |
fct_goods_receipt
Grain: one row per goods-receipt line
Transaction-grain receipt fact supporting OTIF and supplier-quality analysis.
| Column | Type | Keys |
|---|---|---|
| goods_receipt_key | bigint | PK |
| date_key | bigint | |
| supplier_key | bigint | |
| material_key | bigint | |
| cost_center_key | bigint | |
| po_number | varchar | |
| po_line_number | integer | |
| receipt_number | varchar | |
| received_quantity | decimal | |
| accepted_quantity | decimal | |
| rejected_quantity | decimal | |
| received_value | decimal | |
| days_late | integer | |
| is_on_time | boolean | |
| is_in_full | boolean | |
| quality_status | varchar |
fct_spend
Grain: one row per atomic spend transaction (invoiced/settled spend)
Transaction-grain spend fact — the unit of spend-under-management analysis.
| Column | Type | Keys |
|---|---|---|
| spend_key | bigint | PK |
| date_key | bigint | |
| supplier_key | bigint | |
| material_key | bigint | |
| category_key | bigint | |
| buyer_key | bigint | |
| cost_center_key | bigint | |
| contract_key | bigint | |
| currency_key | bigint | |
| po_number | varchar | |
| invoice_number | varchar | |
| spend_quantity | decimal | |
| spend_amount | decimal | |
| spend_amount_reporting | decimal | |
| savings_amount | decimal | |
| exchange_rate | decimal | |
| spend_classification | varchar | |
| is_po_backed | boolean | |
| is_under_management | boolean |
Dimension tables (7)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| fiscal_year | smallint | |
| fiscal_period | smallint | |
| is_business_day | boolean | |
| is_period_end | boolean |
dim_supplier
Grain: one row per supplier per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| supplier_key | bigint | PK |
| supplier_id | varchar | BK |
| supplier_name | varchar | |
| tax_id | varchar | PII |
| supplier_tier | varchar | |
| supplier_status | varchar | |
| country | varchar | |
| state | varchar | |
| risk_rating | varchar | |
| quality_rating | decimal | |
| diversity_classification | varchar | |
| is_preferred | boolean | |
| is_single_source | boolean |
dim_material
Grain: one row per material/SKU per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| material_id | varchar | BK |
| material_name | varchar | |
| material_type | varchar | |
| unit_of_measure | varchar | |
| commodity_code | varchar | |
| is_hazardous | boolean | |
| is_active | boolean |
dim_category
Grain: one row per procurement spend category
| Column | Type | Keys |
|---|---|---|
| category_key | bigint | PK |
| category_id | varchar | BK |
| category_name | varchar | |
| spend_type | varchar | |
| parent_category_id | varchar | |
| is_managed | boolean |
dim_buyer
Grain: one row per buyer/purchasing agent
| Column | Type | Keys |
|---|---|---|
| buyer_key | bigint | PK |
| buyer_id | varchar | BK |
| buyer_name | varchar | PII |
| varchar | PII | |
| buying_team | varchar | |
| region | varchar | |
| is_active | boolean |
dim_cost_center
Grain: one row per cost center per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| cost_center_key | bigint | PK |
| cost_center_id | varchar | BK |
| cost_center_name | varchar | |
| cost_center_type | varchar | |
| region | varchar | |
| parent_cost_center_id | varchar |
dim_contract
Grain: one row per supplier contract
| Column | Type | Keys |
|---|---|---|
| contract_key | bigint | PK |
| contract_id | varchar | BK |
| contract_name | varchar | |
| contract_type | varchar | |
| contract_status | varchar | |
| start_date | date | |
| end_date | date | |
| committed_value | decimal | |
| is_auto_renew | boolean |
Lookup tables (1)
dim_currency
Grain: one row per ISO currency
| Column | Type | Keys |
|---|---|---|
| currency_key | bigint | PK |
| currency_code | varchar | BK |
| currency_name | varchar | |
| currency_symbol | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_purchase_order_line.date_key → dim_date (many-to-one)
- fct_purchase_order_line.supplier_key → dim_supplier (many-to-one)
- fct_purchase_order_line.material_key → dim_material (many-to-one)
- fct_purchase_order_line.category_key → dim_category (many-to-one)
- fct_purchase_order_line.buyer_key → dim_buyer (many-to-one)
- fct_purchase_order_line.cost_center_key → dim_cost_center (many-to-one)
- fct_purchase_order_line.contract_key → dim_contract (many-to-one)
- fct_purchase_order_line.currency_key → dim_currency (many-to-one)
- fct_goods_receipt.date_key → dim_date (many-to-one)
- fct_goods_receipt.supplier_key → dim_supplier (many-to-one)
- fct_goods_receipt.material_key → dim_material (many-to-one)
- fct_goods_receipt.cost_center_key → dim_cost_center (many-to-one)
- fct_spend.date_key → dim_date (many-to-one)
- fct_spend.supplier_key → dim_supplier (many-to-one)
- fct_spend.material_key → dim_material (many-to-one)
- fct_spend.category_key → dim_category (many-to-one)
- fct_spend.buyer_key → dim_buyer (many-to-one)
- fct_spend.cost_center_key → dim_cost_center (many-to-one)
- fct_spend.contract_key → dim_contract (many-to-one)
- fct_spend.currency_key → dim_currency (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.