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

Fact row dilemma

3 posters

Go down

Fact row dilemma Empty Fact row dilemma

Post  DanColbert Mon Apr 06, 2009 10:04 am

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

Posts : 11
Join date : 2009-02-03
Age : 55

Back to top Go down

Fact row dilemma Empty Fact row dilemma

Post  bgray Mon Apr 06, 2009 5:20 pm

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.


Posts : 8
Join date : 2009-02-10

Back to top Go down

Fact row dilemma Empty Re: Fact row dilemma

Post  Todd McDermid Tue Apr 07, 2009 4:19 pm

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.
Todd McDermid
Todd McDermid

Posts : 11
Join date : 2009-02-04
Location : Nanaimo, BC

Back to top Go down

Fact row dilemma Empty Re: Fact row dilemma

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