只做美食类目产品的网站,小程序可以自己开发吗,云盘做网站,二级分销最佳佣金比例文章目录 1. 引言1.1基本概念1.2 事实表定义 2. 设计原则2.1 原则一#xff1a;全面覆盖业务相关事实2.2 原则二#xff1a;精选与业务过程紧密相关的事实2.3 原则三#xff1a;拆分不可加事实为可加度量2.4 原则四#xff1a;明确声明事实表的粒度2.5 原则五#xff1a;避… 文章目录 1. 引言1.1基本概念1.2 事实表定义 2. 设计原则2.1 原则一全面覆盖业务相关事实2.2 原则二精选与业务过程紧密相关的事实2.3 原则三拆分不可加事实为可加度量2.4 原则四明确声明事实表的粒度2.5 原则五避免同一事实表中存在不同粒度的事实2.6 原则六统一事实的度量单位2.7 原则七妥当处理事实的null值2.8 原则八使用退化维度提升事实表的可用性 3. 设计方法3.1 事实表设计流程3.2 设计案例订单事实表 4. 事务类型4.1 单、多事务事实表4.2 其他事实表 5. 写在最后 1. 引言
1.1基本概念
事实表顾名思义是用来存储事实的表这些事实通常是指可以量化的业务指标如销售额、订单数量等。事实表的特点是有大量的行每行代表一个业务事件的度量。
换句话说就是你要关注事物的内容事实表就像故事中的主角它包含我们感兴趣的主要信息如销售金额、订购数量、利润以及它们发生的时间和地点等。事实表中的每一行数据都代表了某种业务活动就好比故事中的一个关键事件一样。
比如一张记录了公司所有业务交易的清单。每一条记录都是一个事实比如一次销售或一笔支出。
举个例子假设我们有一个简单的销售事实表它记录了每次销售的金额和日期
CREATE TABLE Sales_Fact (SaleID INT PRIMARY KEY,ProductID INT,SaleAmount DECIMAL(10,2),SaleDate DATE
);在这个例子中SaleID 是每条销售记录的唯一标识ProductID 与维度表相关联SaleAmount 是销售金额SaleDate 是销售日期。
其他详细内容可以看数据仓库核心揭秘事实表与维度表的角色与区别
1.2 事实表定义
事实表是数据仓库中的核心它与维度表相对应存储了业务过程中量化的数据也就是我们通常所说的度量值measures。事实表通常包含以下主要部分
度量值这些是事实表中的主要数据可以进行数值计算如销售额、订单数量、产品单价等。维度键这些是指向维度表的外键通过它们事实表与维度表相连从而为度量值提供上下文信息。上下文信息提供额外的业务信息如时间戳、事务ID等。
“粒度”描述了事实表中每条记录所捕捉到的业务细节的深度。它可以通过两个维度来衡量首先是维度属性的组合它们决定了数据条目的详细程度其次是数据条目所代表的具体业务含义。 如果我们选择“产品维度”的“SKU”作为粒度那么我们的数据条目将非常详细因为每个SKU都是独特的能够反映单个商品的销售情况。例如一个数据条目可能表示“在2024年6月10日北京地区某款智能手机的销售额为3000元”。 事实表中的数据作为衡量业务流程的量度通常以整数或小数形式出现并分为三种可加性类型
可加性事实这类数据允许我们在事实表的任何维度上进行汇总。例如我们可以将不同时间或地区的销售数据进行求和以得到总销售额。 一个销售数据的事实表记录了每笔交易的销售额。如果我们要计算总销售额我们可以将所有交易的销售额相加 2024年1月1日北京销售额100元。 2024年1月1日上海销售额200元。 2024年1月2日北京销售额150元。 半可加性事实这类数据仅能在特定的维度上进行汇总。以库存为例我们可以按地点或商品类别进行汇总但如果尝试按时间维度累加每个月的库存量这种汇总就失去了实际意义。 分析库存数据库存数量可以按地点或商品类别进行汇总但按时间维度累加就没有意义。例如 2024年1月1日北京库存数量50件。 2024年1月1日上海库存数量30件。 我们可以计算北京和上海的总库存数量80件。但如果我们尝试将1月份每天的库存数量累加这就没有意义因为库存数量是随时间变化的每天的库存数量并不是独立的而是相互关联的。 不可加性事实这类度量无法通过任何维度进行汇总例如比率或百分比。然而即使是不可加性事实我们通常也可以通过将其分解为可加的组成部分来实现某种形式的聚合分析。 记录了每个订单的利润率销售额减去成本后的百分比这个度量就是不可加的。例如 订单1的利润率是20%。 订单2的利润率是15%。 我们不能简单地将这两个利润率相加得到一个总体的利润率。相反如果我们想要得到平均利润率我们需要先计算每个订单的实际利润然后将这些利润相加最后除以订单的总数。例如 订单1的销售额是100元成本是80元利润是20元。 订单2的销售额是150元成本是130元利润是20元。 总利润是40元订单总数是2所以平均利润率是 40/(100150)16.67% 2. 设计原则
2.1 原则一全面覆盖业务相关事实
事实表设计的核心目标是全面捕捉业务流程的每一个细节。在设计时我们应该无一遗漏地纳入所有与业务过程紧密相关的量化事实哪怕这可能导致数据的轻微冗余。由于事实数据通常以数字形式存储其对存储空间的影响相对较小。 案例在一家零售店事实表不仅记录了每笔交易的销售额还记录了交易时间、顾客ID和购买的商品种类。即使某些信息如交易时间在某些分析中不是必需的它的包含仍然为更全面的业务分析提供了可能。 2.2 原则二精选与业务过程紧密相关的事实
在挑选事实时我们必须严格筛选确保只包含那些直接与当前业务过程相关的事实。这有助于保持数据的清晰性和分析的准确性。 案例在一个电商平台的订单处理过程中事实表应记录订单号、商品详情和顾客信息而支付金额则属于支付过程的事实应从订单事实表中排除。 2.3 原则三拆分不可加事实为可加度量
面对不可直接汇总的度量我们应通过创造性地拆分将其转化为可加的组成部分从而扩展分析的可能性。 案例网站的用户访问数据中如果记录了每个页面的浏览次数和独立访客数我们可以将“购买率”这一不可加事实拆分为“购买人数”和“浏览人数”使得原本难以聚合的数据变得可以分析。 2.4 原则四明确声明事实表的粒度
在设计事实表时粒度的选择至关重要。我们应从最细的原子粒度开始设计以满足当前和未来可能的用户需求。 案例销售事实表可能以单个交易为粒度记录每一次购买的详细信息。而在汇总销售数据时我们可以按商品、时间或地区等维度进行聚合。 2.5 原则五避免同一事实表中存在不同粒度的事实
在一张事实表中应避免混合不同粒度的事实以防止汇总时出现重复计算的问题。 案例如果事实表同时记录了单个订单和包含多个子订单的大订单那么在汇总支付金额时大订单中的子订单金额可能会被重复计算。 小订单ID大订单ID小订单付款金额小订单购买数量大订单付款金额L1001B11001300L1002B12001300L1003B21501200L1004B2501200
2.6 原则六统一事实的度量单位
在事实表中所有度量单位应保持一致无论是货币单位还是数量单位这有助于简化分析过程并避免混淆。 案例在财务事实表中所有的金额数据都应该统一为“元”或“分”确保在进行财务分析时的一致性和准确性。 2.7 原则七妥当处理事实的null值
由于null值在某些查询中无法参与计算我们应事先设定规则将null值替换为零或其他适当的默认值。 案例如果销售事实表中的“退货数量”字段出现null我们可以将其默认填充为0以避免在计算总销售数量时出现错误。 2.8 原则八使用退化维度提升事实表的可用性
通过将常用维度属性直接嵌入到事实表中我们可以减少对维度表的依赖提高查询效率。 案例在销售事实表中如果将“商品名称”作为退化维度直接包含那么在进行商品销售分析时就无需额外关联商品维度表从而加快查询速度并减少I/O操作。 3. 设计方法
3.1 事实表设计流程
在构建数据仓库的事实表之前我们必须首先深入挖掘并明确业务的核心需求以及确定事实表所扮演的角色。这一步骤要求我们对业务流程进行全面的需求分析洞察整个业务生命周期的每一个关键步骤并且精准筛选出与我们需求紧密相连的业务活动。
接下来我们必须精确地声明事实表的粒度力求达到原子级别的细节以便捕捉业务活动中最细微的变化。
在粒度确定之后我们也随之锁定了事实表的主键。基于此我们可以识别出与这些主键相关联的维度组合以及它们所包含的维度字段。
我们还需要明确在这个业务过程中所度量的关键指标是什么并确保将不可加的度量进行适当的拆分以便于进行有效的数据聚合。
此外为了优化查询性能和减少数据冗余我们应该尽可能地将维度属性退化并直接嵌入到事实表中这样不仅提升了数据的可用性也简化了数据模型。
3.2 设计案例订单事实表
案例背景 假设我们正在为一家电子商务公司设计一个订单事实表该公司希望分析销售数据以优化库存管理和促销策略。
步骤1确定业务需求和事实表的类型
我们与业务团队合作明确了需求分析订单数据包括销售额、订单量、顾客购买行为等。
步骤2进行详细的需求分析
我们分析了订单处理的整个生命周期从顾客浏览商品到最终的订单交付。
步骤3声明粒度
我们选择了订单级别的原子粒度确保每一条记录都对应一个具体的订单事件。
步骤4确定维度
基于订单粒度我们确定了以下维度顾客、时间、产品、促销活动等。
步骤5确定事实
我们确定了以下度量指标订单总金额、订单中商品的总数量、退货数量等。对于不可加的度量如退货率我们进行了适当的转换以便聚合。
步骤6冗余维度
为了提高查询效率我们将一些常用的维度属性如顾客的地区信息退化并直接包含在事实表中。
CREATE TABLE IF NOT EXISTS order_fact_table (order_id INT COMMENT 唯一标识每个订单的ID,customer_id INT COMMENT 下单顾客的ID,order_date DATE COMMENT 订单的日期,product_id INT COMMENT 订单中商品的ID,quantity INT COMMENT 订单中商品的数量,unit_price DECIMAL(10, 2) COMMENT 商品的单价保留两位小数确保金额的精确度,total_amount DECIMAL(15, 2) COMMENT 订单的总金额保留两位小数适用于大金额订单,return_quantity INT COMMENT 订单的退货数量默认为0表示没有退货,promotion_id INT COMMENT 订单参与的促销活动ID如果有的话,customer_region STRING COMMENT 顾客所属的地区使用字符串存储
)
COMMENT 订单事实表存储订单相关的详细数据
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ,
LINES TERMINATED BY \n
STORED AS TEXTFILE;
4. 事务类型
4.1 单、多事务事实表
单事务事实表结构简单易于管理适用于单一且独立的业务记录。多事务事实表则适用于复杂的业务场景能够记录多个相关联的事务但设计和理解上更为复杂。
特性单事务事实表多事务事实表业务过程一个多个粒度相互间不相关相同粒度维度相互间不相关一致事实只取当前业务过程中的事实保留多个业务过程中的事实非当前业务过程中的事实需要置零处理理解程度易于理解不会混淆难以理解需要通过标签来限定计算存储成本较少不同业务过程融合到一起降低了存储计算量但是非当前业务过程的度量存在大量零值较多每个业务过程都需要计算存储一次
4.2 其他事实表
事务事实表适用于记录具体事务的瞬间数据周期快照事实表用于定期捕获数据状态而累积快照事实表则追踪业务过程的完整历史提供连续的数据视图。每种表根据其更新和加载机制服务于不同的数据分析需求。
特性事务事实表周期快照事实表累积快照事实表时期/时间点离散事务时间点以有规律的、可预测的时期间隔产生快照用于时间跨度不确定的不断变化的工作流日期维度事务日期快照日期相关业务过程涉及的多个日期粒度每行代表一个事务每行代表某时间周期的一个实体每行代表一个实体的生命周期事实事务事实相关业务过程事实和时间间隔事实事务事实、累积事实事实表加载插入插入插入与更新事实表更新不更新不更新业务过程变更时更新
5. 写在最后
在本章我们细致地构建了对事实表这一数据仓库核心元素的理解。事实表记录了企业的关键业务数据每条记录都是业务活动的直接反映。
我们首先明确了事实表的基本功能它集中存储了业务度量和事实是数据分析的基础。然后我们学习了如何根据业务需求设计事实表挑选合适的度量并确保通过维度键与维度表的连接为数据分析提供必要的上下文。
我们讨论了事实表的粒度问题这是决定我们分析细节深度的关键。我们还区分了单事务和多事务事实表并探讨了它们在不同业务场景下的应用。
最后我们掌握了一些高级设计原则包括处理null值的策略、避免数据冗余的方法以及通过退化维度来提高查询性能的技巧。这些原则有助于确保事实表的准确性和效率支持有效的数据分析和决策制定。
随着本章的结束希望你对事实表的设计和应用有了更清晰的认识能够更有效地利用这一工具来挖掘数据的潜力为企业带来价值。
