Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Transaction Fact or periodic snapshot fact

3 posters

Go down

Transaction Fact or periodic snapshot fact Empty Transaction Fact or periodic snapshot fact

Post  JSchroeder Fri Jul 12, 2013 10:56 am

I have a fact table Used to hold monthly recurring revenue for a subscription based business. To populate the table, I Create a record on the first of every month for each month during the life of the transactions which holds the total amount of the subscription divided by the numer of months. So if the subscriptions is for $100 and for 5 months, the MRR is 100/5 or $20. % records are added to the Fact table with a date on the first of every month and a value of $20. Would this type of table generally be considered a Transaction fact table or a periodic snapshot fact table?

JSchroeder

Posts : 12
Join date : 2012-03-29

Back to top Go down

Transaction Fact or periodic snapshot fact Empty Re: Transaction Fact or periodic snapshot fact

Post  ngalemmo Fri Jul 12, 2013 12:23 pm

Its a transaction as the measures are incremental.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Transaction Fact or periodic snapshot fact Empty Re: Transaction Fact or periodic snapshot fact

Post  JSchroeder Fri Jul 12, 2013 3:26 pm

OK, thanks. I've been thinking of the table as a snapshot as it measures, for a point in time, what the MRR values are.

JSchroeder

Posts : 12
Join date : 2012-03-29

Back to top Go down

Transaction Fact or periodic snapshot fact Empty Re: Transaction Fact or periodic snapshot fact

Post  BoxesAndLines Fri Jul 12, 2013 4:23 pm

Do you sum metrics across the dates (snapshots)? If yes, transaction fact. If not, snapshot fact. Each snapshot stands on its own as a complete picture.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Transaction Fact or periodic snapshot fact Empty Re: Transaction Fact or periodic snapshot fact

Post  JSchroeder Fri Jul 12, 2013 4:39 pm

No I do not sum across dates. That's why I've been thinking snapshot. The measure is semi-additive in analysis services. When viewing for the year, I take the last value for the year not the sum for the year.

JSchroeder

Posts : 12
Join date : 2012-03-29

Back to top Go down

Transaction Fact or periodic snapshot fact Empty Re: Transaction Fact or periodic snapshot fact

Post  ngalemmo Fri Jul 12, 2013 6:22 pm

Well, hold on... I was going to use B&L's comment, but thought about it a bit more...

The distinguishing factor between a transactional fact and a snapshot fact to me is how it is updated.  A transactional fact is insert only, each new row represents an incremental change to whatever the facts represent.  A snapshot, on the other hand, is updated in place.  Each row represents a complete picture of whatever.  

But, does that mean it's not summable across time?  Take an example of an order line snapshot table.  It contains one row per order line and its a snapshot because rows are updated in place.  Such a table can be and is summed across time.

In your case, each row represents an incremental change in subscription revenue.  If you want total revenue for a year, for example, you would sum all rows for that year.  So, the data is summable across time.  What makes it transactional is that, I assumed, you would not go back an retroactively update a row.  If you do, its a snapshot.  If you instead insert adjusting entries, such as when someone cancels a subscription, it is transactional.

I don't understand the comment "When viewing for the year, I take the last value for the year not the sum for the year." If you have 5 rows with $20 each, why would you not sum it? If someone had a gap in their subscription during the year (say 3 months, a gap, and another 3 months) how would looking at the last row give you the total for the year?
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Transaction Fact or periodic snapshot fact Empty Re: Transaction Fact or periodic snapshot fact

Post  JSchroeder Fri Jul 12, 2013 6:50 pm

|why would you not sum it?

In analyzing monthly recurring revenue, so far we have not needed to sum the MRR for a given period. We are concerned with MRR at a point in time, for a given month and comparing it to MRR at  a point in time for the next month.  A typical report would show MRR for say June 1st 2013, The new MRR from newly acquired customers the expanded MRR from existing customers who increased their value, minus the lost MRR from lost cutomers, minus the lost MRR from contracting customers and finally the MRR for July 1st 2013. MMR1 + MMR New +  MRR Expanded -  MRR Lost - MRR Contracted = MRR2. Subscription based businesses present a set of challenges that aren't easily answered with a typical transaction based system.  The health of the company is monitored by tracking changes in MRR as opposed to summing sales transactions for a given period.  It's an interesting topic that has not been fully explored.

JSchroeder

Posts : 12
Join date : 2012-03-29

Back to top Go down

Transaction Fact or periodic snapshot fact Empty Re: Transaction Fact or periodic snapshot fact

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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