Modeling a subscription system, what makes sense to handle status changes?
2 posters
Page 1 of 1
Modeling a subscription system, what makes sense to handle status changes?
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
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
Re: Modeling a subscription system, what makes sense to handle status changes?
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Modeling a subscription system, what makes sense to handle status changes?
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
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
Re: Modeling a subscription system, what makes sense to handle status changes?
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» CRM DW, measuring product and services subscription
» Is it a good idea to have a single STATUS table if we have to store multiple status in the data mart?
» Status Code vs Status Description
» New at BI and data warehousing and trying to make sense of it all.
» Subscription Orders Fact
» Is it a good idea to have a single STATUS table if we have to store multiple status in the data mart?
» Status Code vs Status Description
» New at BI and data warehousing and trying to make sense of it all.
» Subscription Orders Fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|