Subscription as an Accumulating Snapshot Fact or a Dimension?

View previous topic View next topic Go down

Subscription as an Accumulating Snapshot Fact or a Dimension?

Post  Irtaza Hassan on Mon Oct 19, 2009 5:31 am

I am designing a data mart for a subscription based service (my first data modelling effort). Each customer can have multiple accounts and each account can have multiple subscriptions. I have added a Sales Fact table and a Monthly Subscription Snaphot Fact table.

However I am confused about handling of subscriptions. We need to keep track of Registration date, Activation date and Cancellation date to determine the status of each subscription. We also need to track the Training Booking, Training Attendance Date and Training Canecllation date to see which customers have opted for training.

1) Now since these dates will change over a period of months, do I add a Training Dimension and a Subscription Dimension and treat these as SCDs and add new row with new surrogate key for registration/activation/cancellation and training booked/training attended/training cancelled as and when they occur? If yes then would it make analysis of Sales fact based on current status of a subscription difficult as the original Sales fact will have the original surrogate key.

OR

2) Create Subscription and Training as Accumulating Snapshot Fact tables? But that would make analysis of Sales based on Subscription and Training Status too complex or even impossible.

Any help would be really appreciated.

Irtaza Hassan

Posts : 7
Join date : 2009-10-19

View user profile

Back to top Go down

Re: Subscription as an Accumulating Snapshot Fact or a Dimension?

Post  ngalemmo on Mon Oct 19, 2009 11:07 am

It would probably be a combination of things. First you would have a subscription dimension to identify what a subscription represents, as well as a training dimension to identify courses. For sales facts, if the issue is identify what subscriptions are active at the time of the sales, use a multi-valued dimension that represents the group of subscriptions in effect at the time (or, if you simply need to know what subscription applies to that sale, then a simple foreign key to subscription is sufficient.
To keep track of the history of subscriptions maintain a separate fact table for each instance of customer & subscription along with appropriate dates. Same with training history. These would not be snapshots. Add a snapshot if you need to track how many customers have a particular subscription (or group of subscriptions) at a particular point in time, such as at the end of each month.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Subscription as an Accumulating Snapshot Fact or a Dimension?

Post  Irtaza Hassan on Mon Oct 19, 2009 12:44 pm

Thanks for the detailed reply. Let me just give a bit more detail, each Sale has a 1 to 1 relation with a Subscription. The customer wants to see how many subscribers who bought the product have:
i)registered the product,
ii)activated the product
iii)have cancelled the product subscription.
The sales are then analyzed by these different subscription status.

Similarly they also want to know how many subscribers booked a training for the product they bought, attended the training they booked or cancelled the training they booked.

Moreover they also want the ability to drill down and see the date the product was registered, activated or cancelled and similarly they want to drill down to see training booking, attendance and cancellation dates.

I can forget about creating a separate Subscription dimension and put all the dates in the Sales Fact Table as role playing date dimensions. But that would mean the Sales Fact table would be an Accumulating Snaphot and I would have to go back and update the dates as and when they come in. This would result in a lot of reprocessing of the cube.

Irtaza Hassan

Posts : 7
Join date : 2009-10-19

View user profile

Back to top Go down

Re: Subscription as an Accumulating Snapshot Fact or a Dimension?

Post  ngalemmo on Mon Oct 19, 2009 4:39 pm

The sales fact would be a record of transactions, not states. Any time a subscription is sold, or renewed or traning sold or refunded, there would be a row in the table. To maintain state, you keep a snapshot based on the transactions. For a given customer/subscription (and/or training) you maintain the dates (as roles) as well as cumulative measures (revenue, etc...).

I assume you are maintaining all this in a relational DB, so updates should not be a big deal. If you are feeding this to a cube (i.e. a Multi-Dimensional Database), I don't understand why regenerating it or passing it deltas would cause issues. What sort of data volumes are you dealing with?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Subscription as an Accumulating Snapshot Fact or a Dimension?

Post  Irtaza Hassan on Mon Oct 19, 2009 5:48 pm

The total sales as of today are around 1.2 million, which I guess is pretty nominal and even regenrating a cube with this volume shouldn't be a problem.

Just to make sure that I have understood what ou have just said, I'll repeat it in my own words:

1. Keep the Sales Fact as a transaction fact table.
2. Based on the transaction create a Subscription Sanpshot Fact table (weekly or monthly) that'll have the dates as roles and also cumulative measures like revenue, number of payments etc.

Irtaza Hassan

Posts : 7
Join date : 2009-10-19

View user profile

Back to top Go down

Re: Subscription as an Accumulating Snapshot Fact or a Dimension?

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