Updating order fact records

[Nickodemus]

Hi All,

Not sure if this is the right place for this, but i'm struggling to figure out the structuring of a fact table, and would appreciate some help...

I have the following order fact:

OrderID	CustomerID	ProductID	Price	Quantity	OrderValue	OrderDate	ShippedDate
00001	1234	987	100	44	4400	25/05/2018	null

(The ShippedDate would be updated at the time it ships)

The issue i have is that there are scenarios where the ProductID (and therefore the price and value) can be changed in the order system without a change to the OrderID. The question i have is what is the best way to model this in the data?

The way i see it, i can either:

1 - Simply update the existing order in the fact table to have the most up to date ProductID, price, and value. The business is really only interested in the date the order was placed, which wouldn't technically change, and the OrderID hasn't changed.... but.... problem is that this method would mean that you lose data... which isn't a great idea.

My above order becoming:

OrderID	CustomerID	ProductID	Price	Quantity	OrderValue	OrderDate	ShippedDate
00001	1234	555	105	44	6600	25/05/2018	null

2 - Infer a 'cancellation' of the initial order, and create a new record for the new version of the order. While the old version would not be flagged as a true cancellation (and therefore wouldn't be added to order cancellation analysis), the old version would no longer be included in 'current' order analysis. The problem is foresee with this method is how to report on this in Power BI - i don't want to be double counting orders in specific days... Also, is this good practice in a fact table? 

My data becoming:

OrderID	CustomerID	ProductID	Price	Quantity	OrderValue	OrderDate	ShippedDate	IsCurrentOrderVersion
00001	1234	987	100	44	4400	25/05/2018	null	0
00001	1234	555	105	44	6600	25/05/2018	null	1

Would i need to change the OrderDate in the new version to be the date the order was updated? I feel like i'd need to create ANOTHER record to negate the old version of the order on the same day the new version is created (without calling it a cancellation)...

As you can probably tell... i'm a little confused, and would just wish the source process would prevent changes to orders - would be much simpler if it just cancelled the old order and created a new one.....

Any advice would be amazing!! thank you.

[MattAllington]

I think you should think of this as a business problem, not a data modelling problem. First decide what you need from a business perspective, then work out how to model it.  My philosophy is “just because you can, doesn’t mean you should”. 

My questions are

1 do you need to report on order changes, annd why?

2 what will the business do with this information if you report on it - how will the ability to report this way drive positive business outcomes?

[Nickodemus]

Thanks Matt.

Agreed, and this is part of the driver for the confusion... Ultimately, the requirement is to report on order 'velocity' - i.e. when are orders coming in, how many have we had to date, and what are the value of those orders (to a given date). From that perspective it's easy enough to assume that, if an order is updated to a different product, then we can simply update the order in the fact table to take on the new productID etc.

However.... There is an argument to establishing the order 'velocity' by productType. I.e. Are the order trends of productType 'X' different to productType 'Y', and if so, how - more at the start of the year, or towards the end, etc. This would drive marketing efforts into specific product types if they're selling much slower at the start of the year than other productTypes. 

Taking our example, if people are ordering specific productTypes early in the year, but then subsequently changing the order to a higher (or lower) productType prior to shipping (shipping cycles are several months apart, leaving long periods between order and ship), then this could be valuable information to drive customers to their final choice first, reducing administrative processing costs for changing orders. Also, this would provide intelligence on the prompt tot purchase and effective upselling popst-order.

I guess i'm saying, the immediate requirement is to just see when orders are occuring and the running totals of orders against each product - therefore the update is not overly impactful. But, pre-empting future business appetite for analysis and BI, i would presume that the business will ultimately see value in being able to track initial order->final order.... (Would this be easy enough to retrofit if needed at a later date......?? I'm not sure)

[MattAllington]

I realise we are a bit off track from your original question - sorry about that.  One other question I have is "why" are the orders being changed?  I don't know your business, but in the businesses I have been involved with, and order is an order.  Why would it change?

[Nickodemus]

The short answer is because it can be changed. In my experience, if a system enables users to change things to bend to exceptional circumstances, they’ll do it, whether it’s a good idea or not. Equally, I guess, it’s the customers’ prerogative to change their mind - increase quantity, upgrade product, change delivery address, change delivery date, etc etc.