第1章 数据仓库概述1.1 数据仓库概念数据仓库是一个为数据分析而设计的企业级数据管理系统。数据仓库可集中、整合多个信息源的大量数据借助数据仓库的分析能力企业可从数据中获得宝贵的信息进而改进决策。同时随着时间的推移数据仓库中积累的大量历史数据对于数据科学家和业务分析师也是十分宝贵的。1.2数据仓库核心架构第2章 数据仓库建模概述2.1数据仓库建模的意义如果把数据看作图书馆里的书我们希望看到它们在书架上分门别类地放置如果把数据看作城市的建筑我们希望城市规划布局合理如果把数据看作电脑文件和文件夹我们希望按照自己的习惯有很好的文件夹组织方式而不是糟糕混乱的桌面经常为找一个文件而不知所措。数据模型就是数据组织和存储方法它强调从业务、数据存取和使用角度合理存储数据。只有将数据有序的组织和存储起来之后数据才能得到高性能、低成本、高效率、高质量的使用。高性能良好的数据模型能够帮助我们快速查询所需要的数据。低成本良好的数据模型能减少重复计算实现计算结果的复用降低计算成本。高效率良好的数据模型能极大的改善用户使用数据的体验提高使用数据的效率。高质量良好的数据模型能改善数据统计口径的混乱减少计算错误的可能性。2.2数据仓库建模方法论2.2.1ER模型数据仓库之父Bill Inmon提出的建模方法是从全企业的高度用实体关系Entity RelationshipER模型来描述企业业务并用规范化的方式表示出来在范式理论上符合3NF。1实体关系模型实体关系模型将复杂的数据抽象为两个概念——实体和关系。实体表示一个对象例如学生、班级关系是指两个实体之间的关系例如学生和班级之间的从属关系。2数据库规范化数据库规范化是使用一系列范式设计数据库通常是关系型数据库的过程其目的是减少数据冗余增强数据的一致性。这一系列范式就是指在设计关系型数据库时需要遵从的不同的规范。关系型数据库的范式一共有六种分别是第一范式1NF、第二范式2NF、第三范式3NF、巴斯-科德范式BCNF、第四范式(4NF和第五范式5NF。遵循的范式级别越高数据冗余性就越低。3三范式1函数依赖2第一范式3第二范式4第三范式下图为一个采用Bill Inmon倡导的建模方法构建的模型从图中可以看出较为松散、零碎物理表数量多。这种建模方法的出发点是整合数据其目的是将整个企业的数据进行组合和合并并进行规范处理减少数据冗余性保证数据的一致性。这种模型并不适合直接用于分析统计。2.2.2维度模型数据仓库领域的令一位大师——Ralph Kimball倡导的建模方法为维度建模。维度模型将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程而维度通常对应业务过程发生时所处的环境。注业务过程可以概括为一个个不可拆分的行为事件例如在线教育交易中的下单付款加购等都是业务过程。下图为一个典型的维度模型其中位于中心的SalesOrder为事实表其中保存的是下单这个业务过程的所有记录。位于周围每张表都是维度表包括Date日期Customer顾客Product产品Location地区等这些维度表就组成了每个订单发生时所处的环境即何人、何时、在何地下单了何种产品。从图中可以看出模型相对清晰、简洁。维度建模以数据分析作为出发点为数据分析服务因此它关注的重点的用户如何更快的完成需求分析以及如何实现较好的大规模复杂查询的响应性能。第3章 维度建模理论之事实表3.1事实表概述事实表作为数据仓库维度建模的核心紧紧围绕着业务过程来设计。其包含与该业务过程有关的维度引用维度表外键以及该业务过程的度量通常是可累加的数字类型字段。3.1.1事实表特点事实表通常比较“细长”即列较少但行较多且行的增速快。3.1.2事实表分类事实表有三种类型分别是事务事实表、周期快照事实表和累积快照事实表每种事实表都具有不同的特点和适用场景下面逐个介绍。3.2事务型事实表3.2.1概述事务事实表用来记录各业务过程它保存的是各业务过程的原子操作事件即最细粒度的操作事件。粒度是指事实表中一行数据所表达的业务细节程度。事务型事实表可用于分析与各业务过程相关的各项统计指标由于其保存了最细粒度的记录可以提供最大限度的灵活性可以支持无法预期的各种细节层次的统计需求。3.2.2设计流程设计事务事实表时一般可遵循以下四个步骤选择业务过程→声明粒度→确认维度→确认事实1选择业务过程在业务系统中挑选我们感兴趣的业务过程业务过程可以概括为一个个不可拆分的行为事件例如在线教育交易中的下单付款加购等都是业务过程。通常情况下一个业务过程对应一张事务型事实表。2声明粒度业务过程确定后需要为每个业务过程声明粒度。即精确定义每张事务型事实表的每行数据表示什么应该尽可能选择最细粒度以此来应对各种细节程度的需求。典型的粒度声明如下订单事实表中一行数据表示的是一个订单中的一门课程。3确定维度确定维度具体是指确定与每张事务型事实表相关的维度有哪些。确定维度时应尽量多的选择与业务过程相关的环境信息。因为维度的丰富程度就决定了维度模型能够支持的指标丰富程度。4确定事实此处的“事实”一词指的是每个业务过程的度量值通常是可累加的数字类型的值例如次数、个数、件数、金额等。经过上述四个步骤事务型事实表就基本设计完成了。第一步选择业务过程可以确定有哪些事务型事实表第二步可以确定每张事务型事实表的每行数据是什么第三步可以确定每张事务型事实表的维度外键第四步可以确定每张事务型事实表的度量值字段。3.2.3不足事务型事实表可以保存所有业务过程的最细粒度的操作事件故理论上其可以支撑与各业务过程相关的各种统计粒度的需求。但对于某些特定类型的需求其逻辑可能会比较复杂或者效率会比较低下。例如1存量型指标例如购物车存量账户余额等。此处以在线教育中的加购业务为例加购业务包含的业务过程主要包括加购物车和减购物车两个业务过程各自对应一张事务型事实表一张存储所有加购物车的原子操作事件另一张存储所有减购物车的原子操作事件。假定现有一个需求要求统计截至当日的各用户各科目的购物车存量。由于加购物车和减购物车操作均会影响到购物车存量故需要对两张事务型事实表进行聚合且需要区分两者对购物车存量的影响加或减另外需要对两张表的全表数据聚合才能得到统计结果。可以看到不论是从逻辑上还是效率上考虑这都不是一个好的方案。2多事务关联统计例如现需要统计最近30天用户下单到支付的时间间隔的平均值。统计思路应该是找到下单事务事实表和支付事务事实表过滤出最近30天的记录然后按照订单id对两张事实表进行关联之后用支付时间减去下单时间然后再求平均值。逻辑上虽然并不复杂但是其效率较低因为下单事务事实表和支付事务事实表均为大表大表join大表的操作应尽量避免。可以看到在上述两种场景下事务型事实表的表现并不理想。下面要介绍的另外两种类型的事实表就是为了弥补事务型事实表的不足的。3.3周期型快照事实表3.3.1概述周期快照事实表以具有规律性的、可预见的时间间隔来记录事实主要用于分析一些存量型例如购物车存量账户余额或者状态型空气温度行驶速度指标。对于购物车存量、账户余额这些存量型指标业务系统中通常就会计算并保存最新结果所以定期同步一份全量数据到数据仓库构建周期型快照事实表就能轻松应对此类统计需求而无需再对事务型事实表中大量的历史记录进行聚合了。对于空气温度、行驶速度这些状态型指标由于它们的值往往是连续的我们无法捕获其变动的原子事务操作所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样构建周期型快照事实表。3.3.2设计流程1确定粒度周期型快照事实表的粒度可由采样周期和维度描述故确定采样周期和维度后即可确定粒度。采样周期通常选择每日。维度可根据统计指标决定例如指标为统计每个用户每个科目的购物车存量则可确定维度为用户和科目。确定完采样周期和维度后即可确定该表粒度为每日-用户-科目。2确认事实事实也可根据统计指标决定例如指标为统计每个用户每个科目的购物车存量则事实为购物车存量。3.3.3事实类型此处的事实类型是指度量值的类型而非事实表的类型。事实度量值共分为三类分别是可加事实半可加事实和不可加事实。1可加事实可加事实是指可以按照与事实表相关的所有维度进行累加例如事务型事实表中的事实。2半可加事实半可加事实是指只能按照与事实表相关的一部分维度进行累加例如周期型快照事实表中的事实。以上述各仓库中各用户购物车存量每天快照事实表为例这张表中的购物车存量事实可以按照用户或者科目维度进行累加但是不能按照时间维度进行累加因为将每天的购物车存量累加起来是没有任何意义的。3不可加事实不可加事实是指完全不具备可加性例如比率型事实。不可加事实通常需要转化为可加事实例如比率可转化为分子和分母。3.4累积型快照事实表3.4.1概述累积型快照事实表是基于一个业务流程中的多个关键业务过程联合处理而构建的事实表如交易流程中的试听、下单、支付等业务过程。累积型快照事实表通常具有多个日期字段每个日期对应业务流程中的一个关键业务过程里程碑。课程id用户id试听日期下单日期支付日期订单分摊金额支付分摊金额100112342022-02-192022-02-202022-02-2110001000累积型快照事实表主要用于分析业务过程里程碑之间的时间间隔等需求。例如前文提到的用户下单到支付的平均时间间隔使用累积型快照事实表进行统计就能避免两个事务事实表的关联操作从而变得十分简单高效。3.4.2设计流程累积型快照事实表的设计流程同事务型事实表类似也可采用以下四个步骤下面重点描述与事务型事实表的不同之处。选择业务过程→声明粒度→确认维度→确认事实。1选择业务过程选择一个业务流程中需要关联分析的多个关键业务过程多个业务过程对应一张累积型快照事实表。2声明粒度精确定义每行数据表示的是什么尽量选择最小粒度。3确认维度选择与各业务过程相关的维度需要注意的是每各业务过程均需要一个日期维度。4确认事实选择各业务过程的度量值。
在线教育之离线数仓 day01
第1章 数据仓库概述1.1 数据仓库概念数据仓库是一个为数据分析而设计的企业级数据管理系统。数据仓库可集中、整合多个信息源的大量数据借助数据仓库的分析能力企业可从数据中获得宝贵的信息进而改进决策。同时随着时间的推移数据仓库中积累的大量历史数据对于数据科学家和业务分析师也是十分宝贵的。1.2数据仓库核心架构第2章 数据仓库建模概述2.1数据仓库建模的意义如果把数据看作图书馆里的书我们希望看到它们在书架上分门别类地放置如果把数据看作城市的建筑我们希望城市规划布局合理如果把数据看作电脑文件和文件夹我们希望按照自己的习惯有很好的文件夹组织方式而不是糟糕混乱的桌面经常为找一个文件而不知所措。数据模型就是数据组织和存储方法它强调从业务、数据存取和使用角度合理存储数据。只有将数据有序的组织和存储起来之后数据才能得到高性能、低成本、高效率、高质量的使用。高性能良好的数据模型能够帮助我们快速查询所需要的数据。低成本良好的数据模型能减少重复计算实现计算结果的复用降低计算成本。高效率良好的数据模型能极大的改善用户使用数据的体验提高使用数据的效率。高质量良好的数据模型能改善数据统计口径的混乱减少计算错误的可能性。2.2数据仓库建模方法论2.2.1ER模型数据仓库之父Bill Inmon提出的建模方法是从全企业的高度用实体关系Entity RelationshipER模型来描述企业业务并用规范化的方式表示出来在范式理论上符合3NF。1实体关系模型实体关系模型将复杂的数据抽象为两个概念——实体和关系。实体表示一个对象例如学生、班级关系是指两个实体之间的关系例如学生和班级之间的从属关系。2数据库规范化数据库规范化是使用一系列范式设计数据库通常是关系型数据库的过程其目的是减少数据冗余增强数据的一致性。这一系列范式就是指在设计关系型数据库时需要遵从的不同的规范。关系型数据库的范式一共有六种分别是第一范式1NF、第二范式2NF、第三范式3NF、巴斯-科德范式BCNF、第四范式(4NF和第五范式5NF。遵循的范式级别越高数据冗余性就越低。3三范式1函数依赖2第一范式3第二范式4第三范式下图为一个采用Bill Inmon倡导的建模方法构建的模型从图中可以看出较为松散、零碎物理表数量多。这种建模方法的出发点是整合数据其目的是将整个企业的数据进行组合和合并并进行规范处理减少数据冗余性保证数据的一致性。这种模型并不适合直接用于分析统计。2.2.2维度模型数据仓库领域的令一位大师——Ralph Kimball倡导的建模方法为维度建模。维度模型将复杂的业务通过事实和维度两个概念进行呈现。事实通常对应业务过程而维度通常对应业务过程发生时所处的环境。注业务过程可以概括为一个个不可拆分的行为事件例如在线教育交易中的下单付款加购等都是业务过程。下图为一个典型的维度模型其中位于中心的SalesOrder为事实表其中保存的是下单这个业务过程的所有记录。位于周围每张表都是维度表包括Date日期Customer顾客Product产品Location地区等这些维度表就组成了每个订单发生时所处的环境即何人、何时、在何地下单了何种产品。从图中可以看出模型相对清晰、简洁。维度建模以数据分析作为出发点为数据分析服务因此它关注的重点的用户如何更快的完成需求分析以及如何实现较好的大规模复杂查询的响应性能。第3章 维度建模理论之事实表3.1事实表概述事实表作为数据仓库维度建模的核心紧紧围绕着业务过程来设计。其包含与该业务过程有关的维度引用维度表外键以及该业务过程的度量通常是可累加的数字类型字段。3.1.1事实表特点事实表通常比较“细长”即列较少但行较多且行的增速快。3.1.2事实表分类事实表有三种类型分别是事务事实表、周期快照事实表和累积快照事实表每种事实表都具有不同的特点和适用场景下面逐个介绍。3.2事务型事实表3.2.1概述事务事实表用来记录各业务过程它保存的是各业务过程的原子操作事件即最细粒度的操作事件。粒度是指事实表中一行数据所表达的业务细节程度。事务型事实表可用于分析与各业务过程相关的各项统计指标由于其保存了最细粒度的记录可以提供最大限度的灵活性可以支持无法预期的各种细节层次的统计需求。3.2.2设计流程设计事务事实表时一般可遵循以下四个步骤选择业务过程→声明粒度→确认维度→确认事实1选择业务过程在业务系统中挑选我们感兴趣的业务过程业务过程可以概括为一个个不可拆分的行为事件例如在线教育交易中的下单付款加购等都是业务过程。通常情况下一个业务过程对应一张事务型事实表。2声明粒度业务过程确定后需要为每个业务过程声明粒度。即精确定义每张事务型事实表的每行数据表示什么应该尽可能选择最细粒度以此来应对各种细节程度的需求。典型的粒度声明如下订单事实表中一行数据表示的是一个订单中的一门课程。3确定维度确定维度具体是指确定与每张事务型事实表相关的维度有哪些。确定维度时应尽量多的选择与业务过程相关的环境信息。因为维度的丰富程度就决定了维度模型能够支持的指标丰富程度。4确定事实此处的“事实”一词指的是每个业务过程的度量值通常是可累加的数字类型的值例如次数、个数、件数、金额等。经过上述四个步骤事务型事实表就基本设计完成了。第一步选择业务过程可以确定有哪些事务型事实表第二步可以确定每张事务型事实表的每行数据是什么第三步可以确定每张事务型事实表的维度外键第四步可以确定每张事务型事实表的度量值字段。3.2.3不足事务型事实表可以保存所有业务过程的最细粒度的操作事件故理论上其可以支撑与各业务过程相关的各种统计粒度的需求。但对于某些特定类型的需求其逻辑可能会比较复杂或者效率会比较低下。例如1存量型指标例如购物车存量账户余额等。此处以在线教育中的加购业务为例加购业务包含的业务过程主要包括加购物车和减购物车两个业务过程各自对应一张事务型事实表一张存储所有加购物车的原子操作事件另一张存储所有减购物车的原子操作事件。假定现有一个需求要求统计截至当日的各用户各科目的购物车存量。由于加购物车和减购物车操作均会影响到购物车存量故需要对两张事务型事实表进行聚合且需要区分两者对购物车存量的影响加或减另外需要对两张表的全表数据聚合才能得到统计结果。可以看到不论是从逻辑上还是效率上考虑这都不是一个好的方案。2多事务关联统计例如现需要统计最近30天用户下单到支付的时间间隔的平均值。统计思路应该是找到下单事务事实表和支付事务事实表过滤出最近30天的记录然后按照订单id对两张事实表进行关联之后用支付时间减去下单时间然后再求平均值。逻辑上虽然并不复杂但是其效率较低因为下单事务事实表和支付事务事实表均为大表大表join大表的操作应尽量避免。可以看到在上述两种场景下事务型事实表的表现并不理想。下面要介绍的另外两种类型的事实表就是为了弥补事务型事实表的不足的。3.3周期型快照事实表3.3.1概述周期快照事实表以具有规律性的、可预见的时间间隔来记录事实主要用于分析一些存量型例如购物车存量账户余额或者状态型空气温度行驶速度指标。对于购物车存量、账户余额这些存量型指标业务系统中通常就会计算并保存最新结果所以定期同步一份全量数据到数据仓库构建周期型快照事实表就能轻松应对此类统计需求而无需再对事务型事实表中大量的历史记录进行聚合了。对于空气温度、行驶速度这些状态型指标由于它们的值往往是连续的我们无法捕获其变动的原子事务操作所以无法使用事务型事实表统计此类需求。而只能定期对其进行采样构建周期型快照事实表。3.3.2设计流程1确定粒度周期型快照事实表的粒度可由采样周期和维度描述故确定采样周期和维度后即可确定粒度。采样周期通常选择每日。维度可根据统计指标决定例如指标为统计每个用户每个科目的购物车存量则可确定维度为用户和科目。确定完采样周期和维度后即可确定该表粒度为每日-用户-科目。2确认事实事实也可根据统计指标决定例如指标为统计每个用户每个科目的购物车存量则事实为购物车存量。3.3.3事实类型此处的事实类型是指度量值的类型而非事实表的类型。事实度量值共分为三类分别是可加事实半可加事实和不可加事实。1可加事实可加事实是指可以按照与事实表相关的所有维度进行累加例如事务型事实表中的事实。2半可加事实半可加事实是指只能按照与事实表相关的一部分维度进行累加例如周期型快照事实表中的事实。以上述各仓库中各用户购物车存量每天快照事实表为例这张表中的购物车存量事实可以按照用户或者科目维度进行累加但是不能按照时间维度进行累加因为将每天的购物车存量累加起来是没有任何意义的。3不可加事实不可加事实是指完全不具备可加性例如比率型事实。不可加事实通常需要转化为可加事实例如比率可转化为分子和分母。3.4累积型快照事实表3.4.1概述累积型快照事实表是基于一个业务流程中的多个关键业务过程联合处理而构建的事实表如交易流程中的试听、下单、支付等业务过程。累积型快照事实表通常具有多个日期字段每个日期对应业务流程中的一个关键业务过程里程碑。课程id用户id试听日期下单日期支付日期订单分摊金额支付分摊金额100112342022-02-192022-02-202022-02-2110001000累积型快照事实表主要用于分析业务过程里程碑之间的时间间隔等需求。例如前文提到的用户下单到支付的平均时间间隔使用累积型快照事实表进行统计就能避免两个事务事实表的关联操作从而变得十分简单高效。3.4.2设计流程累积型快照事实表的设计流程同事务型事实表类似也可采用以下四个步骤下面重点描述与事务型事实表的不同之处。选择业务过程→声明粒度→确认维度→确认事实。1选择业务过程选择一个业务流程中需要关联分析的多个关键业务过程多个业务过程对应一张累积型快照事实表。2声明粒度精确定义每行数据表示的是什么尽量选择最小粒度。3确认维度选择与各业务过程相关的维度需要注意的是每各业务过程均需要一个日期维度。4确认事实选择各业务过程的度量值。
