0

I have a Dimension in my DW in which 4 columns together forms the business key ,so there is no single BK. To insert into the fact table (Staging area) i would need a FK, so should the fact table have the combination of these 4 columns (4 FK) for this specific Dimension or should I create a column "ID identity(1,1) Int" into the source table? Note: This is the staging area I am creating in SSIS, so I assume surrogate keys should only be inserted in DW.

rafamaniac
  • 57
  • 7
  • Are all four columns immutable? If any of them can ever change, I wouldn't repeat them anywhere, and instead rely on a surrogate key like an identity, since that should never need to change. Cascading changes to keys to child tables is a royal pain to do right (and certainly not with the built in `ON CASCADE` functionality). I would worry less about whether not using a _natural_ key violates some study hall notes somewhere. – Stuck at 1337 Oct 24 '22 at 23:01
  • this columns never change, but by creating an identity as a BK in the source table this changes the structural integrity of the table, is this a good approach? should the fact table have the combination of these 4 columns (4 FK) for this dimension? – rafamaniac Oct 24 '22 at 23:12
  • What do you mean by "structural integrity"? Do you mean that if you lost all the identity values and only had the four key columns you wouldn't be able to rebuild the exact same truth? Yes, but there are other ways to accomplish that (e.g. redundant backups). Personally I don't think you should ever repeat a wide key if you can avoid it, but that's probably coming from a different area of concern than your questions. – Stuck at 1337 Oct 24 '22 at 23:15

0 Answers0