Subscription Services - Timespan Fact Table

View previous topic View next topic Go down

Subscription Services - Timespan Fact Table

Post  ccioffi on Thu Oct 16, 2014 10:45 pm

We provide subscription services, where customers subscribe to a product and pay a monthly fee.  A customer can add/remove services at any point of the day, and we have a few million customer base.  I will be using a transaction fact table to record the addition/removal of services from customer accounts.  

The question is on the best method for maintaining a snapshot so that we can meet the requirement such as number of customers by service by day (or even as of x moment in time) or sum of recurring revenue by geography by day (or even as of x moment in time).

A periodic snapshot would not work in this case as I would at the very least need to make a daily snapshot which at millions of customers base seems it would get a bit large and I may be having performance issues a few months in.

Does a time-span fact table, with an effective and expiration date make sense here?  This is the way I am leaning, especially since I have been tempted to put the subscribed services in the customer dimension and track changes via type 2.

Regarding putting the subscribed services in the customer dimension, I have considered this because there are a lot analysis where business wants to know "number of customers that subscribe to x product that called into the call center *or replace called into the call center with a multitude of other business processes* and with the customer key in the multiple business process fact tables I would be able to slice those facts by the customer's subscribed services.

ccioffi

Posts : 7
Join date : 2014-03-28

View user profile

Back to top Go down

Re: Subscription Services - Timespan Fact Table

Post  ccioffi on Mon Oct 20, 2014 8:04 am

Any thoughts on this one?

ccioffi

Posts : 7
Join date : 2014-03-28

View user profile

Back to top Go down

Re: Subscription Services - Timespan Fact Table

Post  nick_white on Mon Oct 20, 2014 9:24 am

Why not amend/re-purpose your transaction fact table so that you have a single record per customer/service? If it has start/end dates (either as date fields directly in the fact or as FKs to your Date Dim) you can then use this to query for all customers who have a particular service at a point in time - and use the result directly or as a filter when querying other fact tables that have FKs to customers

Regards,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Subscription Services - Timespan Fact Table

Post  BoxesAndLines on Mon Oct 20, 2014 9:42 am

Do both, a transaction fact to capture ins and outs and a daily snapshot with a current view. You don't have to keep all the daily snapshots. Keep just the month end snapshot and possibly 30 days worth of dailies.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Subscription Services - Timespan Fact Table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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