Subscription Orders Fact

Hi All,

I'm looking to model the process of Customers ordering subscriptions of magazines, as well as the delivery of those magazines to the customer. So e.g. a customer might order a 1 year subscription to say "The Economist" which would entitle them to 52 issues on a weekly basis. They pay up front, then as issue is delivered a partition of the revenue can actually be realised/banked.

The fact tables that I think I need (initially anyway) are Customer Orders and Customer Deliveries. I have 3 problems that I'd appreciate any help on:

1)For orders, the physical business event is the customer picking up the phone (or internet) and placing an order. They order a subscription and pay say £150 up front. Do I model this Orders Fact at the subscription level (i.e. Product dimension would be Economist Subscription as opposed to Economist Issue)? I know that there is some guidance in the Kimball DW Toolkit that says you should allocate order header values down to the order detail level. But my reservations with that are a)I don't think it works that well if you want to get the Orders Count, as you have to do a Distinct Count over the fact table and b)The issues (order line items effectively) have not occurred yet, as they are not delivered.

The problem with not making the grain of the orders table at the Issue level is that it would make it a bit difficult (for what may be a future requirement) to compare sales of subscriptions against sales of single issues that have occurred via retailers. Or maybe I shouldn't worry about that because reseller sales could be argued to be a separate business process?

2)I plan to have a Customer Deliveries fact. This would be at the issue grain, with a Delivery Date dimension. Once a customer receives an issue, then you can bank the issue value, so there would be a measure called Earned Issue Revenue. Does this sound sensible do you think? Again I suppose the key point is what level do model the Product dimension? Do you model Product at the "brand" level, e.g. Economist and then have say a separate degenerate Issue Number dimension, or do you model Product to include Issue Number?

Thank you

The two facts you defined are fine. Keeping orders and fulfillment separate is very common in a lot of industries. Both tables would carry the product subscribed as dimensions and the fulfillment would add the issue being shipped. The order side would also carry other activities, such as cancelation adjustments, related to the order.

Issue would be a separate dimension, either a degenerate or full dimension as appropriate.

Thanks very much for this ngalemmo,

If the Product is at the subscription level, would you see it as a problem if reseller sales are integrated? They would apparently be at the Issue level, so it would be tricky to see how many Issues you are selling via Subscriptions vs Reseller sales for example. Maybe it would be acceptable to join Issue Fulfilment with Reseller sales on the basis that you haven't really sold an Issue until its been delivered.

Not really because you are tracking revenue (sales) at the issue level in the fulfillment fact. The subscription fact isn't revenue. You could integrate reseller sales in the same fact you are tracking issues sent to subscribers. Alternately you can create a third fact that has summarized subscription sales (one row per issue with quantity and revenue) that also contains reseller activity.

Thanks again. I do like the idea of keeping the order subscription level at a higher level, as then its quite easy to integrate order cancellations