SSIS暂存区事实表外键设计：复合业务键VS新增标识列？

核心结论 #

优先在数据仓库的维度表中新增代理键（ID identity(1,1) Int），用这个代理键作为事实表的外键——这完全符合你“代理键仅应在数据仓库中添加”的原则，也是数据仓库设计的标准做法。

三种方案的利弊分析 #

方案1：用4列复合业务键作为事实表外键

• 优点：无需修改任何表结构（源表、DW维度表），完全遵循现有业务键逻辑。
• 缺点：
  • 事实表需要额外存储4列外键，数据量较大时会显著占用存储空间；
  • 关联查询时需同时匹配4列，性能比单键关联差；
  • 若维度表的业务键出现极端变更（如业务规则调整导致某列值修改），需同步更新事实表中所有对应的4列数据，维护成本极高。

方案2：在源表新增自增ID（不推荐）

• 违背你“代理键仅在DW中添加”的原则，且侵入源系统结构，可能影响源业务系统的稳定性和扩展性；
• 源表的自增ID无法保证在DW维度表中的唯一性映射（比如源数据重复加载、业务键变更时），容易导致数据关联错误。

方案3：在DW维度表新增代理键（推荐）

• 完全符合你的原则：仅在数据仓库内添加代理键，不触动源系统；
• 具体操作：
  1. 在DW的维度表中新增ID identity(1,1) Int作为代理键，设置为主键；
  2. 在SSIS暂存区处理时，通过事实表暂存数据中的4列业务键，关联DW维度表的4列业务键，获取对应的代理键ID；
  3. 将代理键ID插入事实表作为外键。
• 优点：
  • 事实表仅需存储1个整数列，大幅节省存储空间；
  • 单键关联查询性能远优于复合键；
  • 维度业务键变更时，仅需在维度表中维护代理键与新业务键的映射，事实表无需修改，维护成本极低。

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