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.