Sample Header Ad - 728x90

Fact table linking to natual key rather than row key in a slowly changing dimension table?

0 votes
1 answer
282 views
I’m designing a data warehouse and have a Slowly Changing Dimension Type 2 table something like this: UserKey | UserID | Group | EffectiveDate | ExpiryDate | IsCurrent | ---------|--------|----------|---------------|------------|-----------| 1 | 1001 | GR1 | 2021-01-01 | 9999-12-31 | Y | 2 | 1002 | GR1 | 2021-07-31 | 2022-02-28 | N | 3 | 1002 | GR2 | 2022-28-28 | 9999-12-31 | Y | And I have a fact table, which whilst it does have dates, doesn't really relate to the user dimension at any particular point in time, say a subscriptions table like this: SubsKey | SubsID | SubsType | UserKey / UserId ? | StartDate | EndDate ---------|--------|----------|-------------------| -----------|------------| 55501 | SBP501 | Premium | ??? / 2 | 2021-08-01 | 2022-08-01 | 55502 | SBB123 | Bonus | ??? / 2 | 2022-08-01 | 2022-09-01 | The subscriptions could be set up for dates in the future for example, or be changed retrospectively. Or alternatively we might have a fact table which doesn't have any date aspect at all. In such cases is it reasonable for the fact table to reference the natural key UserID instead of the type 2 dimension row key UserKey? As it's not obvious which UserKey ought to be used. I suppose one might use whichever row key is current at the time the fact row is generated, but that would seem to be implying that the fact relates to a particular snapshot in time for the user, when in reality the fact doesn't really.
Asked by stovroz (631 rep)
Apr 6, 2022, 08:27 PM
Last activity: Apr 6, 2022, 09:58 PM