Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fragmented orders?

3 posters

Go down

Fragmented orders? Empty Fragmented orders?

Post  davej 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

Back to top Go down

Fragmented orders? Empty Re: Fragmented orders?

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

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

http://aginity.com

Back to top Go down

Fragmented orders? Empty Re: Fragmented orders?

Post  davej 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

Back to top Go down

Fragmented orders? Empty Re: Fragmented orders?

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

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

http://aginity.com

Back to top Go down

Fragmented orders? Empty Re: Fragmented orders?

Post  davej 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

Back to top Go down

Fragmented orders? Empty Re: Fragmented orders?

Post  pyramidhad 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

Back to top Go down

Fragmented orders? Empty Re: Fragmented orders?

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

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

http://aginity.com

Back to top Go down

Fragmented orders? Empty Re: Fragmented orders?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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