源系统覆盖维度历史场景下的维度建模方案咨询

针对你遇到的销售人员ID不稳定、源系统覆盖历史分配记录的场景，核心方案是结合Type 2缓慢变化维度（SCD）与销售人员映射桥接表，同时保留星型模型的分析友好性，不采用雪花模型。以下是针对你问题的具体解答：

1. 是否应采用Slowly Changing Dimension (Type 2)处理？ #

是，必须采用Type 2 SCD，但需要补充回溯逻辑。

• 源系统虽未保留历史分配记录，但可以通过组织变更时间（如2024年1月1日）和交易日期，回溯生成Salesperson维度的历史版本：
  • 为2023年的销售人员A（ID:1234）、B（ID:5678）创建维度记录，标记生效日期为最早交易日期，失效日期为2023-12-31，当前标志为N；
  • 为合并后的新ID（7654）创建维度记录，生效日期为2024-01-01，失效日期为9999-12-31，当前标志为Y。
• Type 2 SCD的核心价值是保留交易发生时的销售人员快照，确保历史事实的准确性，避免歧义。

2. 采用历史与当前销售人员的桥接/映射表是否更合适？ #

桥接表是必要补充，与Type 2 SCD配合使用。

• 桥接表用于建立历史销售ID到当前组织架构的映射关系，结构示例：

  历史销售ID	当前销售ID	生效日期	失效日期
  1234	7654	2024-01-01	9999-12-31
  5678	7654	2024-01-01	9999-12-31

• 这个表的作用是支持按当前组织架构汇总历史交易的需求：比如统计合并后团队的全量历史业绩，无需改写事实表的历史数据。

3. 是否合理采用snowflaking来推导“当前所有者”？ #

不建议采用雪花模型。

• 雪花模型会增加报表查询的复杂度（多表关联），违背星型模型为分析场景优化的初衷；
• 替代方案：
  • 在Salesperson维度中增加当前归属销售ID属性，直接标记历史版本对应的当前销售实体；
  • 或者通过事实表关联Salesperson维度后，再关联桥接表获取当前销售ID，全程保持星型模型的核心结构（事实表直接关联所有维度）。

具体实施步骤 #

1. 重构Salesperson维度为Type 2 SCD
   • 新增字段：生效日期、失效日期、当前标志、代理键（替代业务键作为事实表关联的主键）；
   • 回溯生成历史维度记录，基于组织变更时间划分版本周期。
2. 构建销售人员映射桥接表
   • 维护历史销售ID到当前销售ID的映射关系，随组织架构变更更新。
3. 优化事实表与维度关联
   • 事实表保留原始的历史销售业务键，同时关联Salesperson维度的代理键（对应交易发生时的版本）；
   • 若需查询当前所有者，通过桥接表或维度内的当前归属销售ID属性关联。
4. 处理Policy/Client维度的当前关联
   • 在Policy和Client维度中新增当前销售ID属性，或通过桥接表关联到当前销售人员维度记录，保持维度的扁平化。

支持的分析场景 #

• 历史视角分析：事实表关联Salesperson维度的历史版本，查看交易发生时的销售人员信息；
• 当前架构分析：通过桥接表将历史销售ID映射到当前销售ID，汇总全周期业绩。

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