Loading a Fact Table with SCD2

[ksnead]

I would love some design input!!

I have a dimension table for Subscriber and Fact table that calculates the credits a subscriber earns based on the number of successful payments (add'l business criteria applied). Note: There is a separate fact that loads all of the payment details for successful and unsuccessful payments). In my fact table, the subscriber has a current credit count = 3 for sub_key=1 (naturual key/subscriber account = A) as of 4/1. On 4/10 the same subscriber's status changes changes from Active to Suspended, so I expire the record (sub_key = 1) I add a new sub_dim record (sub_key=2) for Acct = A with eff_date = 4/10. I have two issues: 1) Since I have millions of subscribers, I don't want the volumne of my Subscriber_Dim to get out of hand and slow down performance, and 2) My fact record didn't changed, credit count still = 3 (no change in payment credits), howver the fact record is now associated with an expired record.

Questions:
1) Is it reasonable to have a fact associated with an expired dimension record even if the fact is a current fact (I hope you followed this one! LOL!)
2) Should I expire the current fact and create a new fact record and associated assign sub_key=2 (new sub_dim record)?
3) There are two attributes in Subscriber_Dim that are Type 2: Phone_Number and Status. Should I pull these type 2 attributes out and create a new dimension "Sub_Phone_Sts_Dim", change Subscriber_Dim to a Type 1 dimension? Then create a factless fact tabe to associated the Sub_Phone_Sts_Dim with the Subscriber_Dim to track Phone and Status change history?
4) If none of the above, what would be a better design for this scenario???

Subscriber_Dim
Sub_Key
Account_ID
Phone_Number
Status
Eff_Date
Exp_Date

Sub_Credit_Fact
Sub_Key
Credit_Count
Eff_Date
Exp_Date

[ngalemmo]

It is not 'expired', it is simply superceeded. The point of a type 2 is to maintain such historical change. If you don't want change history, don't implement a type 2.

The fact/dimension relationship is when the fact was created. If you need to restate facts using current dimension context, you self-join the dimension on natural key to locate the current row. (There are other techniques covered in other threads).

If you want to maintain a history of just a few attributes, you can maintain a type 1 dimsion as well as another fact table that tracks history of the attributes you are concerned about.

I would not add new dimensions to the existing fact, nor would I create new fact rows. The former doesn't change anything and the latter effectivly changes the nature of the dimension to a type 1.

[ksnead]

Thanks so much!!! This is what my gut was telling me, but after talking to some other folks I started to doubt my design. I appreciated the confirmation!

One follow-up:

If you need to restate facts using current dimension context, you self-join the dimension on natural key to locate the current row. (There are other techniques covered in other threads).

Could you tell me what the other thread topics might be so that I could research the mentioned query techniques.

Tahnks again!!!

[Sponsored content]