I would like to create dimension with members which are
ranges.
For exmple if I use FoodMart 2000 example, I would like to
add to cube Sales dimension [Sales Ranges] with members:
store sales < $5, $5-$15, $15-$50 and sales >$50.
The idea is to retrieve number of sales between these
ranges, their total amount, etc.

Is this possible? and could you tell me how I can do that
without adding column (which contains range of sale) to
the database.

Best

Svetlin Grancharov

You can't add the column to the actual dimension table? That is what I'd
recommend..

Alternatively create the memebr usign SQL case stataments within the
dimension editor window - create a a new level (e.g. 'range' within the
customer dimension) and edit the definition (customer.customer name) to look
like case when sales between 0 and 5 then '< 5'... etc etc

HTH

Darren


"Svetlin Grancharov" <sgrancharov (AT) frontsys (DOT) com> wrote

Programatically create a dimension with the value from the fact table as
the first column and the range as the second column. When designing the
cube, join the fact to the dimension using the orginal value. The cube
can be built either includingor excluding the detailed lines i.e. it
could be built with only the range if needed.
Major problem is that you may end up with a large dimension table as the
values will be very varied.
This sytem works fine if there are a limited number of values e.g. from
1 to 100 and building a quartile dimension as there would only be 100
rows in the dimension table with a hierachy as required.
Try running a select distinct on the value column and see how many rows
you get.

You could also try creating a fact table with the ranges in directly
rather than using your existing fact (providing you don't want the low
level values?).


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!