Expense Management Analytics data model
Kimball warehouse for expense management: a transaction-grain expense-line fact (category, policy-limit, and violation measures) plus an accumulating-snapshot expense-report fact (submitted → approved → reimbursed roles, cycle-time and lag measures) over conformed employee/category/cost-center/merchant/project/payment-method dimensions. Employee PII is masked.
Travel-and-expense management analytics covering transaction-grain expense line items and the expense-report lifecycle from submission through approval to reimbursement across a multi-currency workforce. A Kimball star schema with a conformed SCD2 employee dimension and expense-category, cost-center, merchant, project, and payment-method dimensions supporting spend-by-category, policy-compliance, approval-cycle-time, and reimbursement-lag analysis. Employee identity and contact data are classified 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.
- 9
- tables
- 97
- columns
- 11
- relationships
- 4
- 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 expense-line fact at the lowest atom
The line item (one charge, one category, one merchant) is the atomic grain of T&E; Kimball recommends declaring facts at the lowest grain so category spend, policy limits, and merchant mix can be analyzed and rolled up without losing detail.
Accumulating snapshot for the expense-report lifecycle
A report progresses through submitted, approved, and reimbursed milestones on the same row; Kimball's accumulating-snapshot pattern with multiple date-role keys yields approval-cycle-time and reimbursement-lag measures directly.
Header report fact alongside the line fact
Some measures (cycle times, report-level approval status, total reimbursed) live naturally at report grain; pairing a header accumulating-snapshot fact with the line transaction fact avoids duplicating header measures onto every line.
SCD2 on the employee dimension
Employee grade, department, and cost-center assignment change over time and drive policy limits; type-2 history preserves the as-was profile so historical policy-compliance is evaluated against the limits in force at the time the expense was incurred.
Tables in the Expense 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_expense_line
Grain: one row per expense line item
Transaction-grain expense fact — the atomic line item for category and policy analysis.
| Column | Type | Keys |
|---|---|---|
| expense_line_key | bigint | PK |
| date_key | bigint | |
| employee_key | bigint | |
| expense_category_key | bigint | |
| cost_center_key | bigint | |
| merchant_key | bigint | |
| project_key | bigint | |
| payment_method_key | bigint | |
| report_number | varchar | |
| expense_line_number | integer | |
| currency | varchar | |
| expense_amount | decimal | |
| amount_reporting | decimal | |
| reimbursable_amount | decimal | |
| tax_amount | decimal | |
| policy_limit | decimal | |
| over_limit_amount | decimal | |
| exchange_rate | decimal | |
| is_billable | boolean | |
| is_policy_violation | boolean | |
| has_receipt | boolean |
fct_expense_report
Grain: one row per expense report (accumulating snapshot: submitted → approved → reimbursed)
Accumulating snapshot tracking the report lifecycle for cycle-time and reimbursement-lag.
| Column | Type | Keys |
|---|---|---|
| expense_report_key | bigint | PK |
| employee_key | bigint | |
| cost_center_key | bigint | |
| payment_method_key | bigint | |
| submitted_date_key | bigint | |
| approved_date_key | bigint | |
| reimbursed_date_key | bigint | |
| report_number | varchar | |
| currency | varchar | |
| line_count | integer | |
| total_amount | decimal | |
| reimbursable_amount | decimal | |
| reimbursed_amount | decimal | |
| rejected_amount | decimal | |
| over_limit_amount | decimal | |
| days_to_approve | integer | |
| days_to_reimburse | integer | |
| policy_violation_count | integer | |
| is_approved | boolean | |
| is_reimbursed | boolean | |
| report_status | varchar |
Dimension tables (6)
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_employee
Grain: one row per employee per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| employee_key | bigint | PK |
| employee_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| job_title | varchar | |
| department | varchar | |
| employee_grade | varchar | |
| manager_name | varchar | PII |
| region | varchar | |
| home_country | varchar | |
| is_active | boolean |
dim_expense_category
Grain: one row per expense category
| Column | Type | Keys |
|---|---|---|
| expense_category_key | bigint | PK |
| category_id | varchar | BK |
| category_name | varchar | |
| category_group | varchar | |
| parent_category_id | varchar | |
| is_reimbursable | boolean | |
| is_billable_default | boolean | |
| policy_limit | decimal |
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_merchant
Grain: one row per merchant
| Column | Type | Keys |
|---|---|---|
| merchant_key | bigint | PK |
| merchant_id | varchar | BK |
| merchant_name | varchar | |
| mcc | varchar | |
| merchant_category | varchar | |
| country | varchar | |
| is_preferred_vendor | boolean |
dim_project
Grain: one row per project
| Column | Type | Keys |
|---|---|---|
| project_key | bigint | PK |
| project_id | varchar | BK |
| project_name | varchar | |
| project_status | varchar | |
| client_name | varchar | |
| is_billable | boolean | |
| start_date | date | |
| end_date | date |
Lookup tables (1)
dim_payment_method
Grain: one row per expense payment method
| Column | Type | Keys |
|---|---|---|
| payment_method_key | bigint | PK |
| method_code | varchar | BK |
| method_name | varchar | |
| is_corporate_liability | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_expense_line.date_key → dim_date (many-to-one)
- fct_expense_line.employee_key → dim_employee (many-to-one)
- fct_expense_line.expense_category_key → dim_expense_category (many-to-one)
- fct_expense_line.cost_center_key → dim_cost_center (many-to-one)
- fct_expense_line.merchant_key → dim_merchant (many-to-one)
- fct_expense_line.project_key → dim_project (many-to-one)
- fct_expense_line.payment_method_key → dim_payment_method (many-to-one)
- fct_expense_report.submitted_date_key → dim_date (many-to-one)
- fct_expense_report.employee_key → dim_employee (many-to-one)
- fct_expense_report.cost_center_key → dim_cost_center (many-to-one)
- fct_expense_report.payment_method_key → dim_payment_method (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.