<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div>Thanks a lot Matt! I did google for this problem and only found one mention on the Pentaho forum and one jira case ( although not stating completely the same problem ). I am quite astound that this problem has not been observed by more users - this was also the reason why I originally thought that I was doing something wrong. <br></div><div>I created this morning a jira case as well and sent Thomas additional details. He could replicate the problem as well. I'll link your jira case to mine. Many thanks again for your help - this problem has kept me busy for the last 3 days and it is good to know now that it is indeed a bug.</div><div>Best regards,</div><div>Diethard</div><div><br>On 14 Oct 2014, at 20:44, Matt Campbell <<a href="mailto:mcampbell@pentaho.com">mcampbell@pentaho.com</a>> wrote:<br><br></div><blockquote type="cite"><div>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi Diethard,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I ran a quick test and I’m seeing some funkiness with PRD tabularizing MDX results when a set includes members from multiple levels of the same dim, as in your
queries. Conversion to a table model appears to fall down under certain circumstances. I created PRD-5278 for this—feel free to add your example to it as well.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">(BTW- this doesn’t appear to be a new problem. I saw the same behavior w/ 5.0 and 5.1)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">-matt<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <a href="mailto:mondrian-bounces@pentaho.org">mondrian-bounces@pentaho.org</a> [<a href="mailto:mondrian-bounces@pentaho.org">mailto:mondrian-bounces@pentaho.org</a>]
<b>On Behalf Of </b>Diethard Steiner<br>
<b>Sent:</b> Monday, October 13, 2014 1:31 PM<br>
<b>To:</b> Mondrian developer mailing list<br>
<b>Subject:</b> [Mondrian] Weird MDX result set in CDA and PRD<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Good evening,<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Although this is not really the right mailing list for this topic, quite likely some of you have created a PRD Report or CDE dashboard based on the result of a MDX query. As you might well know, CDA uses the PRD libs for data access.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">The Problem<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">==========<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Manifests its in CDA as well as in PRD. The sample query shown below uses 3 dimensions: date, brand group and brand (but the problem is not restricted to these dimensions). In PRD I used the standard Pentaho Analysis data source and in
CDA the ‘classic’ output method. Note: I have to use the ‘classic’ output method in CDA because I need the long version of the column headers to extract additional info (as CDA does not support Mondrian level captions).<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">The problem is the brand group column will just show one value: Total. However, Saiku as well as the CDA legacy MDX data source show proper values. I’ve been investigating this problem for several hours but haven’t yet come to a conclusion
as to why this is happening:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">SELECT<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">NON EMPTY(<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">{ [Brand Group].Members} * {[Brand].Members }<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">) ON ROWS<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM [Subscriber Base]<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">WHERE [Measures].[Subscribers]<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">No brand <b>group names</b> are displayed, all just labeled ‘Total'. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">This works however:<o:p></o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">SELECT<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">NON EMPTY(<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">{ [Brand Group].[Brand Group].Members} * {[Brand].Members }<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">) ON ROWS<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM [Subscriber Base]<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">WHERE [Measures].[Subscribers]<o:p></o:p></p>
</div>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">So in this case we do not have the total for brand group and brand. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">This query works as well:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">SELECT<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">NON EMPTY(<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Union ( { [Brand Group].[Brand Group].Members} * {[Brand].[Total], [Brand].[Brand].Members} , { [Brand Group].[Total] } * {Brand.Total}) <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">) ON ROWS<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM [Subscriber Base]<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">WHERE [Measures].[Subscribers]<o:p></o:p></p>
</div>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">So why does the first one not work as expected? The result set looks wrong … is this down to how I wrote the query or what exactly is causing this?<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Another example:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<div>
<p class="MsoNormal">SELECT<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">NON EMPTY(<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Union ( <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">[Brand Group].[Total] * [Brand].[Total] <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">, [Brand Group].Children * {[Brand].[Total], [Brand].Children}<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">) <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">) ON ROWS<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM [Subscriber Base]<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">WHERE [Measures].[Subscribers]<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Brand Group column doesn’t show proper values, only ‘Total’ value.<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">Slightly changing the query by mentioning the totals last in the UNION makes PRD/CDA happy:<o:p></o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">SELECT<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">NON EMPTY(<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Union ( <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">[Brand Group].Children * {[Brand].[Total], [Brand].Children}<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">-- totals have to be mentioned last as otherwise PRD will not display some col values correctly<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">, [Brand Group].[Total] * [Brand].[Total] <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">) <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">) ON ROWS<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">FROM [Subscriber Base]<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">WHERE [Measures].[Subscribers]<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">This time the Brand Group column displays the proper values.<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<div>
<p class="MsoNormal">All the ‘wrong' results show a similar pattern: not every row has a Brand (All) and Brand Group value (see attached screenshot). Also the column positions are different.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><image001.png><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Sorry for not sharing any more detailed screenshots, but I am currently working with the clients' data.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Do you have any ideas why this is happening?<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal">I tried similar queries on other cubes and I get a similar behaviour.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Environment: PRD 5.2 and a fairly modern version of c-tools.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Diethard<o:p></o:p></p>
</div>
</div>
</div></blockquote><blockquote type="cite"><div><span>_______________________________________________</span><br><span>Mondrian mailing list</span><br><span><a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a></span><br><span><a href="http://lists.pentaho.org/mailman/listinfo/mondrian">http://lists.pentaho.org/mailman/listinfo/mondrian</a></span><br></div></blockquote></body></html>