Billing System Transaction fact table

I am developing a billing system transaction fact table, which has the number of payment processing attempts as a fact. So a customer who was bill $9.99 for subscription X by vendor Y had two unsuccessful billing attempts so the fact table transaction line would have the facts of AMOUNT=$9.99, RETRIES=2.

My Question is it better for me to write a new fact record or update the existing record when for example the "retry count" changes?

You're mixing the grain. Adding another row for the second retry will double the subscription amount. Updates are really slow. The appropriate solution is to create a fact table for each grain that you have.

So since this is a billing system and we handle the payment processing we get successful attempts, n retries, and partial and full refunds. Each of these should be broken out into its own fact table? So if I created a "Retry Fact Table", do I create a new record each time this value goes up or just update the "retry Count"?

Yes, a row is a retry event. You insert one row for each event. Counting events is simply summing the retry count (in this case it will always be 1). You can aggregate this fact to the billing snapshot fact table to get all measures in one fact table at the same grain.

The retry count is incremented in the production code and stored as the aggregate retry count. So if I created a separate table, I would be storing the aggregated total of retries. This would make the value non-additive, unless I just added a dummy column of '1'.

Got it. Forget all the other stuff I said and build a daily snapshot fact table. Everything is an insert every day. Be sure to partition on date.

BoxesAndLines wrote:Got it.  Forget all the other stuff I said and build a daily snapshot fact table.  Everything is an insert every day.  Be sure to partition on date.

I don't understand how a daily snapshot is applicable here.  These sound like transactional facts. Please explain.

Although it introduces the complexity of multiple fact tables, I liked your first answer better--two fact tables at different grains, one for subscription amounts and one for billing attempts.

jmagana wrote:I am developing a billing system transaction fact table, which has the number of payment processing attempts as a fact.  So a customer who was bill $9.99 for subscription X by vendor Y had two unsuccessful billing attempts so the fact table transaction line would have the facts of AMOUNT=$9.99, RETRIES=2.

My Question is it better for me to write a new fact record or update the existing record when for example the "retry count" changes?

As has been mentioned, it is important to be very clear on the grain of a transactional fact table.  Mixed grains get you into trouble.  It seems that you have two types of transactons represented here--a billing amount, and a payment processing attempt.  This could be modeled as two separate fact tables, but let's see if there is a way to model it in a single fact table without introducing erroneous aggregations.

Let's try this...

BilledAmount	Retry	Collected
$9.99	1	$0.00
$0.00	2	$0.00
$0.00	3	$9.99

Note that only the first row records the billed amount.  It is forced to zero for subsequent attempts so as not to overstate the billed amount when aggregating.  The Collected measure indicates the amount that was sucessfully collected on each attempt. Both the BilledAmount and Collected amount measures are aggregatable.

Thoughts?  Pros and Cons?

It looks to me as if the data is not transactional in nature, which is normally the case with billing data. That fact drives the snapshot design. It maybe that some metrics are transactional, some are point in time, hard to tell at this point. If that's the case, my design suggestions could change again. :-)

It looks like the "Transaction" can use a TRANSACTION FACT TABLE  but the "Retry Count" is actually billing information and should go on a separate "Billing" fact table, since a retry cannot be attempted more than once per day.  I was thinking this could be a daily snapshot and the two facts could join on the conformed dimensions.  Refunds could also be a separate fact table since the system allows partial refunds and those could happen at different times.  All this could then be rolled up into an aggregate fact table with all the current information at the "transaction" level.  Does this sound reasonable?