数仓建模：维度建模中的事实表设计八大原则

会飞的一十六 2024-11-11

222

点击上方【蓝色】字体关注我们

维度建模是一种数据仓库设计方法，其核心是围绕业务过程建立事实表和维度表。事实表主要存储与业务过程相关的度量数据，而维度表则描述这些度量数据的属性。

以下是设计事实表时需要遵循的几个重要原则，来源于《维度建模工具箱》那本书上，结合实际例子进行了说明

01 原则一：尽可能包含所有与业务过程相关的事实

在设计事实表时，应尽量包含所有与业务过程相关的事实。即使存在冗余，由于事实通常为数字型，带来的存储开销不会很大。例如，在销售业务的事实表中，应包含销售金额、销售数量、折扣金额等与销售过程密切相关的所有事实。

示例1：订单下单事实表

该表记录订单下单过程中的相关事实。

CREATE TABLE OrderFact (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    OrderDate DATE,
    OrderAmount DECIMAL(10, 2),
    DiscountAmount DECIMAL(10, 2),
    Quantity INT
);

02

原则二：只选择与业务过程相关的事实

在选择事实时，应注意只选择与当前业务过程相关的事实。例如，在订单下单的业务过程事实表中，不应包含支付金额这种与支付业务过程相关的事实。这样可以确保每个事实表只记录一个业务过程的度量数据，避免混淆。

示例2：支付事实表

该表记录支付过程中的相关事实。

CREATE TABLE PaymentFact (
    PaymentID INT PRIMARY KEY,
    OrderID INT,
    PaymentDate DATE,
    PaymentAmount DECIMAL(10, 2)
);

03

原则三：分解不可加性事实为可加的组件

对于不具备可加性条件的事实，需要将其分解为可加的组件。例如，订单的优惠率无法直接进行加总，因此应将其分解为订单原价金额和订单优惠金额两个事实存储在事实表中。这些可加的组件可以通过聚合操作进行加总，提供更灵活的分析能力。

示例3：处理不可加性事实

将订单优惠率分解为订单原价金额和订单优惠金额两个可加的事实。

CREATE TABLE OrderFact (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    OrderDate DATE,
    OriginalPrice DECIMAL(10, 2),
    DiscountAmount DECIMAL(10, 2),
    Quantity INT
);

计算优惠价格时

  select (OriginalPrice - DiscountAmount) AS FinalPrice

04

原则四：在选择维度和事实之前必须先声明粒度

粒度用于确定事实表中一行所表示业务的细节层次，决定了维度模型的扩展性。在选择维度和事实之前必须先声明粒度，且每个维度和事实必须与所定义的粒度保持一致。设计事实表时，粒度定义得越细越好，建议从最低级别的原子粒度开始，因为原子粒度提供了最大限度的灵活性。例如，销售事实表的粒度可以定义为每个销售事务，而不是每月或每年的汇总数据。

示例4：声明粒度

粒度用于确定事实表中一行所表示业务的细节层次，定义得越细越好。以下示例展示了订单事务的粒度定义为每个订单项。

CREATE TABLE OrderItemFact (
    OrderItemID INT PRIMARY KEY,
    DiscountAmount DECIMAL(10, 2)
    , Quantity INT
);

05

原则五：在同一个事实表中不能有多种不同粒度的事实

事实表中的所有事实需要与表定义的粒度保持一致，不同粒度的事实不能混在同一个事实表中。例如，机票支付成功事务事实表的粒度为票级，一个订单可以包含多张机票。因此，票支付金额和票折扣金额可以存储在同一个事实表中，而订单支付金额和订单票数则不适合存储在该表中，因为它们的粒度是订单级，与票级不一致。

示例5：同一事实表中不同粒度的处理

CREATE TABLE TicketPaymentFact ( 
TicketID INT PRIMARY KEY, 
OrderID INT, 
TicketPrice DECIMAL(10, 2), 
TicketDiscount DECIMAL(10, 2), 
PaymentDate DATE );

06

原则六：事实的单位要保持一致

对于同一事实表中的事实，其单位应保持一致。例如，订单原金额、订单优惠金额和订单运费金额这三个事实，应该统一使用元或分为单位，以便于后续的计算和分析。

示例6：保持单位一致

在创建事实表时，确保所有金额字段使用相同的单位（例如元）。

CREATE TABLE FinancialFact (
    TransactionID INT PRIMARY KEY,
    TransactionDate DATE,
    OriginalAmount DECIMAL(10, 2),
    DiscountAmount DECIMAL(10, 2),
    ShippingCost DECIMAL(10, 2),
    TotalAmount AS (OriginalAmount - DiscountAmount + ShippingCost) PERSISTED
);

07

原则七：对事实的null值要处理

在事实表中，对于度量值为null的情况，建议用零值填充。这是因为在数据库中，null对常用的数字型字段SQL过滤条件如大于、小于等操作都不生效。用零值填充可以避免这些问题，并简化查询操作。

示例7：处理null值

在插入数据时，用零值填充null值。

INSERT INTO OrderFact (OrderID, ProductID, CustomerID, OrderDate, OriginalPrice, DiscountAmount, Quantity)
VALUES (1, 101, 1001, '2024-06-01', 100.00, COALESCE(NULL, 0), 1);

08

原则八：使用退化维度提高事实表的易用性

在维度建模中，通常按照星形模型的方式设计事实表和维度表。
在大数据领域，为了减少用户查询时关联多个表的操作，可以在事实表中存储常用的维度信息，即使用退化维度。
例如，在销售事实表中，可以直接存储产品类别和客户地区等信息，而不是通过外键关联维度表。这种设计可以减少计算开销，提高查询效率

示例8：使用退化维度

在销售事实表中直接存储产品类别和客户地区等信息，以减少查询时的表关联操作。

CREATE TABLE SalesFact (
    SalesID INT PRIMARY KEY,
    ProductID INT,
    ProductCategory VARCHAR(50),
    CustomerID INT,
    CustomerRegion VARCHAR(50),
    SalesDate DATE,
    SalesAmount DECIMAL(10, 2),
    DiscountAmount DECIMAL(10, 2),
    Quantity INT
);

综上所述，事实表的设计需要遵循以上原则，确保表中存储的数据准确、完整且易于分析。这些原则在实际应用中具有很强的指导意义，有助于构建高效、灵活的数据仓库。大数据开发可以更好地理解和应用这些原则，提升数据仓库的设计质量和使用效率。

往期精彩

基于SQL语言实现的一种二次指数平滑法构建的线性预测模型 | 纺织生产布料年产量预测

数据同步工具Sqoop原理及优化

数仓规范：命名规范如何设计？| 规范2

会飞的一十六

扫描右侧二维码关注我们

点个【在看】你最好看

数据粒度业务建模维度粒度

文章转载自会飞的一十六，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。