Fragmented orders?

View previous topic View next topic Go down

Fragmented orders?

Post  davej on Thu Mar 13, 2014 12:28 pm

If I have a situation where orders can be broken up and shipped to several destinations I am thinking that my lowest level fact table needs to be "shipment line item" and then my first aggregate would be "order line item" and my second aggregate would be "order." Is that a reasonable approach? Are there better alternate approaches? I am just getting into this stuff.

Several things that confuse me -- for the "order line item" fact table I don't know if I should have a "shipment" dimension -- and even for the "shipment line item" fact table I'm not sure how to record the shipping charges without a "shipment" dimension.

Thanks.

davej

Posts : 6
Join date : 2014-02-17

View user profile

Back to top Go down

Re: Fragmented orders?

Post  ngalemmo on Thu Mar 13, 2014 7:16 pm

Orders and fulfillment are two different business functions that occur at different times.  Each should be represented by its own fact table.  The shipment fact will contain dimensions relating to the order (order #, line #, product ordered, customer, etc...) as well as those related to the shipment (including product shipped as substitutions may occur).  Measures in the shipment fact should pertain to the shipment, not the order. Treat invoicing the same way.

If you want to combine order and shipment information, you can always build an aggregate as well.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fragmented orders?

Post  davej on Thu Mar 13, 2014 8:36 pm

So if I start with a "shipment line item" fact table then what are the obvious aggregates? Do I simply proceed up to shipments over periods of time or do I merge into orders? Thanks!

davej

Posts : 6
Join date : 2014-02-17

View user profile

Back to top Go down

Re: Fragmented orders?

Post  ngalemmo on Thu Mar 13, 2014 8:40 pm

By 'aggregate' I mean to combine order and shipment information into a single table. Such an aggregate is optional and would be considered if performance is an issue without it.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fragmented orders?

Post  davej on Sun Mar 16, 2014 6:20 pm

I just don't understand whether orders and shipping should be separate or if they should be tied together as completely as possible. Thanks.

davej

Posts : 6
Join date : 2014-02-17

View user profile

Back to top Go down

Re: Fragmented orders?

Post  pyramidhad on Tue Mar 18, 2014 12:47 am

personally, i prefer to separate them . in fact  i think it is a good idea to create fact table(s) according to the time : if they occur in the same time , they can be tied toghter , otherwise , separate them .

pyramidhad

Posts : 1
Join date : 2014-03-17

View user profile

Back to top Go down

Re: Fragmented orders?

Post  ngalemmo on Tue Mar 18, 2014 1:00 am

In dimensional modeling an atomic fact tables represents a business event or state. The receipt of an order, and shipment of an order are two different business events. Besides, the measures are different, the dimensionality is different, the moment they occur is different.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fragmented orders?

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