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

Modeling a subscription system, what makes sense to handle status changes?

2 posters

Go down

Modeling a subscription system, what makes sense to handle status changes? Empty Modeling a subscription system, what makes sense to handle status changes?

Post  2by4 Mon Jun 25, 2012 12:24 pm

All,

Our business is to sell newsletter subscriptions. As we start to model our processes, it seems that we've hit a roadblock to which I cannot find a good answer. Here is what we're trying to do (bear with my newness to dm please):

We receive orders for subscriptions of the different newsletters. A subscription ORDER (fact) is booked, with corresponding order $, dates, etc.
For the typical subscription a variety of things can happen:
Issues are sent, payment is received etc.

The problem is this: SOME of those orders will be cancelled, should cancellation of an order (plus the date of the cancellation) be
modelled as accumulating facts of the original ORDER fact table? Most orders never get cancelled (we hope), so this is a "status"
change that we want to track, when it happens and if it happens. Should we have a cancelled_orders fact table? The trick here is
that an order can be cancelled, but that can cancel can later be reversed if the person changes their mind. In summary we want to
track the 'status' of an order (active, expired, cancelled, renewed) and the dates for which those things happen, but not all of those
things will happen so accumulating facts does not seem like the best choice.

Any insight into this is much appreciated. I'm happy to provide more detail, but I did not want to make my first post overlong...
Thank you in advance,
Franco

2by4

Posts : 5
Join date : 2012-06-25

Back to top Go down

Modeling a subscription system, what makes sense to handle status changes? Empty Re: Modeling a subscription system, what makes sense to handle status changes?

Post  BoxesAndLines Wed Jun 27, 2012 9:09 am

There is usually a transacation fact associated with an accumulating snapshot fact. You will capture the cancel in the order status transaction fact and you could capture the cancel status in the accumulating snapshot fact. I would not create a specific canceled orders fact table though. How often a cancel occurs isn't really a concern.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Modeling a subscription system, what makes sense to handle status changes? Empty Re: Modeling a subscription system, what makes sense to handle status changes?

Post  2by4 Wed Jun 27, 2012 9:58 am

Thank you, B&L, that seems to make sense. You answered my latent question which was whether I could have the status
in both an accumulated fact form and the individual orderstatus fact table as well.

One other question if I may impose: in my accumulated fact I will have a reference to what amounts to the
'current status' of the order, does it make sense to also have a last_status_change_date in the accumulated
table or should I derive that from the status fact table? I'm thinking the former since it would be very useful
in answering questions off the snapshot such as "when did this person cancel" but I am not sure if this is commonplace
to do.

Thanks again,
Franco

2by4

Posts : 5
Join date : 2012-06-25

Back to top Go down

Modeling a subscription system, what makes sense to handle status changes? Empty Re: Modeling a subscription system, what makes sense to handle status changes?

Post  BoxesAndLines Wed Jun 27, 2012 2:17 pm

If statuses come in sequentially then the current status is simply the latest status date. This is true for the transaction or accumulating snapshot. I've never derived the "current" status since there always has been a workflow associated with the statuses.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Modeling a subscription system, what makes sense to handle status changes? Empty Re: Modeling a subscription system, what makes sense to handle status changes?

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