数据仓库建模怎么做?从业务过程到事实表粒度的完整方法

零门槛、免安装!海量模板方案,点击即可,在线试用!

免费试用

数据仓库建模怎么做?从业务过程到事实表粒度的完整方法

阅读人数:153预计阅读时长:5 min

数据仓库最容易踩的坑,不是表建少了,而是表建了、数据汇了,指标却算不准,不同报表的结果还对不上。

销售分析要关联订单、客户和产品,回款分析要核对收款记录,库存分析还要追踪入库、出库和结存。数据明明都在数仓里,为什么分析起来还是这么费劲?

问题往往出在建模环节。业务过程没有梳理清楚,事实表粒度没有定义明确,维度关系没有设计合理,后续分析就很容易出错。

下面结合企业常见的销售业务,讲清楚数据仓库建模的核心方法。

一、数仓建模第一步:先理解业务,而不是先建表

业务分析关注销售额、客户回款和订单履约,背后对应的是不同的业务过程。

如果直接关联订单、客户、产品和收款表,容易混淆数据粒度,造成金额重复计算。

数据仓库建模的起点,是明确业务过程、分析需求和统计口径。

梳理业务过程

业务过程,是企业经营活动中持续发生、能够被记录和分析的事件。

梳理时,要明确三个问题:业务事件是什么、事件发生时记录什么、后续需要分析什么。这些问题决定了需要采集哪些数据,以及事实表应该如何设计。

明确业务分析口径

同样是销售额,不同部门可能采用不同口径。销售部门关注订单金额,财务部门关注确认的营业收入,经营部门可能关注扣除退款后的净销售额。

建模之前,需要明确指标定义、统计范围、时间口径和计算规则。订单取消后是否计入金额、退款归属哪个期间、收入按照订单日期还是确认日期统计,都应提前确定。

业务口径没有统一,数仓建模越完善,反而越容易产生看似精确、实际不一致的报表。

二、事实表和维度表,到底有什么区别?

理解数仓建模,首先要弄清楚两个概念:

  • 事实表记录业务发生了什么,以及发生了多少。
  • 维度表描述业务对象是什么样的,以及可以从哪些角度分析。

事实表:记录业务事件和度量值

事实表通常包含三类信息:

  • 关联键:客户键、商品键、日期键等,用于连接维度表。
  • 业务度量值:销售数量、销售金额、折扣金额、成本金额等。
  • 追踪字段:订单编号、订单行编号、业务状态和来源系统标识等。

事实表的核心任务,是准确记录业务过程中的事实,并保持清晰、稳定的数据粒度。

维度表:提供分析视角

维度表主要描述业务对象的属性。

销售事实表记录销售金额,产品维度表描述商品属于什么品类、什么品牌。两者关联后,就能按品类、品牌分析销售表现。

事实表决定记录什么,维度表决定从什么角度分析。

关联时还要确保键值唯一、关系明确,避免维度表中的重复记录导致事实数据被重复计算。

在实际开发中,可以通过 FineDataLink 的 ETL 数据流完成字段映射、数据清洗和表关联,将事实表与维度表按照既定规则加工到数仓中。

三、建模最关键的一步:先确定事实表的粒度

粒度,就是事实表中一条记录究竟代表什么业务含义。

销售事实表可以采用不同粒度,但必须在设计时明确。

粒度不同,分析能力也不同

假设某笔订单包含两个商品:

  • 采用订单明细粒度,每行代表一个订单商品明细,可以分析商品销量、销售额和订单商品结构。
  • 采用订单头粒度,每行代表一笔完整订单,适合分析订单数量、订单状态和订单整体金额,但商品明细需要另外保存。
  • 采用门店日汇总粒度,每行代表一个门店一天的销售汇总,适合分析门店日销售趋势,却无法还原每笔订单的明细。

粒度决定了事实表能够支持哪些分析,也决定了指标应该如何汇总。

粒度不清,最容易造成重复计算

订单 SO1001 的订单金额为 500 元,后续产生两笔收款,分别为 200 元和 300 元。

如果直接将订单表与收款明细表连接,收款金额合计为 500 元,但订单金额如果直接求和,就会变成 1000 元。

问题不在于 SQL 写错了,而在于订单事实与收款事实具有不同的粒度。

正确做法是分别建立订单事实表和收款事实表,根据分析需求汇总到一致的业务粒度后再关联。如果需要分析具体的收款核销关系,则应依据核销明细设计关联逻辑。

设计事实表之前,先用一句话描述每条记录的含义:一行代表一个订单商品明细、一笔收款事件,还是某个时点的库存快照。

先定粒度,再设计字段;先明确业务含义,再决定表结构。

四、事实表怎么设计?常见的三种类型

常见的事实表包括事务事实表、周期快照事实表和累积快照事实表。它们解决的问题不同,不能简单互相替代。

事务事实表:记录每一次业务事件

事务事实表用于记录业务过程中发生的具体事件,常见于订单明细、收款流水和商品出入库明细。

以收款业务为例,一条记录可以包含收款流水号、客户键、收款日期键、收款金额和支付方式。每发生一笔新的收款,就增加相应的事实记录。

这种设计适合分析业务发生的时间、数量和金额,也便于追踪明细、核查异常。

订单状态变化、退款和冲销等业务,则需要根据实际规则保留相应的变化记录或调整记录,不能简单用最新状态覆盖所有历史信息。

周期快照事实表:记录某个周期的业务状态

周期快照事实表按照固定时间间隔记录业务状态,常用于库存、账户余额和应收账款余额等场景。

通过每日库存快照,可以观察库存变化和比较不同仓库的库存水平。

但期末库存不能像销售金额一样随意跨日期累加。三天的库存相加得到 310,并不代表这三天实际流转了 310 件商品。

设计快照事实表时,需要区分期末余额、期间发生量和期间累计量。

累积快照事实表:跟踪业务流程的进展

累积快照事实表用于记录一个业务流程从开始到结束的关键节点。

订单履约过程中,可以记录订单创建、审核、发货和签收时间,再分析各环节耗时、订单积压情况和整体履约周期。

这种设计适合订单履约、采购执行、生产工单和售后处理等具有明确流程节点的业务。

如果还需要完整追踪每一次状态变化,则应结合事务事实表或状态变更明细表,保留更完整的历史轨迹。

对于需要及时反映业务变化的事务事实表,FineDataLink 的实时管道可以根据数据源类型和时效要求,利用 CDC 等机制捕获源数据库变化;对于库存快照等周期性数据,则可以通过定时任务按设定周期采集和加工。

五、维度表怎么设计?重点关注这几个问题

维度表看似只是保存名称、类别和编码,但如果处理不好,历史分析、跨表关联和指标口径都会受到影响。

建立统一的业务维度

同一个客户可能同时出现在 CRM、ERP 和财务系统中,各系统使用的客户编号也可能不同。如果直接将不同系统的编号当作统一主键,同一个客户就可能被识别为多个对象。

因此,需要建立统一的客户维度,将源系统编码、统一客户标识和客户属性进行关联。

统一客户键用于连接数仓中的事实表,源系统编号用于追踪数据来源和维护映射关系。

跨系统编码映射不一定总是一对一。客户合并、拆分或多个业务主体共用客户资料时,都需要明确匹配规则。

在数据准备环节,FineDataLink 的数据清洗能力可以用于统一处理重复出现的字段问题、按规则替换异常值,以及在 ETL 流程中进行字段和编码转换,减少多源数据进入数仓后的重复清洗工作。

区分业务主键与代理键

  • 业务主键:业务系统用于识别对象的标识,如客户编号、商品编码。
  • 代理键:数仓内部生成的标识,常用于维度关联和历史版本管理。

业务主键负责识别业务对象,代理键则便于管理数仓中的维度记录及其历史版本。

维度属性变化,历史数据怎么处理?

客户可能调整所属区域,产品可能变更分类。如果直接覆盖旧属性,历史数据就可能按照最新属性重新归类。

常见方式有两种:

  • 直接覆盖:只保留当前属性,不要求追溯历史变化。
  • 保留历史版本:采用缓慢变化维度(SCD),记录不同版本的生效时间和失效时间。

假设客户 C001 在 1—6 月属于华东区域,7 月起调整为华南区域。采用历史版本管理后,才能按照当时的区域归属分析上半年和下半年的销售表现。

如果业务需要还原历史,就必须提前设计历史版本管理规则。

时间维度不要只保存一个日期字段

如果需要统一处理财务期间、自然周、节假日和工作日等口径,可以建立时间维度,维护日期、月份、季度、年度、星期和财务期间等属性。

一笔订单还可能同时包含下单日期、发货日期和签收日期。这些时间对应不同的分析问题,因此应保留相应的日期键,按业务需求选择统计口径。

六、事实表和维度表建好之后,怎么组织成数仓模型?

常见的维度建模结构包括星型模型和雪花模型。

星型模型:结构直观,便于分析

星型模型以事实表为中心,周围关联客户、产品、时间、组织等维度表。

销售事实表关联这些维度后,就可以按月份、产品类别、客户区域和销售团队进行分析。

星型模型结构直观、查询路径清晰,适合经营分析、BI 报表和多维度指标统计。

雪花模型:进一步拆分维度层级

雪花模型会将部分维度拆分成多个关联表。例如,产品维度中的产品类别、品类大类等属性,可以拆分到不同层级的表中。

这种方式有利于管理复杂的维度层级,但会增加关联层级和查询复杂度。应结合数据规模、查询方式、维度管理要求和维护成本选择,而不是刻意追求某种模型形式。

还要注意,订单事实表、收款事实表和库存快照表可以共享客户、产品和时间等一致性维度,但不能因此直接按明细互相关联。

跨事实表分析时,仍需明确业务关系,并根据需要汇总到一致的粒度,避免重复计算。

免费试用

当数仓表和加工任务逐渐增多,模型之间的依赖关系也会变得复杂。此时,FineDataLink 的血缘分析功能可以帮助查看数据表与开发任务之间的处理关系,辅助追踪数据来源、排查重复加工,并评估调整上游处理逻辑可能影响的下游对象。

七、从业务过程到数仓落地,建议按照这套流程推进

第一步:梳理业务需求与业务过程

收集销售、采购、库存、生产和财务等部门的分析需求,明确关键指标、统计口径、数据来源和需要保留的历史信息。

第二步:确定事实表粒度

明确每个业务过程的一行数据代表什么。在粒度确定之前,不要急于设计字段或关联关系。

第三步:识别事实与维度

确定事实表中的业务键、维度键和度量值,再识别客户、产品、时间、组织、渠道等维度,同时梳理跨系统编码映射和历史变化规则。

第四步:设计表结构与关联规则

明确事实表的唯一性约束、维度代理键、数据类型和关联关系。对于一对多、多对多等复杂关系,需要提前确定处理方式,避免重复关联。

第五步:实施数据集成与加工

模型设计完成后,再开展数据采集和加工。企业可以通过 FineDataLink 连接不同业务系统,配置全量、增量或 CDC 同步任务,并通过 ETL 流程完成字段映射、数据清洗、编码转换、表关联和计算,将处理结果写入数仓。

数据集成工具负责执行处理规则,但不能替代业务过程梳理、粒度确定和指标口径设计。如果模型本身存在问题,自动化程度再高,也只会更快地生成有问题的数据。

第六步:校验数据与业务口径

数据层面,检查记录数量、主键唯一性、关键字段完整性和维度关联成功率;业务层面,核对销售金额、收款金额和库存余额等关键指标。

FineDataLink 的数据质量检测与任务调度能力,可以用于配置校验规则、跟踪任务状态和发现处理异常,帮助企业将数据校验纳入日常加工流程。

第七步:通过实际分析验证模型

数仓建模不是建完表就结束了。还要验证模型能否支持客户、产品、区域和时间等多维度分析,能否在不重复计算订单金额的前提下分析回款,以及能否还原历史组织归属。

如果业务分析仍然需要大量临时 SQL 才能完成,就需要重新检查事实表粒度、维度设计、指标口径和数据关联关系。

结语

数据仓库建模,真正要解决的不是如何把表设计得更复杂,而是如何让业务数据形成一套清晰、统一、可复用的分析结构。

从业务过程入手,明确分析需求;根据业务事件确定事实表;围绕分析视角设计维度表;先确定粒度,再处理关联关系和历史变化。

其中,事实表决定记录什么,维度表决定从什么角度分析,粒度决定数据能否被正确统计。

真正有价值的数仓,不是表越多越好,而是业务口径清楚、数据关系明确、分析结果可信,并且能够随着业务发展持续演进。

【AI声明】本文内容通过大模型匹配关键字智能生成,仅供参考,帆软不对内容的真实、准确或完整作任何形式的承诺。如有任何问题或意见,您可以通过联系blog@fanruan.com进行反馈,帆软收到您的反馈后将及时答复和处理。

若想了解更多关于FineDataLink的相关信息,您可以访问下方链接,或点击下方组件,快速获得帆软为您提供的企业大数据分析平台建设建议、免费的FineDataLink试用和同行业自助智能分析标杆案例学习参考。

了解更多FineDataLink信息:www.finedatalink.com

帆软FineDataLink数据集成平台在线试用!

免费下载

评论区

暂无评论
帆软企业数字化建设产品推荐
报表开发平台免费试用
自助式BI分析免费试用
数据可视化大屏免费试用
数据集成平台免费试用