Accumulating Snapshot Fact Table Data Model (Order Management)
Page 1 of 1
Accumulating Snapshot Fact Table Data Model (Order Management)
Hi,
I am designing a data model for an use case similar to the Order Management case mentioned in the The Data Warehouse Toolkit book. I am planning to have a row in the fact table per line item in the order and store the various dates e.g order date, shipment date, delivery date etc as an accumulating snapshot. But I have a few doubts regarding my design
1) The order can be submitted by the customer online. Hence the will be instances when the customer has added a particular line item to the shopping cart but has not yet submitted the order. I need to track such pending orders. Should I track such orders in a different Order level fact table? Or should I add a row in the Line Item Fact table for each of the line items when the items are saved in the cart and then update the Line Item fact table after the order has been submitted?
2) I am having CANCELLATION_DATE and DELIVERY_DATE as two of the snapshot date dimensions in the Fact table. Since after completion of the life cycle only one of the two columns will have a value, should I have two different date columns or just one COMPLETION_DATE column and the completion status(Cancelled/Delivered) as another column?
Thanks and Regards,
Nik
I am designing a data model for an use case similar to the Order Management case mentioned in the The Data Warehouse Toolkit book. I am planning to have a row in the fact table per line item in the order and store the various dates e.g order date, shipment date, delivery date etc as an accumulating snapshot. But I have a few doubts regarding my design
1) The order can be submitted by the customer online. Hence the will be instances when the customer has added a particular line item to the shopping cart but has not yet submitted the order. I need to track such pending orders. Should I track such orders in a different Order level fact table? Or should I add a row in the Line Item Fact table for each of the line items when the items are saved in the cart and then update the Line Item fact table after the order has been submitted?
2) I am having CANCELLATION_DATE and DELIVERY_DATE as two of the snapshot date dimensions in the Fact table. Since after completion of the life cycle only one of the two columns will have a value, should I have two different date columns or just one COMPLETION_DATE column and the completion status(Cancelled/Delivered) as another column?
Thanks and Regards,
Nik
nik_de- Posts : 1
Join date : 2013-10-16
Similar topics
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Order / Order Item: Accumulating Snapshot vs. transaktional
» Accumulating Snapshot fact table
» Accumulating Snapshot Fact table
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Order / Order Item: Accumulating Snapshot vs. transaktional
» Accumulating Snapshot fact table
» Accumulating Snapshot Fact table
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum