<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">You might want to have a look at bridge tables as well, see e.g:<div class=""><a href="http://diethardsteiner.github.io/mondrian/2014/12/26/Bridge-And-Closure-Table.html" class="">http://diethardsteiner.github.io/mondrian/2014/12/26/Bridge-And-Closure-Table.html</a></div><div class=""><br class=""></div><div class=""><br class=""><div><blockquote type="cite" class=""><div class="">On 4 May 2015, at 21:18, Matt Campbell <<a href="mailto:mcampbell@pentaho.com" class="">mcampbell@pentaho.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="WordSection1" style="page: WordSection1; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;"><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">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. <span class="Apple-converted-space"> </span><o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">In the absence of many-to-many support I can make a couple suggestions.<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 12pt; font-family: 'Times New Roman', serif; text-indent: -0.25in;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""><span class="">1)<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-family: 'Times New Roman';" class=""> <span class="Apple-converted-space"> </span></span></span></span><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> 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.<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 12pt; font-family: 'Times New Roman', serif; text-indent: -0.25in;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""><span class="">2)<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-family: 'Times New Roman';" class=""> <span class="Apple-converted-space"> </span></span></span></span><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">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. <span class="Apple-converted-space"> </span><o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">hth<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""> </span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><b class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif;" class="">From:</span></b><span style="font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span class="Apple-converted-space"> </span><a href="mailto:mondrian-bounces@pentaho.org" class="">mondrian-bounces@pentaho.org</a> [<a href="mailto:mondrian-bounces@pentaho.org" class="">mailto:mondrian-bounces@pentaho.org</a>]<span class="Apple-converted-space"> </span><b class="">On Behalf Of<span class="Apple-converted-space"> </span></b>Ricardo Fradinho<br class=""><b class="">Sent:</b><span class="Apple-converted-space"> </span>Monday, May 04, 2015 11:12 AM<br class=""><b class="">To:</b><span class="Apple-converted-space"> </span>Mondrian developer mailing list<br class=""><b class="">Subject:</b><span class="Apple-converted-space"> </span>[Mondrian] Segment cache and multi-valued dimensions<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><o:p class=""> </o:p></div><div class=""><div class=""><div class=""><div class=""><div class=""><p class="MsoNormal" style="margin: 0in 0in 12pt; font-size: 12pt; font-family: 'Times New Roman', serif;">Hi,<o:p class=""></o:p></p></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">I have a question around many to many / multi-valued dimensions support in Mondrian:<o:p class=""></o:p></div></div><div class=""><p class="MsoNormal" style="margin: 0in 0in 12pt; font-size: 12pt; font-family: 'Times New Roman', serif;"><br class="">Can I use Mondrian on top of a schema with 1:N relation on the fact => dimension without having the segment cache ?<o:p class=""></o:p></p></div><div class=""><p class="MsoNormal" style="margin: 0in 0in 12pt; font-size: 12pt; font-family: 'Times New Roman', serif;">Here's a simplified version of the model I'm looking at:<br class=""><br class=""><span style="font-family: 'Courier New';" class="">Fact books:<br class=""><br class="">isbn prints<br class="">----------- ------------<br class="">123-XYZ 1<br class=""><br class="">Dimension book_authors:<br class=""><br class="">isbn author<br class="">----------- ------------<br class="">123-XYZ Mr 001<br class="">123-XYZ Mr 002<br class="">...<br class="">123-XYZ Mr 010</span><o:p class=""></o:p></p></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">I have a fact table that joins with a dimension that has several authors for the same book.<o:p class=""></o:p></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">I want Mondrian to return sum(prints)=10 when I join the fact with the dimension table, but<o:p class=""></o:p></div></div><div class=""><p class="MsoNormal" style="margin: 0in 0in 12pt; font-size: 12pt; font-family: 'Times New Roman', serif;">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.<o:p class=""></o:p></p></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">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 ?<o:p class=""></o:p></div></div><div class=""><p class="MsoNormal" style="margin: 0in 0in 12pt; font-size: 12pt; font-family: 'Times New Roman', serif;">If so, Mondrian will aggregate over the cached segment and sum(prints)=10.<br class=""><br class="">Put differently, does Mondrian expect a 1:1 relation on the fact foreign key to dimension primary key ?<o:p class=""></o:p></p></div><div class=""><p class="MsoNormal" style="margin: 0in 0in 12pt; font-size: 12pt; font-family: 'Times New Roman', serif;">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.<br class=""><br class=""><o:p class=""></o:p></p></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">Thanks,<o:p class=""></o:p></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class="">Ricardo Fradinho.<o:p class=""></o:p></div></div><div class=""><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><o:p class=""> </o:p></div></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif;" class=""><o:p class=""> </o:p></div></div><p class="MsoNormal" style="margin: 0in 0in 12pt; font-size: 12pt; font-family: 'Times New Roman', serif;"><o:p class=""> </o:p></p></div></div></div><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">_______________________________________________</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class="">Mondrian mailing list</span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class=""><a href="mailto:Mondrian@pentaho.org" class="">Mondrian@pentaho.org</a></span><br style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><span style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; float: none; display: inline !important;" class=""><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" class="">http://lists.pentaho.org/mailman/listinfo/mondrian</a></span></div></blockquote></div><br class=""></div></body></html>