4. 维度建模实战：采购日期维度、供应商维度、物料维度、采购组织维度的星型模型设计

在采购支出数据仓库中，星型模型是最常用的维度建模方法。它以事实表为中心，周围环绕多个维度表，形成星状结构。本章将围绕采购支出分析的核心需求，详细设计采购日期维度、供应商维度、物料维度和采购组织维度，并构建完整的星型模型。

4.1 星型模型总体架构

采购支出星型模型以采购支出事实表为中心，连接四个核心维度表。事实表存储可量化的采购指标（如金额、数量、单价），维度表提供描述性属性（如日期、供应商名称、物料分类）。

4.2 采购日期维度设计

日期维度是数据仓库中最通用的维度之一。在采购场景中，日期维度支持按年、季度、月、周、日进行采购趋势分析。

4.3 供应商维度设计

供应商维度是采购分析的核心维度之一，支持供应商绩效评估、支出集中度分析、风险监控等场景。

4.4 物料维度设计

物料维度描述采购物料的属性，支持按物料类别、规格、单位等维度进行支出分析。

4.5 采购组织维度设计

采购组织维度描述企业内部采购管理架构，支持按公司、部门、采购组进行支出分摊分析。

4.6 采购支出事实表设计

事实表是星型模型的核心，记录每笔采购支出的度量值，并通过外键关联四个维度表。

4.7 星型模型SQL建表示例

-- 日期维度表
CREATE TABLE dim_date (
    date_sk INT PRIMARY KEY,
    full_date DATE NOT NULL,
    year INT NOT NULL,
    quarter INT NOT NULL,
    month INT NOT NULL,
    month_name VARCHAR(10),
    week_of_year INT,
    day_of_week INT,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN DEFAULT FALSE
);

-- 供应商维度表
CREATE TABLE dim_supplier (
    supplier_sk INT PRIMARY KEY,
    supplier_code VARCHAR(20) NOT NULL,
    supplier_name VARCHAR(100) NOT NULL,
    region VARCHAR(50),
    country VARCHAR(50),
    supplier_tier VARCHAR(10),
    risk_level VARCHAR(10),
    payment_terms VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    effective_date DATE NOT NULL,
    expiry_date DATE NOT NULL DEFAULT '9999-12-31'
);

-- 物料维度表
CREATE TABLE dim_material (
    material_sk INT PRIMARY KEY,
    material_code VARCHAR(20) NOT NULL,
    material_name VARCHAR(100) NOT NULL,
    category_l1 VARCHAR(50),
    category_l2 VARCHAR(50),
    category_l3 VARCHAR(50),
    unit VARCHAR(10),
    specification VARCHAR(200),
    is_procurable BOOLEAN DEFAULT TRUE
);

-- 采购组织维度表
CREATE TABLE dim_organization (
    org_sk INT PRIMARY KEY,
    org_code VARCHAR(20) NOT NULL,
    org_name VARCHAR(100) NOT NULL,
    company VARCHAR(50),
    department VARCHAR(50),
    cost_center VARCHAR(20),
    region VARCHAR(50),
    org_level INT,
    parent_org_sk INT
);

-- 采购支出事实表
CREATE TABLE fact_purchase_expense (
    expense_sk BIGINT PRIMARY KEY,
    date_sk INT NOT NULL,
    supplier_sk INT NOT NULL,
    material_sk INT NOT NULL,
    org_sk INT NOT NULL,
    po_number VARCHAR(20),
    quantity DECIMAL(18,4) NOT NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    total_amount DECIMAL(18,2) NOT NULL,
    tax_amount DECIMAL(18,2),
    total_with_tax DECIMAL(18,2),
    currency VARCHAR(3) DEFAULT 'CNY',
    exchange_rate DECIMAL(18,6) DEFAULT 1.0,
    FOREIGN KEY (date_sk) REFERENCES dim_date(date_sk),
    FOREIGN KEY (supplier_sk) REFERENCES dim_supplier(supplier_sk),
    FOREIGN KEY (material_sk) REFERENCES dim_material(material_sk),
    FOREIGN KEY (org_sk) REFERENCES dim_organization(org_sk)
);

4.8 维度建模最佳实践与避坑指南

4.9 星型模型查询示例

以下SQL展示如何通过星型模型进行采购支出分析：

-- 查询2025年第一季度各供应商的采购总额
SELECT
    ds.supplier_name,
    ds.region,
    SUM(fpe.total_amount) AS total_expense
FROM fact_purchase_expense fpe
JOIN dim_date dd ON fpe.date_sk = dd.date_sk
JOIN dim_supplier ds ON fpe.supplier_sk = ds.supplier_sk
WHERE dd.year = 2025
  AND dd.quarter = 1
  AND ds.is_active = TRUE
GROUP BY ds.supplier_name, ds.region
ORDER BY total_expense DESC;

-- 查询各物料类别在华东区的采购数量与金额
SELECT
    dm.category_l1,
    dm.category_l2,
    SUM(fpe.quantity) AS total_qty,
    SUM(fpe.total_amount) AS total_amount
FROM fact_purchase_expense fpe
JOIN dim_material dm ON fpe.material_sk = dm.material_sk
JOIN dim_organization dor ON fpe.org_sk = dor.org_sk
WHERE dor.region = '华东'
GROUP BY dm.category_l1, dm.category_l2
ORDER BY total_amount DESC;