<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<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;}
@font-face
        {font-family:Tahoma;
        panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
        {margin:0in;
        margin-bottom:.0001pt;
        font-size:11.0pt;
        font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
        {mso-style-priority:99;
        color:blue;
        text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
        {mso-style-priority:99;
        color:purple;
        text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
        {mso-style-priority:99;
        mso-style-link:"Balloon Text Char";
        margin:0in;
        margin-bottom:.0001pt;
        font-size:8.0pt;
        font-family:"Tahoma","sans-serif";}
span.BalloonTextChar
        {mso-style-name:"Balloon Text Char";
        mso-style-priority:99;
        mso-style-link:"Balloon Text";
        font-family:"Tahoma","sans-serif";}
span.EmailStyle19
        {mso-style-type:personal;
        font-family:"Calibri","sans-serif";
        color:windowtext;}
span.EmailStyle20
        {mso-style-type:personal;
        font-family:"Calibri","sans-serif";
        color:#1F497D;}
span.apple-converted-space
        {mso-style-name:apple-converted-space;}
span.EmailStyle22
        {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]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Thanks Matt and Carlos! I do agree<span style="color:#1F497D">
</span><a href="http://jira.pentaho.com/browse/MONDRIAN-1495">http://jira.pentaho.com/browse/MONDRIAN-1495</a> is the issue I’m seeing (and I voted).<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I looked at the code changes for MONDRIAN-663, and it does look like the affected areas in SqlTupleReader and SqlMemberSource are the places where some more smarts is still needed. There’s conditional logic on whether the level has multiple
columns. I was looking to see if there was some low hanging fruit nobody had had time to work on here, but it wasn’t obvious to me.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">--jeff<span style="color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> mondrian-bounces@pentaho.org [mailto:mondrian-bounces@pentaho.org]
<b>On Behalf Of </b>Matt Campbell<br>
<b>Sent:</b> Tuesday, June 16, 2015 1:31 PM<br>
<b>To:</b> Mondrian developer mailing list<br>
<b>Subject:</b> Re: [Mondrian] aggregate tables, collapsed dimensions, and ordinal column<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">MONDRIAN-663 had 3 proposed fixes:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.5pt;font-family:"Arial","sans-serif";color:#333333;background:white">// 1. changing the sql generation to join the collapsed level to its<span class="apple-converted-space"> </span></span><span style="font-size:10.5pt;font-family:"Arial","sans-serif";color:#333333"><br>
<span style="background:white">// dimension table(s) to select the additional columns.<span class="apple-converted-space"> </span></span><br>
<span style="background:white">// 2. Create members that are missing these values and populate the<span class="apple-converted-space"> </span></span><br>
<span style="background:white">// values at a later time.<span class="apple-converted-space"> </span></span><br>
<span style="background:white">// 3. extend agg tables to support additional level columns<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">The one that was implemented (starting with 68e9b0f75a) was #1, producing the SQL you’re seeing. Unfortunately there’s not a way currently to include extra columns associated with a member directly onto the
agg table. Hopefully the case Carlos referenced gets picked up at some point. Vote for it if you haven’t already.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="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>From:</b> <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>Wright, Jeff<br>
<b>Sent:</b> Tuesday, June 16, 2015 11:49 AM<br>
<b>To:</b> Mondrian developer mailing list<br>
<b>Subject:</b> [Mondrian] aggregate tables, collapsed dimensions, and ordinal column<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">In our application, we’ve been using aggregate tables based on collapsing dimensions. Due to our data model, it’s hard for us to get benefit from the “lost dimension” approach to aggregation.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">We’re seeing some really poor SQL when we have a separate column defining the ordinal for a dimension. What we’re seeing is SQL that joins the aggregate table to the original dimension table *<b>on the dimension value</b>*, to bring in
the ordinal.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">We had hoped there might be an easy fix, to also collapse the ordinal column into the aggregate table, but that doesn’t seem to work.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Does this ring any bells with anybody? In JIRA I found <a href="http://jira.pentaho.com/browse/MONDRIAN-663">
http://jira.pentaho.com/browse/MONDRIAN-663</a>, but I didn’t trace this through to the code.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">--jeff wright<o:p></o:p></p>
</div>
</body>
</html>