Picking the right dimenstion key and locating the associated facts

I have two fact tables. One that loads all detail payments (payment_fact) and the second the counts the successful payments (basically speaking) (Credit_fact). I have both fact tables associated with the same SCD2 subcriber_dimension. For every successful payment (some other business criteria applied), the credit count in Credit_fact is incremented by 1.

My subscriber_dim has a record:
sub_key=1
Acct=A,
Status=A
eff_date=01/01/2011
exp_date=null/current

My Credit Fact has a record:
Sub_key=1
Credit_Count=3
eff_dt = 4/1/2011
exp_dt = null/most current record

The subscriber status changes on 4/15, so we expire where sub_key=1 and add a new record to SCD2 subscriber_dim:
orig:
sub_key=1
Acct=A,
Status=A
eff_date=01/01/2011
exp_date=04/15/2011

new:
sub_key=2
Acct=A,
Status=S
eff_date=04/15/2011
exp_date=null/current

Now, I load a new payment on 4/15 into the Payment_Fact and associate that payment with SCD2 sub_key=2. The payment is successful, so I want to increment the Credit_count in Credit_Fact. I need to locate the last record for the same subscriber in the Credit_Fact to not only expire that record, but o allow obtain the Credit_Count so that I can increment it by 1 for the new successful payemnt.

Question: How do I find the Credit_Fact record where Credit_Count=3 for this same subscriber in an efficient manner now that they are no longer associated with the same sub_key? In otherwords, how do I know that the previous record for the subscriber had a Credit_Count = 3, so now I need to insert a the record in the Credit_fact and set Credit_Count = 4?

Table structures:
Subscriber_Dim
Sub_Key
Account_ID
Phone_Number
Status
Eff_Date
Exp_Date

Sub_Credit_Fact
Sub_Key
Credit_Count
Eff_Date
Exp_Date

Hi, I think your issue is in the granularity of the Credit Fact. As I understand it it would currently be defined as "an accumulating total of successful payments (credits) per subscriber" . If you changed it to be "a record for each successful payment per subscriber" meaning you will write a record each time a subscriber makes a successful payment. So in the situation you describe you'll have 3 rows (each with a count of 1) with a sub_key = 1 and when the 4th successful payment is made you'll write another row for the subscriber with a sub_key of 2. To get your accumulating total you query the subscriber dimension using the business code and join to the Credit Fact using the surrogate key and sum the count.

rob.hawken wrote:Hi, I think your issue is in the granularity of the Credit Fact. As I understand it it would currently be defined as "an accumulating total of successful payments (credits) per subscriber".

I see your point, but it's not truly and accumulating snapshot since the credit count can be reset upon subscriber status changes, therefore it's not as straight forward as summing the credit_counts for all records. A sum would give the incorrect answer. However, I could have Credit_Count=1 for each row and when the credit_count is reset use Credit_Count= -6 (negative 6 which would equal the reset). Definitely something to consider.

ksnead wrote:I see your point, but it's not truly and accumulating snapshot since the credit count can be reset upon subscriber status changes, therefore it's not as straight forward as summing the credit_counts for all records. A sum would give the incorrect answer. However, I could have Credit_Count=1 for each row and when the credit_count is reset use Credit_Count= -6 (negative 6 which would equal the reset). Definitely something to consider.

Spoken like a true ER modeler... you are too hung up on keys. If I wanted to get counts based on status, then, I would get correct counts for a subscriber for a status. If I do not include status I get a correct count for a subscriber.

If I want a count for a subscriber and show his current status, I self join on the dimension to get the current version of the subscriber. I still get the correct count.

You are not 'resetting' the count. You are simply maintaining a count in context. Queries against fact tables are ALWAYS aggregate queries. The query controls the context for the measures. Don't do the negative thing... its a bad idea.