Price - measure or dimension

[arrowhouse]

Hi there,

I'm new to Cognos - so pls forgive if this is an easy question.

We want to have price in our cube - but this information is valid only at the lowest level granularity of the cube. If we make the price a measure how can we prevent aggregation occuring when reporting at higher levels?

It is not appropriate (nor a requirement) for users to be able to drill by price. However it needs to be displayed in the reports.

In general should prices be dimensions or measures? How can I get the required behaviour in the cube?

Any advice/experience of similar welcome!

Thanks

 

[CognosProfessional]

Have you tried to change the price to a text field in impromptu before saving as .iqd and then implement that into the .mdl? That would be my first thought.
CP

 

[arrowhouse]

Thanks for your suggestion, but this does not appear to help - I think because it is defined as measure, transformer just converts the text back into a number. Per haps I have misunderstood your suggestion?

How do you (or anyone) deal with prices normally?

Thanks.

 

[clown4him]

If you are showing it just as additional information about a product, consider concatenating name and price as a text string.
Otherwise, use CP's suggestion with price as a dimension instead of a measure. Then users can add it in when they need to see it.

Happy Independence Day!

 

[JerrySmith123]

You can set the measure to aggregate the average of all the granular prices... this doesn't exactly exclude it - but does cause the aggregation to make a little more sense.

The other thing you can do is to show at the summary levels the "Lowest" price or the "Highest" price.

Doesn't make as much sense as the avg...at least to the user - but if you train them...they'll get used to it.

It's just one of those idiosynchracies of Cognos - it summarizes aggregated data...

If you use price as a dimension rather than a measure - you can't use it in a calculation though... so if you are only looking to show it as an attribute of the product or material, dimensionalize it, otherwise live with some sort of aggregation.

 

[arrowhouse]

Thanks for all your suggestions, I'm thinking through the possiblities & ideas you've come up with.

We want to stop any aggregation/averaging at anything other than the lowest level of granularity as the information is only valid at this level. For example we have the information in different currencies & none of average/Highest/Lowest make sense.

We have a strong preference for a measure so the infomation can be used in calculations.

Has any one used external roll-ups? Would this offer us any possiblities?