Subscription Services - Timespan Fact Table
3 posters
Page 1 of 1
Subscription Services - Timespan Fact Table
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.
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
Re: Subscription Services - Timespan Fact Table
Any thoughts on this one?
ccioffi- Posts : 7
Join date : 2014-03-28
Re: Subscription Services - Timespan Fact Table
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,
Regards,
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Subscription Services - Timespan Fact Table
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Timespan accumulating fact table
» CRM DW, measuring product and services subscription
» Transaction fact table and Sql server analysis services calculated measure
» Timespan Transaction Fact
» Subscription Orders Fact
» CRM DW, measuring product and services subscription
» Transaction fact table and Sql server analysis services calculated measure
» Timespan Transaction Fact
» Subscription Orders Fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum