> Hi
> I am trying to find out how you deal with variable prices, so when I query
> [annual unit sales] * [price] I don't get [annual unit sales] * [current
> price]. Is there a rule of thumb for this type of issue?
>
> graeme
I don't have any rule of thumb for you, but I do know how to keep
individual sales in a data mart or data warehouse.
The trick is to tie each item sold to the price charged for that item, on
that sale. This can even take into account discounts for coupons or thing
like that. The way you can have time varying prices for the same item is to
have multiple entries in the PRODUCT dimension for the same product. The
entries have a start date and stop date. The entries in the SALES facts are
tied to the PRODUCT entry with the correct price.
At this point, you can, if desired, materialize the extended price as
[extended price] = [units sold] * [current price] way down at the lowest
level of granularity. Then, when you want the annual sales dollars for
that product, it's just a matter of finding and adding.
Warning: if you have no intention of creating a data mart, don't let this
argument persuade you otherwise. There is a large cost to building such a
thing, and it needs to be justified by a large benefit.