Changing Fact for Order Modifications
3 posters
Page 1 of 1
Changing Fact for Order Modifications
I've been asking a lot of questions on the forum and have been receiving great help. I don't want it to seem as though I'm taking advantage of this help, but I'm really stuck here.
Business users want to see, in (near) real-time, how its online store is doing. Due to the business of this online store, it may take months to fulfill an order. Furthermore, modifications are quite common. I want to create an order line item fact table, where each row represents a line item of the order. The problem I'm seeing is that the user may modify their entire order such that each line item is completely different (and the user may select more or fewer products). This would mean that on an order status change, I would have to delete all the fact records for a given order ID and then reload the fact table with the new order id line items. Is deleting and then re-adding fact table data the correct way to handle such modifications? If not, are there any suggestions on how to fulfill this requirement -- or is this type of requirement not appropriate for a data warehouse, since its goal is to replace an existing operational reporting system)?
The implication would be that report numbers run off of future fulfillment dates could change, but the numbers for already fulfilled orders should remain the same. Is this acceptable in a data warehouse?
Note: another reason for replacing the operational reports with this system is that this system combines data from multiple sources.
Business users want to see, in (near) real-time, how its online store is doing. Due to the business of this online store, it may take months to fulfill an order. Furthermore, modifications are quite common. I want to create an order line item fact table, where each row represents a line item of the order. The problem I'm seeing is that the user may modify their entire order such that each line item is completely different (and the user may select more or fewer products). This would mean that on an order status change, I would have to delete all the fact records for a given order ID and then reload the fact table with the new order id line items. Is deleting and then re-adding fact table data the correct way to handle such modifications? If not, are there any suggestions on how to fulfill this requirement -- or is this type of requirement not appropriate for a data warehouse, since its goal is to replace an existing operational reporting system)?
The implication would be that report numbers run off of future fulfillment dates could change, but the numbers for already fulfilled orders should remain the same. Is this acceptable in a data warehouse?
Note: another reason for replacing the operational reports with this system is that this system combines data from multiple sources.
kangaroo- Posts : 8
Join date : 2009-12-09
Re: Changing Fact for Order Modifications
If this order system was something cooked in-house, I would try to push back a bit. A well designed order system retains a consistant identifier for an order line and does not reuse them. If a line is added to an order, it is given a new number, lines that are deleted are usually flagged as such or their quantities are set to zero. Having a source like this makes maintenace of the facts much easier.
Of course, this also begs the question... do they want order history?
Of course, this also begs the question... do they want order history?
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Work Order / Customer Order Design - Dimension or Fact
» Order dimension vs. order degenerate dimensions in the fact table!?
» Need to merge fact tables
» Order Dimension and Order Fact
» Order Line Fact
» Order dimension vs. order degenerate dimensions in the fact table!?
» Need to merge fact tables
» Order Dimension and Order Fact
» Order Line Fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum