Fact row dilemma

I have a FactOrders table. When we have a return, I record it as a new row on the original order, but with negative values in the Quantity and ExtendedPrice columns.

There are times when we credit a customer a dollar amount, but no units are returned. I'm not sure how to record that in the fact row.

If I record the dollar amount, but no units - any calculated "average price" measure will break because of a divide by zero. If I record a (-1) for the quantity, then I overstate the number of units that have been returned.

So what is the best approach for this? I can't imagine I'm the only one to have come across this.

Thanks in advance!

Dan Colbert

If your credit order references the original order and line number, can't you simply look up the quantity from the original order line in the fact table before writing the credit record? Rather than write the actual Qty on the credit order row, write the reversed Qty that you looked up. Of course that assumes you are crediting the entire quantity.

I believe that the true problem comes when they credit a customer for some amount of money - but no units change hands. This is not an uncommon scenario - I can think of several business processes that can result in this:
1. A pricing mistake/change - the customer was charged $5 per unit instead of the "negotiated" $4. Issuing a credit to the customer fixes this mistake, but no units of product change hands
2. An invoicing mistake - the customer was charged some kind of additional fee, and this is being returned. In some OLTP systems, such fees are not "products" that can be returned (as there's no inventory of it).
3. A period-end rebate is applied retroactively to several invoices. Some systems would model this in a separate fact table, but others may "adjust" the original invoices' prices.

To solve this problem, you could do one of the following:
1. Simply place a negative dollar value, zero unit entry into your fact table. Hopefully, you don't store a price/unit in your fact table (as it's non-aggregatable anyway). "Protect" your calculated facts from zero divisors - even though such a situation should never happen if the credit "matches" a true has-product invoice. (Although this may not be the case if dating is different.)
2. Don't add one fact record to adjust the total - add two. Completely reverse the original entry - negative original units and negative ORIGINAL amount. Then add in the adjusted entry. When it's all aggregated up you'll get valid totals - regardless of the time window.