a price range dimension question

I'm using sql2k.
I'm providing a simplified scenario here.
I'm trying to build a fact table on sales (ie. item, price, quantity,
price*quantity).
I'd like build a cube that I can look up the price by range ($0-$5,
$5-10, $10-$15, etc...).
What's the best way to handle this? do i need a price range
dimension? or should i keep the price range in fact table?
I can't predict what new price will be added to sales, it could be
from 1 cent to any pricing, so if I were to build a price range
dimension, how would it look like?=== Steve L === wrote:
> I'm using sql2k.
> I'm providing a simplified scenario here.
> I'm trying to build a fact table on sales (ie. item, price, quantity,
> price*quantity).
> I'd like build a cube that I can look up the price by range ($0-$5,
> $5-10, $10-$15, etc...).
> What's the best way to handle this? do i need a price range
> dimension? or should i keep the price range in fact table?
> I can't predict what new price will be added to sales, it could be
> from 1 cent to any pricing, so if I were to build a price range
> dimension, how would it look like?
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe the price range would be considered more a criteria than a
dimension. The only price-range dimension table I could come up w/
would be something like this:
CREATE TABLE PriceRange (
range_code INT NOT NULL PRIMARY KEY,
start_value DECIMAL (11,2) NOT NULL,
end_value DECIMAL (11,2) NOT NULL
)
The fact table would hold the range_code. When you made the CUBE you'd
include the range_code. It might be faster to use range_codes if all
you're doing is a retrieval of data based on ranges. Probably, you
could include both range_code and price in the cube.
But, it make more sense to only use the price in the CUBE then you could
do SUMs and change the price range criteria for each query. Using the
PriceRange dimension - what happens when you want to change the range
criteria of a query? The PriceRange dimension would have to be rebuilt,
then the cube. Are you always going to be using the same price ranges?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQuVvxYechKqOuFEgEQJxJACgwYIakvQZsqvI
WzXp1z6A6aFyMN8An3VU
aJ07XYAO+lukEymGqGnn8aQe
=E0wk
--END PGP SIGNATURE--|||Hi Steve ,
This might solve your problem
Here the gap is 10 ,You can easily make it 5 ,qty can be changed to
price
SELECT LowRange,HiRange,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
Please let me know if it solved your purpose
With warm regards
Jatinder