[Mondrian] Segment cache and multi-valued dimensions

Matt Campbell mcampbell at pentaho.com
Mon May 4 16:18:34 EDT 2015

There is currently an expectation that each fact will map to 1 member within a hierarchy.  Your scenario with many authors for a single books fact violates that, and could cause issues when Mondrian determines roll ups.

In the absence of many-to-many support I can make a couple suggestions.

1)       Define cubes at the two levels of granularity and bring them together in a virtual cube.  That is, define one cube at the granularity of (ISBN x Author).  The [All Author] value of [author prints] would be the total of count for all authors (10 in your example).  A second cube could be defined at the ISBN granularity and would not include the [Author] hierarchy.  This would give a [isbn prints] value of 1 for a single isbn.  This option ends up w/ 2 base measures where you formerly had one, but you may be able to stitch them together in a single calculation which looks at context to determine which to show.

2)      This seems less appropriate if you want to be able to both sum up across authors and sum up without, but another option for these sorts of models is to go horizontal.  That is, define attributes for each author position.  E.g. [Primary Author], [Author 2], [Author 3], etc..  These attributes will have a value of N/A (or whatever) for ISBNs that lack an author at that position.  It’s a pretty clunky option, but I’ve seen it used in a few cases to get around many-to-many.


From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Ricardo Fradinho
Sent: Monday, May 04, 2015 11:12 AM
To: Mondrian developer mailing list
Subject: [Mondrian] Segment cache and multi-valued dimensions

I have a question around many to many / multi-valued dimensions support in Mondrian:

Can I use Mondrian on top of a schema with 1:N relation on the fact => dimension without having the segment cache ?
Here's a simplified version of the model I'm looking at:

Fact books:

isbn        prints
----------- ------------
123-XYZ     1

Dimension book_authors:

isbn        author
----------- ------------
123-XYZ     Mr 001
123-XYZ     Mr 002
123-XYZ     Mr 010
I have a fact table that joins with a dimension that has several authors for the same book.
I want Mondrian to return sum(prints)=10 when I join the fact with the dimension table, but
I want Mondrian to return sum(prints)=1 when don't query over the authors dimension, ie, just looking at the fact or another dimensions with a 1:1 relation.
If execute a query first with a authors breakdown and the information is stored in the segment cache, does Mondrian use that segment if I query a second time without the authors breakdown ?
If so, Mondrian will aggregate over the cached segment and sum(prints)=10.

Put differently, does Mondrian expect a 1:1 relation on the fact foreign key to dimension primary key ?
I'm not asking about the many to many feature support or semi/non additive measures, rather I'm looking at the consistency requirements when dealing with segment aggregates.

Ricardo Fradinho.

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

More information about the Mondrian mailing list