Billing System Transaction fact table

View previous topic View next topic Go down

Billing System Transaction fact table

Post  jmagana on Sat Aug 17, 2013 9:30 pm

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?

jmagana

Posts : 5
Join date : 2010-11-29
Location : California

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  BoxesAndLines on Sun Aug 18, 2013 8:43 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  jmagana on Mon Aug 19, 2013 5:27 pm

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"?

jmagana

Posts : 5
Join date : 2010-11-29
Location : California

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  BoxesAndLines on Mon Aug 19, 2013 8:28 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  jmagana on Mon Aug 19, 2013 11:05 pm

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'.

jmagana

Posts : 5
Join date : 2010-11-29
Location : California

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  BoxesAndLines on Tue Aug 20, 2013 9:01 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  VHF on Tue Aug 20, 2013 4:58 pm

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  VHF on Tue Aug 20, 2013 5:10 pm

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...

BilledAmountRetryCollected
$9.991$0.00
$0.002$0.00
$0.003$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?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  BoxesAndLines on Tue Aug 20, 2013 5:12 pm

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. :-)
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  jmagana on Wed Aug 21, 2013 7:07 pm

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?

jmagana

Posts : 5
Join date : 2010-11-29
Location : California

View user profile

Back to top Go down

Re: Billing System Transaction fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum