数据仓库设计：ERP 和电子商务分析的星型架构

您的 ERP 数据库针对交易进行了优化——插入订单、更新库存、处理付款。您的电子商务平台针对提供产品页面和处理结帐进行了优化。两者都没有针对推动业务决策的问题进行优化：哪些产品类别在退货后利润最高？哪些客户群的终生价值不断增长？我们的供应链瓶颈在哪里？

数据仓库可以填补这一空白。星型模式是一种使分析查询快速、直观且可维护的设计模式。

要点

• 星型模式将业务指标（事实）与描述性上下文（维度）分开，使查询直观、快速
• ERP 和电子商务分析通常需要四到六个事实表和八到十二个维度表来涵盖核心业务问题
• ETL管道应使用缓慢变化维度的增量加载来处理历史分析，而无需重新处理所有数据
• 与直接查询规范化操作数据库相比，精心设计的星型模式可将查询复杂性降低 60% 至 80%

---

为什么不直接查询ERP？

在投资单独的数据仓库之前，许多公司尝试对其运营数据库运行分析查询。这因三个原因而失败。

性能。 分析查询扫描数百万行、计算聚合并连接许多表。针对生产数据库运行这些会降低每个用户的 ERP 速度。扫描六个月订单数据的报告可能会锁定表并降低 Shopify 商店的结账性能。

复杂性。 操作数据库是标准化的——旨在最大限度地减少数据冗余。像“按月产品类别划分的总收入”这样的简单问题可能需要连接 Odoo 的 PostgreSQL 数据库中的八个表。在星型模式中，相同的查询连接两个表。

历史记录。 操作系统会覆盖数据。当客户更改地址时，旧地址就会消失。当产品重新分类时，历史报告会追溯更改。数据仓库通过缓慢变化的维度来保存历史。

多源。 中型市场公司通常运行三到七个包含业务数据的系统。数据仓库整合了所有这些。我们的 ERP 数据 ETL 管道 指南详细介绍了提取和加载。

---

星型模式基础知识

星型模式将数据组织成两种类型的表：事实表和维度表。事实表位于中心（星体），维度表围绕它们（星体的点）。

事实表

事实表存储可衡量的业务事件——发生的事情。每一行代表最低有意义粒度的一个事件。

特点：

• 包含数字度量（数量、金额、持续时间、计数）
• 包含维度表的外键
• 通常是仓库中最大的桌子
• 随着新事件的发生而不断成长
• 应该是支持业务问题的最细粒度

维度表

维度表存储描述性上下文——业务事件的人物、事件、地点、时间和方式。

特点：

• 包含文本属性和层次结构
• 相对较小（数千到数百万行，而不是数十亿行）
• 随着时间的推移慢慢改变
• 为了查询简单性而进行非规范化
• 提供报告的标签、过滤器和分组

星形

                    Dim: Customer
                         |
    Dim: Product ---  Fact: Sales  --- Dim: Time
                         |
                    Dim: Location

像“按产品类别按季度按地区划分的总收入”这样的查询将销售事实表连接到三个维度表。没有子查询，没有复杂的嵌套连接——只有简单的星形连接。

---

为 ERP 和电子商务设计事实表

运行 Odoo ERP 和 Shopify 电子商务的典型中端市场公司需要四到六个事实表来涵盖核心分析用例。

事实：销售

销售事实表是基石。每一行代表销售订单上的一个行项目。

颗粒： 每个订单行项目每天一行。

事实：库存

以定期快照而非事件的方式跟踪库存水平。

谷物： 每个仓库每天每种产品一排。

事实：生产

对于制造公司来说，生产事实跟踪工作订单。

谷物： 每个工作订单每个产品每天一行。

附加事实表

• 事实：采购 --- 按供应商、产品和时间划分的采购支出。
• 事实：支持票证 --- 票证数量、响应时间、按代理、客户和类别划分的解决时间。
• 事实：网络流量 --- 页面浏览量、会话、页面转化、来源和活动。对于营销归因分析很有用。

---

设计维度表

维度表提供了使事实表数字有意义的上下文。关键原则是非规范化——存储冗余数据以简化查询。

昏暗：时间

时间维度存在于每个星型模式中。预先计算日历属性以避免查询中复杂的日期函数。

暗淡：顾客

将 CRM、会计和电子商务系统中的客户属性非规范化为单一维度。

rfm_segment 和 lifetime_value_tier 列是从 RFM 分析 派生的计算字段，由 ETL 管道定期更新。

暗淡：产品

---

缓慢变化的维度

当客户从纽约搬到伦敦时，数据仓库应该做什么？答案取决于业务问题。

类型 1：覆盖

用新值替换旧值。客户的城市变为伦敦，所有历史订单现在都显示伦敦。当属性的历史准确性不重要时使用此选项。

类型 2：添加新行

为客户创建一个新行，其中包含新城市、生效日期和到期日期。历史订单仍然指向旧行（纽约），新订单指向新行（伦敦）。这是影响分析的属性（客户群、员工部门、产品类别）最常见的方法。

类型 3：添加新列

将旧值和新值存储在单独的列中。当您需要比较之前和之后但不需要完整的历史记录时很有用。实践中较少见。

对于中型市场公司，对客户群、员工部门、产品类别和地理属性使用类型 2。对于其他所有内容都使用类型 1，以保持仓库简单。

---

ETL 设计模式

ETL（提取、转换、加载）流程将数据从源系统移动到仓库中。适用于 ERP 和电子商务数据的设计模式包括以下内容。

增量加载

不要在每次运行时重新加载所有数据，而是跟踪上次成功加载的时间戳，并仅处理此后修改的记录。 Odoo 的 write_date 字段和 Shopify 的 updated_at 参数使这一过程变得简单。

1. Query source: SELECT * FROM sale_order_line WHERE write_date > last_load_timestamp
2. Transform: Map source fields to warehouse columns, look up dimension keys
3. Load: INSERT new rows, UPDATE changed rows (upsert)
4. Update: Set last_load_timestamp to current run start time

代理密钥管理

维度表使用代理键（自动递增整数）而不是自然键（Odoo ID、Shopify ID）。这将仓库与源系统密钥格式解耦，并处理不同系统具有冲突 ID 方案的多源整合。

迟到的尺寸

有时，事实记录会先于相应的维度记录到达——订单引用尚未同步的新客户。使用占位符维度行来处理此问题，该占位符维度行会在完整维度记录到达时更新。

刷新安排

有关实时要求，请参阅我们的流分析 指南。

---

查询性能优化

设计良好的星型模式由于其简单的连接模式而已经表现良好。其他优化包括以下内容。

索引。 在事实表中的所有维度外键和常用过滤维度属性（日期范围、客户细分、产品类别）上创建索引。

具体化视图。 预先聚合常见查询：按产品类别划分的每日收入、按仓库划分的每周库存水平、按渠道划分的每月客户获取。每次 ETL 加载后刷新物化视图。

分区。 按日期（每月或每季度）对大型事实表进行分区。按日期范围筛选的查询仅扫描相关分区。

列统计信息。 在批量加载后使用 ANALYZE 保持 PostgreSQL 统计信息最新，以便查询规划器做出最佳决策。

这些优化支持自助服务 BI 体验，业务用户可以在其中运行临时查询而无需担心性能问题。

---

常见问题

公司需要多大规模才能证明数据仓库的合理性？

没有最小规模，但当您有多个数据源需要组合进行分析时，当操作数据库查询减慢生产系统的速度时，或者当您每周花费超过 10 个小时用于手动数据收集和报告创建时，这种投资就变得值得。大多数拥有 30 名或以上员工和至少两个系统（ERP 加电子商务）的公司都可以从仓库中受益。

我们应该使用像 Snowflake 或 BigQuery 这样的云数据仓库吗？

对于中端市场公司来说，PostgreSQL 可以很好地处理大多数分析工作负载，并且成本显着降低。当您的数据超过 1 TB、需要将计算与存储分离以优化成本时，或者当您有跨组织的复杂数据共享需求时，像 Snowflake 这样的云仓库就会变得有吸引力。从 PostgreSQL 开始，当您无法满足它的需求时进行迁移。

构建一个数据仓库需要多长时间？

对于经验丰富的团队来说，建立一个包含一个事实表（销售）、四个维度表以及连接 Odoo 和 Shopify 的 ETL 管道的最小可行仓库需要四到八周的时间。添加事实表、缓慢更改维度以及数据质量监控每个事实表还需要四到八周的时间。计划三到六个月建设一个涵盖所有主要业务领域的综合仓库。

---

下一步是什么

精心设计的星型模式是每项分析功能的基础——从自助仪表板到预测模型到嵌入式分析。它是更广泛的 BI 战略 的一部分，可改变您公司的决策方式。

ECOSIRE 为运行 Odoo、Shopify 和 GoHighLevel 的公司构建数据仓库和分析管道。我们的 Odoo 咨询 团队设计适合您的业务模型的仓库模式，而我们的 OpenClaw AI 服务 则在其之上进行预测分析。

联系我们 讨论您的数据仓库架构。

---

由 ECOSIRE 发布 --- 通过 Odoo ERP、Shopify 电子商务 和 OpenClaw AI 等人工智能驱动的解决方案帮助企业扩展规模。