There is a measure in the cube called Price. Also a dimension called
Product.

I need to create a Dimension that classifies each Product by a "Price
Range". For example, Expensive, Moderate, Cheap.

A user can therefore choose Cheap from "Price Range" dimension and see
the "Cheap" Products and the associated Measures (Price, Units Sold,
Cost, etc).

To derive the classification, a Case statement can be used
CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
etc.......

But I can't figure out how to make this information a dimension.

I've tried a couple of things but have been unsuccessful. Please help!

Joel,

Is product uniquely identified in the fact table? Duplicates will
interfere in creating a dimension. What is the error message in
building the cube?

Thanks

Peter


JoelBarish wrote:

Product is uniquely identified.

There is not a specific error message, per se.

I tried to create a virtual dimension based on the Price measure, but it
won't allow a dimension to be based on a measure. It also won't allow a
dimension to be based on the fact table.

Also, there is more than just a Product dimension, that was just for
illustration. There would be a customer dimension, a channel dimension,
etc. The "Price Range" would apply to the intersection across all
dimensions.


*** Sent via Developersdex http://www.developersdex.com ***

I had a similar problem. I don’t believe you can create dimension based on
measure on the cube.
In your case you need create price range table that fact table will
reference to it.

Example:

Price_range_id, price, range_name

And
In the fact table you would have price_range_id column.

Aside that, I believe price should not be in the fact table. It is a
property of product dimension.
Price is not additive measure.


"Joel Barish" wrote:

This is true - Price is not an additive measure. This does not reflect
my actual data. I gave this example for simplicity sake since my data
is very industry specific.

I was hoping not to have to change the star. That will mean the
relational db team will have to get a request, spec it, blah blah
blah....

It just seems like this should be something that is possible. The
other two major OLAP vendors include this type of functionality.... it
prevents the source data from having to be reworked.

In Analysis Services 2005 you can create calculated columns in the DSV
layer and tables can play the roles of both a fact and dimension, so you
could do what you are after.

In Analysis Services 2000 you would have to create a separate view over
the fact table in the relation database and use that as the source for a
dimension.

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

In article <1134673443.166240.269910 (AT) g14g2000cwa (DOT) googlegroups.com>,
stacijon (AT) gmail (DOT) com says...

I just noticed your reply. Thank you for taking the time to respond...
I figured out how to do this in AS 2000.... I created a Dimension
using a measure field as a source, used an expression to convert the
measure to a string using the Case statement above, used grouping and
unique names, and voila --- it worked. I think the key is the Unique
Member Names and Member Key. I had to create the dimension using the
wizard, I couldn't get it to work using the dimension editor... but it
was probably my own fault.

Overall, however, I think your solution is the better one.

Hi JoelBarish,

I got into a similiar case with your situation, but I'a still new into this
and really hope u could explain further
on how you solved it.
Could you give more details on creating a Dimension using a measure field as
a source,
how you use an expression to convert the measure to a string using the Case
statement above,
and using grouping and unique names, to achieve your results.

My situation is that i need to retrieve the measure.amount based on a few
columns on fact tables.
For example, if column_a = 1 and column_b = 3 then i will take the amount
into gross premium.
if column_a=1 and column_b = 3 and column_c = 5 then it ake the amount into
reinsurance.
I need to retrieve this values in each group and placed against the other
dimensionsof business class.

Thanks and regards,
ray

JoelBarish wrote:
--
Message posted via http://www.sqlmonster.com