[Mondrian] IgnoreMeasureForNonJoiningDimension

Matt Campbell mcampbell at pentaho.com
Thu Feb 21 18:14:39 EST 2013


This afternoon I started working on a task to fix some failing tests on lagunitas involving the  IgnoreMeasureForNonJoiningDimension property (MONDRIAN-1437).  Despite having been involved in the original request for this feature, it took me a little while to wrap my head back around the functionality.

Here's my memory of the use case that prompted my former employer to push for this a few years back.  With healthcare data, analysts may want to sum up claims data that's coming from different sources.  For example, [Net Payment] is defined as the sum of payments from the Drug Claims cube and payments from the Hospital Claims cube.  These two cubes have very similar but not identical dimensionality.

Our users would create reports with a dimension that joins to one but not both of these two cubes, and then be very surprised at the totals.  In the example below the [Drug] dimension does not join to the Hosp cube:

Drug

Net Payment

Net Pay Drug

Net Pay Hosp

FLECAINIDE ACETATE

758.79

758.79



MAXAIR AUTOHALER

1,689.79

1,689.79



MENOSTAR

1,482.10

1,482.10



NITROGLYCERIN

766.60

766.60



PROVENTIL AEROSOL

609.48

609.48



TOTAL

11536.24

5306.76

6229.48


Since the total would be defined with [Drug].[All Drug], the [Net Pay Hosp] column ends up with an odd looking number, and the [Net Payment] column does not seem to add up correctly.  Easy to explain to someone versed in MDX, not so easy to explain to the typical analyst.

Enter IgnoreMeasureForNonJoiningDimension.  The thought was that we needed something essentially the opposite of ValidMeasure or IgnoreUnrelatedDimensions.  ValidMeasure forces unrelated dimensions to the All level.  We needed something that would cause evaluation to be null even if the unrelated dimension was at the All level.  I worked with a developer from ThoughtWorks to get this enhancement in and passing cases with Cognos generated MDX.
We used this functionality briefly before hitting enough other border cases around totals that we moved to an alternative fix involving more complex Cognos report specs.

I'm curious now whether anyone else is using this functionality.  If you are, it would be good to hear your use cases so we make sure they're accounted for in 4.x.  If no one is using it, it seems like a potential candidate for the chopping block.

As I've been poking at it today, I've noticed some things that seem broken even in Mondrian 3.x.  For example, with IgnoreMeasureForNonJoiningDimension enabled, the following two queries give different results in 3.x.  The first ignores the measure with the non-joining Gender dimension and returns null, the second query returns 196,770.888.

WITH MEMBER [Measures].[memberArrayValueCalc] as
  '( measures.[warehouse sales], [Gender].[All Gender], [Product].[All Products] )'
SELECT
   [Measures].[memberArrayValueCalc] on 0
FROM
  [warehouse and sales]


SELECT
  ([Measures].[warehouse sales], [Gender].[All Gender], [Product].[All Products] ) on 0
FROM
  [warehouse and sales]





-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20130221/eaf60a7c/attachment.html 


More information about the Mondrian mailing list