[Mondrian] ValidMeasure() combined with Aggregate()

Matt Campbell mkambol at gmail.com
Thu Oct 18 15:22:50 EDT 2007

I apologize in advance for the long post.

There is an interesting quirk with using Aggregate() combined with
ValidMeasure().  If you aggregate a ValidMeasure value over a crossjoined
set involving an unrelated dimension you will get a null value in certain
unexpected cases.  For example, consider the following members:

member measures.[valid_units shipped] as 'validmeasure( measures.[units
shipped] )'
member gender.g as 'Aggregate(crossjoin({gender.gender.members},
[Warehouse].[State Province].members) ) '
member warehouse.w as 'Aggregate(crossjoin({gender.gender.members},
[Warehouse].[State Province].members) ) '

In the cube [warehouse and sales], the warehouse dimension is applicable to
[units shipped], and gender is inapplicable.  The [gender].[g] member will
correctly return the aggregated value of [units shipped].  [warehouse].[w],
however will return a null value, even though it should aggregate the exact
same set.  It's interesting that defining the aggregate member on an
inapplicable dimension returns the correct value, but defining it on an
applicable dimension does not.

Mondrian behavior is consistent with AS2K and SSAS2005 here, although it
seems incorrect.  In SSAS2005, however, there is a property called
"IgnoreUnrelatedDimensions" which is applied to measure groups and causes
ValidMeasure() like behavior.  The nice thing about using
IgnoreUnrelatedDimensions is that you do not need to create a calculation
for each base measure using the ValidMeasure() function—it applies to the
base measures themselves.  Also, setting this property causes the correct
results in all cases.

Can anyone understand why Mondrian would return null in the case of the
[warehouse].[w] member above?  Any guesses about how Microsoft solved the
issue with IgnoreUnrelatedDimensions?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20071018/307d4bd0/attachment.html 

More information about the Mondrian mailing list