Re: Variable prices

"graeme" <nntp1grame_DELETETHIS__at_googlemail.com> wrote in message news:6MGhj.39504$KC3.14591_at_newsfe6-gui.ntli.net...
> 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. Received on Fri Jan 11 2008 - 13:30:44 CET