2、报表数据模型设计：星型模型与雪花模型、事实表与维度表设计、数据粒度概念、在ERP中如何设计订单分析模型

好，咱们进入第二章。这一章可以说是报表引擎的「地基工程」。

我见过太多团队，一上来就写SQL，报表跑得飞快。等数据量上来了，查询慢得像蜗牛，改都没法改。为什么？数据模型没设计好。

说白了，报表好不好用，七成取决于数据模型。今天咱们就把星型模型、雪花模型、事实表、维度表、数据粒度这几个核心概念掰开揉碎讲清楚。

2.1 星型模型 vs 雪花模型：选哪个？

先问个问题：你见过仓库里的货架吗？

星型模型就像一个大货架，中间放核心数据（事实），四周挂着小标签（维度）。雪花模型呢？它把标签又细分了，像树枝一样分叉出去。

核心区别一句话：

  • 星型模型：事实表在中间，维度表直接连在周围。简单、查询快。
  • 雪花模型：维度表继续拆分，形成多层结构。节省存储，但查询慢。

我在项目中遇到过一件事。有个客户做电商报表，用了雪花模型，把「地区」拆成省、市、区三级。结果每次跑全国销售汇总，要关联5张表，查询时间从2秒变成20秒。后来改成星型模型，把省市区合并成一张维度表，查询直接回到2秒。

你想想看，报表引擎最怕什么？怕慢。所以我个人习惯，90%的场景用星型模型。除非你存储空间极度紧张，否则别碰雪花。

我的建议：

ERP报表引擎，优先选星型模型。简单、高效、好维护。雪花模型只在你需要严格规范化、且查询频率不高时考虑。

2.2 事实表与维度表：报表的「骨架」和「血肉」

这两个概念，我换个说法你马上懂。

事实表：记录「发生了什么」。比如一笔订单、一次入库、一次付款。它里面全是数字和ID。

维度表：描述「是谁、在哪、什么时候」。比如客户姓名、产品名称、日期。

举个例子，订单分析模型：

表类型 表名 字段示例
事实表 fact_order order_id, customer_id, product_id, amount, quantity, order_date
维度表 dim_customer customer_id, name, city, level, phone
维度表 dim_product product_id, name, category, price, supplier
维度表 dim_date date_key, year, month, day, quarter, week

嗯，这里要注意：事实表里的ID，一定要和维度表的主键对应。我曾经见过一个项目，事实表用「订单编号」做关联，维度表用「自增ID」，结果两张表永远对不上。排查了三天，最后发现是设计时没统一规范。

避坑指南：

我曾经犯过一个错：在事实表里直接存「客户姓名」而不是「客户ID」。结果客户改名了，历史报表全乱套。记住：事实表只存ID，所有描述信息放维度表。

2.3 数据粒度：你的报表「精细」到什么程度？

数据粒度，说白了就是「每行数据代表什么」。

举个例子：

  • 订单级粒度：一行数据 = 一笔订单。适合看订单总数、总金额。
  • 订单行级粒度：一行数据 = 订单中的一行商品。适合看每个商品的销量。
  • 日汇总粒度：一行数据 = 一天的总销售额。适合看趋势。

我建议你记住这个原则：粒度越细，灵活性越高，但数据量越大。

在ERP中，订单分析模型通常选「订单行级粒度」。为什么？因为ERP需要知道每个订单里每个商品的销售情况。如果你只存订单级，想分析「哪个SKU卖得好」就得重新算，很麻烦。

粒度选择口诀：

看明细，用行级；看趋势，用汇总；看全貌，用订单级。

宁细勿粗，粗了改不了，细了可以聚合。

2.4 在ERP中如何设计订单分析模型

好，理论讲完了，咱们来点实战。这是我在一个中型制造企业ERP项目中的真实设计。

第一步：确定业务过程

订单分析，核心业务过程就是「下单」和「发货」。所以我们设计两张事实表：

  • fact_order：记录下单事件
  • fact_delivery：记录发货事件

第二步：确定粒度

订单表用「订单行级粒度」。每行数据代表一个订单中的一行商品。

第三步：设计维度表

我们需要这些维度：

  • dim_customer：客户信息
  • dim_product：产品信息
  • dim_date：日期信息
  • dim_employee：销售人员
  • dim_warehouse：仓库信息

第四步：建表SQL示例

-- 事实表：订单行级
CREATE TABLE fact_order (
    order_line_id    INT PRIMARY KEY,
    order_id         INT,
    customer_id      INT,
    product_id       INT,
    employee_id      INT,
    warehouse_id     INT,
    order_date_key   INT,
    quantity         DECIMAL(10,2),
    unit_price       DECIMAL(10,2),
    discount         DECIMAL(10,2),
    line_amount      DECIMAL(10,2)
);

-- 维度表：客户
CREATE TABLE dim_customer (
    customer_id      INT PRIMARY KEY,
    customer_name    VARCHAR(100),
    city             VARCHAR(50),
    province         VARCHAR(50),
    customer_level   VARCHAR(20),
    created_date     DATE
);

-- 维度表：产品
CREATE TABLE dim_product (
    product_id       INT PRIMARY KEY,
    product_name     VARCHAR(100),
    category         VARCHAR(50),
    brand            VARCHAR(50),
    unit_price       DECIMAL(10,2)
);

-- 维度表：日期
CREATE TABLE dim_date (
    date_key         INT PRIMARY KEY,
    full_date        DATE,
    year             INT,
    month            INT,
    day              INT,
    quarter          INT,
    week_of_year     INT
);

实战小技巧：

日期维度表我建议一次性生成10年的数据。别问我为什么，我曾经因为日期表没预生成，导致报表跑不出来，被老板骂了一下午。预生成后，查询直接用date_key关联，快得飞起。

第五步：设计汇总表（可选）

如果报表需要频繁查询「每日销售额」，可以建一张汇总表：

CREATE TABLE fact_daily_sales (
    date_key         INT,
    total_amount     DECIMAL(12,2),
    order_count      INT,
    customer_count   INT,
    PRIMARY KEY (date_key)
);

这样，日报查询直接查汇总表，不用每次都扫描几百万行的明细表。

2.5 总结一下

这一章内容不少，我帮你捋一捋：

  • 星型模型：简单快速，首选。
  • 雪花模型：省存储但慢，少用。
  • 事实表：存事件，只放ID和数字。
  • 维度表：存描述，放文本和属性。
  • 数据粒度：宁细勿粗，细了可以聚合。
  • 订单分析模型：订单行级粒度 + 星型模型 + 预生成日期表。

下一章，咱们聊聊ETL流程设计。说白了，就是怎么把ERP里的脏数据洗干净，装进咱们设计好的模型里。到时候见。

公众号:蓝海资料掘金营，微信deep3321

← 上一章 📖 返回目录 下一章 →