构建数据仓库星型Schema：事实表填充与维度表连接方案咨询

一、解决事实表填充的核心误区与SCD Type2关联问题 #

你当前的核心误区是试图让维度表之间直接连接来填充事实表——这是错误的逻辑。正确的做法是从OLTP的事实源数据（而非维度表之间）关联各维度表，步骤如下：

1. 事实表填充的正确逻辑

   • 事实表的数据源是OLTP中的业务事实记录（比如订单明细、交易记录），这些记录本身携带了关联各维度的业务键（比如订单里的customerNumber、productCode、employeeNumber）。
   • 不需要维度表之间互相连接，而是用OLTP事实记录中的每个业务键，分别关联对应的维度表，获取维度的代理键，再将代理键+事实度量存入事实表。

2. SCD Type2场景下的OLTP数据关联方法
   当维度表采用SCD Type2（保留历史版本）时，必须依赖OLTP事实记录中的事件时间戳（比如订单日期、交易时间）来匹配维度的正确版本：

   • 维度表需维护SCD2字段：start_date（版本生效日期）、end_date（版本失效日期，用9999-12-31表示当前生效版本）、is_current（标记是否为当前版本）。
   • 关联时，用OLTP事实的业务键匹配维度表的业务键，同时添加时间条件：事实时间戳 BETWEEN 维度.start_date AND 维度.end_date，以此获取该时间点对应的维度版本代理键。

   伪SQL示例（以订单事实为例）：

   INSERT INTO fact_order_line (
       customer_sk, product_sk, employee_sk, date_sk,
       quantity_ordered, price_each, order_number, order_line_number
   )
   SELECT
       c.customer_sk,
       p.product_sk,
       e.employee_sk,
       d.date_sk,
       od.quantityOrdered,
       od.priceEach,
       o.orderNumber,
       od.orderLineNumber
   FROM oltp.orderdetails od
   JOIN oltp.orders o ON od.orderNumber = o.orderNumber
   JOIN dim_customers c 
       ON o.customerNumber = c.customerNumber
       AND o.orderDate BETWEEN c.start_date AND c.end_date
   JOIN dim_products p 
       ON od.productCode = p.productCode
       AND o.orderDate BETWEEN p.start_date AND p.end_date
   JOIN dim_employees e 
       ON o.salesRepEmployeeNumber = e.employeeNumber
       AND o.orderDate BETWEEN e.start_date AND e.end_date
   JOIN dim_dates d ON o.orderDate = d.full_date;
   

二、基于你的OLTP表构建星型Schema的具体步骤 #

结合你设定的维度（Customers、Products、Offices、Employees）和事实字段，完整构建流程如下：

1. 设计维度表 #

（1）维度通用规则

• 每个维度表添加代理键（如customer_sk）作为主键，保留OLTP业务键（如customerNumber）用于关联。
• 对需要保留历史变化的维度采用SCD Type2，稳定维度可采用SCD Type1（直接覆盖更新）。

（2）各维度表设计

• dim_customers（客户维度）

  • 字段：customer_sk（代理键，自增/序列）、customerNumber（业务键）、customerName、contactLastName、contactFirstName、addressLine1、city、country、creditLimit、start_date、end_date、is_current
  • SCD类型：Type2（客户信息如地址、信用额度可能变化）

• dim_products（产品维度）

  • 字段：product_sk、productCode（业务键）、productName、productLine、productScale、productVendor、quantityInStock、buyPrice、start_date、end_date、is_current
  • SCD类型：Type2（产品价格、库存分类可能变化）

• dim_offices（办公室维度）

  • 字段：office_sk、officeCode（业务键）、city、country、postalCode、territory、start_date、end_date、is_current
  • SCD类型：Type1或Type2（办公室地址变化频率低，若很少变动用Type1）

• dim_employees（员工维度）

  • 字段：employee_sk、employeeNumber（业务键）、lastName、firstName、jobTitle、officeCode（关联办公室业务键，或直接存储办公室属性作为退化维度）、start_date、end_date、is_current
  • SCD类型：Type2（员工职位、所属办公室可能变化）

• dim_dates（日期维度，必备补充）

  • 预生成所有可能的日期记录，字段：date_sk、full_date、year、quarter、month、day、day_of_week等，用于时间维度分析。

2. 设计事实表 #

（1）事实表粒度

选择订单明细行作为事实粒度（原子级粒度，支持最细维度的分析）。

（2）事实表字段

• 代理键：customer_sk、product_sk、employee_sk、date_sk（若需直接按办公室统计，可添加office_sk，否则通过员工维度关联）
• 事实度量：quantity_ordered（可加和度量）、price_each（可加和/平均度量）、line_total（派生度量，quantity_ordered * price_each，可选）
• 退化维度：order_number、order_line_number（用于回溯OLTP原始订单）

3. ETL加载流程 #

1. Staging区加载：将OLTP的customers、products、offices、employees、orders、orderdetails全量/增量抽取到staging区，清洗脏数据（如空值、格式错误）。
2. 维度表加载：
   • 对SCD Type2维度，对比staging数据与现有维度数据，新增版本记录（更新旧版本的end_date，插入新版本并设置start_date为当前加载日期）。
   • 对SCD Type1维度，直接覆盖更新变化的字段。
3. 事实表加载：按照前文的伪SQL逻辑，从staging的订单、订单明细数据关联各维度表，获取代理键后插入事实表。

内容的提问来源于stack exchange，提问作者imran