Modelling sales_orders and order_shipment_schedule

View previous topic View next topic Go down

Modelling sales_orders and order_shipment_schedule

Post  kjfischer on Mon Mar 12, 2012 1:33 pm

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

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Modelling sales_orders and order_shipment_schedule

Post  BoxesAndLines on Mon Mar 12, 2012 2:49 pm

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

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

View user profile

Back to top Go down

Re: Modelling sales_orders and order_shipment_schedule

Post  Vishy on Mon Mar 12, 2012 2:55 pm

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.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Modelling sales_orders and order_shipment_schedule

Post  John Simon on Mon Mar 12, 2012 6:28 pm

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

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Modelling sales_orders and order_shipment_schedule

Post  ngalemmo on Mon Mar 12, 2012 6:42 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Modelling sales_orders and order_shipment_schedule

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum