Can We show the string value as a measure on mondrian olap

Question

I want to show the string value as one of the measure value. When a fact table has a integer value and string value respectively and also has some foreign table's keys. Then I could show the integer value as a measure value, but I couldn't show the string value as a measure. Because Measure element in schema of cube (written in XML) doesn't allow that a measure value doesn't have 'aggregator'(It specify the aggregate function of measure values). Of course I understood that we can't aggregate some string values. But I want to show the string value of the latest level in hierarchy.

I read following article. A figure (around middle of this page) shows a cube that contains string value as a measure value. But this is an example of Property value of Dimension table, so this string value isn't contain in fact table. I want to show the string value that contains in fact table.

A Simple Date Dimension for Mondrian Cubes

Anyone have some idea that can be shown the string value as a measure value? Or I have to edit Mondrian's source code?

Answer 1

I have had the same problem and solved it by setting the aggregator attribute in the measure tag to max. e.g.

\<Measure name="Comment" datatype="String" column="comment" caption="Comment" aggregator="max"/\>

Answer 2

Why does it need to be a measure?

If no aggregation would naturally be applied to it and you just want the string value, it is a dimension, not a measure. Trying to force it to be a measure is not the best approach.

I think the figure you reference is just showing a drillthrough, and that the only actual measure is Turnover. The report layout is slightly misleading in terms of dimensions and measures.

You can just use the fact table again in the schema as a dimension table if for some reason you don't want to split this out into a separate physical table.

Sounds like the string may be high cardinality to the integer, possibly 1:1. Depending upon the size of your cube, this might or might not be a performance challenge. But don't try to make it a measure.

Good luck!