Questions about a subscription based online service

I´m modeling my first star schema and I´m having a hard time figuring out the best way to do it. The schema will be used to track relevant events from a online subscription service, like Netflix for example. Signups and logins were easy enough to model, the subscription not so much...

I need to track events like create, cancel, reactivate, renew and failed to renew. I thought about having different fact tables for each of these events, which seems reasonable.

Originally I wouldn't have a subscription dimension, just a product dimension with details of the subscribed product. However, this approach would complicate the process of listing which subscriptions are currently active, since all the facts would have to be joined (subscriptions that have been created, but not cancelled or expired etc).

So, I added a subscription dimension that has all the dates (create, renew, cancel, reactivate) and some other details, like status and number of times it was renewed, so I could easily browse the dimension and get relevant information. All the subscription facts would use this dimension.

What worried me is the number of date columns in this dimension. It almost seemed like an accumulating snapshot would be better suited, but since the subscription can be cancelled, reactivated and renewed many times, I gave up on the idea.

Also, I’m a little confused about whether I need to carry the dimensions from the create fact to the other facts like renew and cancel.

For example, the renew fact could link only to a date dimension and the subscription dimension mentioned above. However, to answer a question like “where did the users whose subscriptions were renewed today came from”, it would be necessary to join the renew fact to the create fact.

The alternative, I think, is to reuse the same dimensions used in the create fact in the renew fact.

Is this a good approach?

Thanks!

PS: Sorry for my English!

No, it is not a good approach.  What if they canceled, reactivated, canceled, then reactivated again?  What would your dimension look like?

Besides, these actions; subscribing, canceling, reactivating are business events, not context.  They belong in fact tables. I would probably put all these in a single fact.

Hello ngalemmo, thanks for your reply.

Perhaps I didn´t make myself clear - or maybe I just didn’t understand. I see these actions as both events and context.

That´s why I thought of using fact tables for each of them and also a dimension to store all relevant information about the subscription itself - like when was it created, when was it last renewed or last cancelled, how many times it was renewed etc.

The dates in the subscription dimension are all type 1 changes, they would only keep the date of the most recent events.

If I want to track all the events associated with a subscription, I can join all the facts (create, renew, cancel etc, so it wouldn't matter if the subscription was cancelled, reactivated or renewed multiple times.

Is this not ok?

How can I use a single fact table for this? The fact could have various dates as roles for each event, but how would I track recurring events on a single fact?

Regards.

Create a snapshot fact with active subscriptions. Every day, drop the inactives and the actives. Booyeah!

Thanks for your reply BoxesAndLines!

I thought about creating a snapshot fact that would give me the number of active subscriptions per day. Is this what you are suggesting?

The "problem" with the snapshot is that it would not allow me to browse the active subscriptions.

I'm tempted to use ngalemmo's advice, but I'm not sure I fully understand it. It just makes sense in my head to have individual facts for each of the subscription's events, like create, cancel and renew, since some of them can happen multiple times.

However, to me it also makes sense to keep a subscription dimension (linked to each of the subscription facts above) which would contain at least the subscription status and the number of renewals.

I also wanted to keep the creation date and the most recent renew and cancel dates, just to make browsing more relevant, but I guess I can ditch these dates from the dimension.

What is the major problem with this approach? I can't see it, but I don't have the experience. I wish someone could explain the problem so I could learn and better the design.

Thanks again!

Yep, I re-read my post and it was a little confusing. Every day, drop the inactives and keep the actives. Why can't you browse active subscriptions?

The scenario I'm considering is (omitting some dimensions to keep it simple):

- user, product (monthly or annual subscription) and date dimensions
- create, renew, cancel and reactivate facts (linking user, product and date. some facts can happen multiple times)
- snapshot fact with number of active subscriptions per day

Note that I don't have a subscription dimension, but a product dimension shared with every user that subscribes a particular product.

Since the snapshot is only a number, to browse the active subscriptions I would have to join all facts (create, renew, cancel, reactivate) to determine if a subscription is still active.

That's why I wanted to create a subscription dimension (linked to the above facts) with the status, number of times it was renewed and the most recent date each of the facts happened.

I understand this is unorthodox and against the rules, but it's just a mechanism to streamline the answer to some common questions. What I don't understand (and would like to) is how this would hamper the rest of the solution. An alternative solution would also be fine.

Thanks again!

http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=533

Factless fact tables are recommended here.