I am new to OLAP and I was wondering if it is possible to create a dimension
in Analysis Services 2005 based on a range of values for a given field in a
Fact table.
Example:
Field Name: Quantity
Dimension Name: Quantity Range
Dimension Values: 0-100
101-200
201-1000
> 1000
The only solution I see now is to denormalize Quantity into a new field
QuantityRange that is computed based on the value of the quantity
1: 0-100
2: 101-200
3: 201-1000
4: > 1000
The values 1 to 4 would be my dimension.

Thanks al lot

I gave you some ideas on the microsoft.public.sqlserver.olap newsgroup.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"carlos" <carlos@discussions.microsoft.com> wrote in message
news:6043ABBE-7ADA-4E9D-8AA7-D252F075DAEB@microsoft.com...
>I am new to OLAP and I was wondering if it is possible to create a
>dimension
> in Analysis Services 2005 based on a range of values for a given field in
> a
> Fact table.
> Example:
> Field Name: Quantity
> Dimension Name: Quantity Range
> Dimension Values: 0-100
> 101-200
> 201-1000
> > 1000
> The only solution I see now is to denormalize Quantity into a new field
> QuantityRange that is computed based on the value of the quantity
> 1: 0-100
> 2: 101-200
> 3: 201-1000
> 4: > 1000
> The values 1 to 4 would be my dimension.
>
> Thanks al lot

You could drive this denormalisation using the data in the dimension
table.

1) Create a dimension table with max and min columns

ID MinValue MaxValue
1 0 100
2 101 200
3 201 1000
4 1000 2147483648 <-- max integer value

2) Create a view on the fact table like the following

Select
...
<list fact columns here>
...
,dim.ID
from fact f
inner join dimQuantityRange dim
on f.Quantity between dim.MinValue and dim.MaxValue

3) Alter you cube to use the newly created view as the fact "table".
then you can join the dimension back onto the id in the view.

The advantage of doing things this way is that everything is data
driven. If you need to add more ranges or change the ranges you just
update the max/min columns in the dim table and re-process.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell