Modelling sales_orders and order_shipment_schedule

We have a sales_order_fact table which contains all orders at the detail line item level.

SALES_ORDER_FACT
customer_key
part_key
vendor_key
order_date
salesorder_id <-- natural_key
salesorder_line_id <-- natural_key
initial_requested_order_qty
backorder_qty
deferred_qty

When orders are placed, they can be immediately fulfilled and invoiced. Or, the part may be backordered and a schedule is created for that line_item to be shipped at a future date based on avereage vendor lead time. Additionally, a customer may order 100 of part A, and want 70 shipped today and 30 shipped next month on schedule. So, there can be a one-to-many between an order line item and the order ship schedule which will include a ship_date and a qty.

SALES_ORDER_FACT
customer part vendor order_date salesorder_id line_item request_qty backorder_qty deferred_qty
123456 100 5 3/12/2012 555555 1 10 0 0
123456 200 5 3/12/2012 555555 2 20 20 0
123456 300 5 3/12/2012 555555 3 100 0 30

This is the data from the source transaction table for the line items 2 and 3 above which have a schedule:
salesorder_id line_item ship_date scheduled_qty
555555 2 5/15/2012 20 <-- backordered
555555 3 4/12/2012 30 <-- deferred

Not only will the business want to analyse the order schedule for which vendors which have backordered products or customers which put orders on deferred shipments, but then cancel, but the schedule will be used for planning resources, etc. in the distribution center. We will want to look historically at the schedule as well, i.e. compare schedule for March relative to the schedule last March.

Does this become another fact table with all the same dinmensions as SALES_ORDER_FACT or is there some other way to model this?

Thanks in advance for your help, Kim

Yes, you need a ship fact. If you know the order and line number when shipping, you can drill across on degenerate dimensions.

you can not have 2 shipdates for one line item in a fact !!
Business activity is happening at 2 places... you need 2 facts to show that.

When I've done this before we have had three fact tables - Order, Scheduled and Shipped.

John Simon wrote:When I've done this before we have had three fact tables - Order, Scheduled and Shipped.

... and eventually invoiced.

Breaking it out as John & Vishy suggest makes everything so much simpler. Backorders, returns, substitutions, ect... all occur at different phases of the process. Eventually you can put it all together as an aggregate giving an order-to-cash view of things, but not until each step in the process is properly represented.