数仓建模理论

因为工作原因小黄需要涉入大数据这一块的工作所以再次补习一下数仓建模这一块的理论参考《阿里大数据之路》这本书以及AI来给我讲解的方式进行学习。什么是数仓建模我觉得是这样数仓整套工作分为数据存储和数据建模两部分数据存储指将各业务系统的原始数据同步至数仓引擎落地保存数据建模是对已同步的原始数据按照统一规范分层、结构化重构生成标准化分析模型最终依旧落地到数仓存储中供分析使用。这里就引出了两个专业术语OLTP、OLAPOLTP联机事务处理用于产生原始业务数据的业务数据库典型如 ERP、BIP、订单系统底层库负责记录日常增删改业务操作常用 MySQL、SQL Server、Oracle 这类关系型数据库设计上遵循三范式侧重单条事务读写性能。OLAP联机分析处理专门存放经过建模加工后的分析数据也就是数据仓库会同步抽取 OLTP 业务库的数据做清洗分层建模常见引擎有 Doris、Hive、StarRocks、ClickHouse面向大批量、多维度统计查询存储全量历史数据。维度建模我看数据仓库的建模方法论有很多种其他的我也不太没怎么了解大部分制造业的应用全是维度建模所以主攻这一块的理论知识。我们拿销售订单来举例原始数据订单号日期客户商品数量金额SO0012026-06-01张三苹果10100SO0022026-06-01李四香蕉2080SO0032026-06-02张三苹果550维度建模分为维度表事实表我是这么理解的维度需要以某个字段汇总的数据相当于group by(字段)存储维度数据的表就叫维度表事实实际上需要汇总的数据相当于group by之后sum(字段)存储事实数据的表就叫事实表那么在上述的表中日期、客户、商品属于维度数量、金额属于事实。接下来就要建立维度表和事实表。事实表订单 id日期 id客户 id商品 id销售数量销售金额SO0012026-06-01C01P0110100SO0022026-06-01C02P022080SO0032026-06-02C01P01550客户维度表客户 id客户名称C01张三C02李四商品维度表商品 id商品名称P01苹果P02香蕉时间维度表日期 id年份月份2026-06-01202662026-06-0220266其实我感觉就有点类似于把一张表拆分为多张表把实际的数据转换为关联数据。一张事实表多张维度表星型模型这里我还特地问了AI这种星型模型跟我们平时业务系统中实际存储的主键其实是类似的但他们实际的目的是不一样的。业务系统OLTP三范式服务日常增、删、改业务操作下单、改客户信息、修改商品价格、退货。优先保证数据一致性修改一处数据只改一张表不会出现数据不一致限制冗余极致拆分防止更新异常。数仓星型模型OLAP服务批量统计、报表、多维分析按月算销售额、按客户统计销量。几乎不删改历史数据只新增优先降低查询 JOIN 复杂度适度接受少量冗余提升统计速度。数仓分层我们先来讨论一个问题我们需要看报表统计数据的时候有两种方案**方案A**直接从业务数据库里写SQL查询**方案B**每天把业务数据库同步到数仓在写SQL查询数仓里的数据一般来说当数据量非常大的时候我们应该选择方案B从以下几方面分析对于关系型数据库来说统计的SQL经常需要关联很多张表、聚合查询这种数据量大的情况下查询会变得非常慢。工作时间大部分都在使用系统一个长时间的查询可能导致CPU飙升造成业务系统卡顿。ODS业务系统备份所以在这种情况下就引出一个ODS层大家可以把他理解为业务系统在数仓中的备份比如销售订单表做一个ODS的话基本上字段都跟业务系统字段一致。DWD数据明细层那么ODS有什么问题呢这就要引出下一层DWD可以理解为明细数据层把原始数据进行清理后存在DWD层。比如你做销售订单分析真正关心的字段有订单号、客户、物料、数量、金额、日期等而原始表中有删除标志、修改时间、审批意见等一堆对于分析没有的字段所以ODS层对我们来说数据是非常脏、乱、杂的这时候就需要DWD来保证数据的可用性。DWS汇总层接下来我们要说DWS层对于DWD和DWS来说他们本质上还是业务系统有多少条数据他们就有多少条数据当数据量非常之大的时候该慢还是要慢这就引出了DWS层也就是汇总层。说白了汇总层就是预聚合提前group by比如老板要看销售明细等数据按维度看例如这个月、某个商品、某个客户销售了多少我们提前设计好表去定期统计数据存入这张表那么在查询相关数据时只需要查询DWS明显提升效率。月份客户销售金额2026-0601100万2026-060280万ADS应用层ADS层数据是直接服务报表的无论是DWD还是DWS他们落库始终还是建立在维度建模上也就是说事实表中只存储维度ID而不存具体业务名称例如客户名称等而ADS是给报表、BI 直接使用的成品表在建表时就提前把所有维度名称关联好把 ID 翻译成中文名称直接落地入库表里一整条包含全部业务可读信息前端不用再做任何关联。总结那么数据建模中分为以下几层ODS 原始镜像完整同步业务全量数据不作加工 DWD 清洗规整后的标准明细维度建模事实/维表存放层 DWS 按维度预聚合的中间汇总数据供多报表复用 ADS 面向业务需求的成品报表数据直接对外展示完整数据流链路线上 ERP 业务库 → ODS 原始备份层 → DWD 清洗明细层 → DWS 预汇总层 → ADS 业务报表层学到这里我又诞生了一个问题为什么不能在取数的时候直接进行清理存到DWD层而需要ODS来备份一套数据这样会浪费磁盘空间以下是AI给我的解释对账溯源必须要ODS这我其实觉得业务系统才需要对账我并不会为了这个而备份数据清洗出错可重新修复不用重读业务库这个是我觉得非常重要的一点一套原始数据多业务复用这个我觉得也蛮重要的可能业务A需要某几个字段业务B需要不同的几个字段事实表设计业务过程业务过程在我的理解下面是一个动作一个可以记录、可以计量的行为。这要跟平时做Java的思想区别开做Java关注点往往是数据表比如说销售订单对于Java来说他只是冷冰冰的一张记录表而对于数仓的这边的理解是先找业务动作再构建承载这个动作的事实表例如销售下单下单这个动作称之为业务过程。如何识别业务过程简单方法问自己两个问题问题1有没有业务动作发生客户下单有下单这个动作客户档案没有动作只有基础资料所以客户是维度下单是业务过程问题2能不能统计指标销售订单可以统计销售金额、销售数量客户档案里面只有客户名称、客户电话无法统计所以销售订单属于事实客户属于维度粒度官方定义粒度Grain就是事实表中一行数据所代表的业务含义。同一种业务过程中通常会有很多种粒度还是拿销售订单举例业务表是这样订单号行号商品数量SO0011苹果10SO0012香蕉20粒度1订单级如果按照订单级来区分粒度那么事实表应该是一行一张订单订单号金额SO001300粒度2订单行级如果按照订单行级来区分粒度那么事实表应该是一行一条订单行订单号行号商品金额SO0011苹果100SO0012香蕉200为什么强调粒度因为粒度决定了后面能分析什么例如我想知道苹果卖了多少如果是订单级粒度他是不知道某个商品卖了多少他只知道这一个订单卖了多少所以DWD通常选择最细粒度度量事实表中可以计算统计的字段被称为度量假设销售订单行有下面几个字段订单号 行号 客户 物料 数量 金额 税额 折扣额 单价 业务员 订单类型 来源单据销售订单行字段分类唯一标识订单号、行号、来源单据维度客户、物料、业务员、订单类型度量数量、金额、税额、折扣额、单价维度设计退化维本该属于维度表的属性但不单独建维度表直接存在事实表里的字段叫退化维。例如订单号、行号、来源单据等这些字段没有统计意义也没有分组意义的字段就叫退化维一致性维度多张事实表共用一套标准的维度表这些维度就是一致性维度。例如物料、客户、日期只要在同一家公司不管是销售、库存、采购都是统一使用这些维度缓慢变化维SCD有些维度是不会变化的例如物料他从投入使用开始就不可能改编码这种但是有些维度是会有变化的比如客户的所属区域可能25年属于华东26年属于华南这种会变化的维度叫做缓慢变化维。25年客户维度客户ID客户名称所属区域C001张三公司华东26年客户维度客户ID客户名称所属区域C001张三公司华南销售事实表日期客户金额2025-06张三公司100万2026-06张三公司200万我觉得这个应该是在设计前跟业务确定好要怎么看数据比如统计25年的数据时这个销售额应该属于华东还是华南。SCD Type 1覆盖更新这种情况是直接讲25年的数据统计到华南的做法直接覆盖客户数据SCD Type 2保留历史我觉得这个才是企业常用的并且更适合用的修改后变成SK客户ID客户区域1C001张三公司华东2C001张三公司华南然后销售事实表应该是通过SK1和SK2关联2025的销售数据关联SK12026年的数据关联SK2。事实表类型维度建模把事实表分为三大类事务事实表周期快照事实表累计快照事实表事务事实表这就是目前所理解的事实表专门记录业务动作并且这个业务动作只增不改。例如销售订单事实表、采购订单事实表等周期快照事实表这个也比较好理解正常用的时候比较多在我接触的ERP项目中他的库存状态是实时更新的只有库存流水记录着每一笔出入库状态这时候没法统计昨天库存多少、上个月库存多少所以就需要事实表来每天保存一次库存状态例如日期商品库存数量6月1日苹果1006月2日苹果706月3日苹果80累计快照事实表累积快照事实表记录的是一个业务实体订单、工单、采购单等从开始到结束的生命周期状态一般一条业务实体对应一行记录这行记录会随着流程推进不断更新。创建订单时order_id下单日期审核日期发货日期签收日期状态SO0012026-06-01NULLNULLNULL待审核审核完成order_id下单日期审核日期发货日期签收日期状态SO0012026-06-012026-06-01NULLNULL待发货发货完成order_id下单日期审核日期发货日期签收日期状态SO0012026-06-012026-06-012026-06-02NULL已发货签收完成order_id下单日期审核日期发货日期签收日期状态SO0012026-06-012026-06-012026-06-022026-06-04已完成