Dimension based on (derived from) Measure value

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!

If it was me, I would do the following;
1. add an additional columnn to your fact table for price range, make it an integer
2. create a dimension for these price ranges
3. apply the case statement you have here as an update to that fact table and change the terms ('Expensive', 'Moderate', etc.) to integers that match the counterparts in your new price range dimension
4. add the new dimension to the cube schema, etc.
5. reprocess
Unless I am not understanding what you are going for here that should do it.
Edward R Hunter
|||

Hi Edward -

Thank you for your feedback. You are right on the money with your suggestion, and that would be my first choice as I think it is the more correct way to design this. However, the relational data store is owned by a different group in my company and I have no update privileges to it, and change requests are added to a very long list of requests.

Also, I just think that this should be something that the tool should allow if needed.

If anyone reading this is interested, I did figure out how to add a dimension based on a Measure. I used the dimension wizard, selected the measure as the source, and select the option "Ordering and Uniqueness of Members" (i think this is the key). Then, in the dimension editor, add the case statement to the member key column and member name column expressions. process and there it is.

Edward's solution above is also a very good one and the one i would have used if I were designing this from the beginning. I would have also created a table that would hold the limits to the ranges. This would be updateable by administrators or power users via a simple web form. Then pass those values as variables to the case statement.

Joel B