April 6, 2009 at 8:08 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
April 6, 2009 at 8:30 am
Your approach sounds correct to me, the dollars will all reconcile, the qty will reconcile, it's only the avg price calc that is potentially going to cause you issues, and you should be able to work around that. Just an initial thought - couldn't you mod your avg price calc to run along the lines of if sum(sales price) == 0 then 0 else sum(sales price)/ sum(qty)? This avoids the div/0 errors and is technically correct, the avg sale price *was* zero if you allowed the customer to hold the goods (ie they're "sold") yet charged them nothing (ie returning 100% of sale price is same as charging $0 initially). It also has the added affect of lowering your overall avg sales price, which again is technically correct.
Steve.
April 6, 2009 at 8:58 am
Steve,
Thanks for the quick response!
Currently, I am actually over-stating the Units returned because a credit on the order has no units, but I record it as a (-1) unit. I'm correctly stating the revenue.
Are you saying you think the order-level credit would work as a zero-unit line item?
I'm somewhat of a newbie with SSAS - is it possible to include that protective logic in the cube calulated measure?
April 6, 2009 at 9:12 am
My bad :S Yes, if you're recording it as a -x, and they haven't truly returned x units, then that sounds wrong to me. Where possible, your data should reflect what really happened and what $$ is in the bank and what units are on the shelf.
And yes, you can definitely use some logic in your calc members, 'if' is actually implemented as Iif. BIll Pearson covers this in one of his series articles (here, note I dropped you on page 2, you may want to go back for the preamble) and the BOL for MDX covers it (here) and even makes mention of it being used to cover the div/0 error (do a find on any part of this string "A very common use of IIF is to handle 'division by zero' errors within calculated measures, as in the following example").
Steve.
April 6, 2009 at 9:38 am
Steve,
You are THE MAN! Thanks for the help and the links to articles.
The good news is, all I need to do to correct the data is zero out a whole bunch of units and add the div/0 filter. :w00t:
Thanks again for sharing your knowledge!
Dan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply