<div dir="ltr">Hi all,<div><br></div><div>Perhaps this is known behavior - if so, pardon my ignorance. I did look but did not find any description of this issue I just ran into. </div><div><br></div><div>Consider this query:</div><div><br></div><div><span style="font-family:Consolas,'Lucida Console',monospace;font-size:12px;white-space:pre-wrap">SELECT [Measures].Members on COLUMNS,
[Gender].Members on ROWS,
[Marital Status].Members on PAGES,
[Education Level].Members on SECTIONS,
[Product].[Product Family].Members on CHAPTERS
FROM [Sales]</span></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap">I execute this over XML/A and I get a result. But not all is well. Consider this fragment of the AxisInfo:</span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><div style=""> <AxesInfo></div><div style="">...</div><div style=""> <AxisInfo name="Axis3"><br></div><div style=""> <HierarchyInfo name="Product"></div><div style=""> <UName name="[Product].[MEMBER_UNIQUE_NAME]"/></div><div style=""> <Caption name="[Product].[MEMBER_CAPTION]"/></div><div style=""> <LName name="[Product].[LEVEL_UNIQUE_NAME]"/></div><div style=""> <LNum name="[Product].[LEVEL_NUMBER]"/></div><div style=""> <DisplayInfo name="[Product].[DISPLAY_INFO]"/></div><div style=""> </HierarchyInfo></div><div style=""> </AxisInfo></div><div style=""> <AxisInfo name="Axis4"></div><div style=""> <HierarchyInfo name="Education Level"></div><div style=""> <UName name="[Education Level].[MEMBER_UNIQUE_NAME]"/></div><div style=""> <Caption name="[Education Level].[MEMBER_CAPTION]"/></div><div style=""> <LName name="[Education Level].[LEVEL_UNIQUE_NAME]"/></div><div style=""> <LNum name="[Education Level].[LEVEL_NUMBER]"/></div><div style=""> <DisplayInfo name="[Education Level].[DISPLAY_INFO]"/></div><div style=""> </HierarchyInfo></div><div style=""> </AxisInfo></div><div style=""> <AxisInfo name="SlicerAxis"/></div><div style=""> </AxesInfo></div></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap">The problem is that the order of the last two query axes is reversed in the result. I get a similar result when I use integer axis designations or Axis(x) designations.</span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap">I also took a look at the docs. First of all, this:</span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><a href="https://msdn.microsoft.com/en-us/library/ms146052.aspx">https://msdn.microsoft.com/en-us/library/ms146052.aspx</a></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br></span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap">which lists the named axes in this order:</span></font></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br></span></font></div><div><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important">COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS</pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important">and this</pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px"><a href="https://msdn.microsoft.com/en-us/library/windows/desktop/ms713616(v=vs.85).aspx">https://msdn.microsoft.com/en-us/library/windows/desktop/ms713616(v=vs.85).aspx</a><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px"><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px">which reads: The first five axes — COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS — can be used in place of AXIS(0), AXIS(1), AXIS(2), AXIS(3), and AXIS(4), respectively.</span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px"><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px">When I compare this to both the olap4j and the mondrian documentation:</span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px"><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="line-height:17.55px"><a href="http://www.olap4j.org/api/org/olap4j/Axis.Standard.html">http://www.olap4j.org/api/org/olap4j/Axis.Standard.html</a><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="line-height:17.55px"><a href="http://www.olap4j.org/2.0/api/org/olap4j/Axis.Standard.html">http://www.olap4j.org/2.0/api/org/olap4j/Axis.Standard.html</a><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="line-height:17.55px"><a href="http://mondrian.pentaho.com/api/mondrian/olap/AxisOrdinal.StandardAxisOrdinal.html">http://mondrian.pentaho.com/api/mondrian/olap/AxisOrdinal.StandardAxisOrdinal.html</a><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px"><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px">These all list CHAPTERS as Axis(3) and SECTIONS as Axis(4); that is, chapters and sections are reversed as compared to the order listed in the ole db standard.</span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px"><br></span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><font color="#000000" face="Consolas, Courier, monospace"><span style="font-size:13px;line-height:17.55px">Finally, Oracle Essbase (<a href="https://docs.oracle.com/cd/E12825_01/epm.111/esb_techref/frameset.htm?mdx_axis.htm">https://docs.oracle.com/cd/E12825_01/epm.111/esb_techref/frameset.htm?mdx_axis.htm</a>) also seems to reverse CHAPTERS and SECTIONS with respect to the Ole DB Standard.</span></font></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><br></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal">I just wanted to check my assumptions with the list before proceeding to file bugs:</pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><br></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal">1) in my opinion the reversal of the axes in the result like I witnessed in my query example is an issue that is separate from the difference <span style="font-family:arial,sans-serif">w/re to olad4j vs oledb </span><span style="font-family:arial,sans-serif">to naming convention </span></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><span style="font-family:arial,sans-serif">2) While I would like it better if there was no difference between mondrian/olap4j vs oledb for olap, I suppose the convention for mapping axis names to index is not that important. But if a choice is made to use a designation different from the one in Ole DB, then the olap4j / mondrian documentation should probably make a point of explicitly documenting the difference so as to prevent confusion.</span></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal"><span style="font-family:arial,sans-serif"><br></span></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important">Finally, does someone have a suggestion for working around or avoiding the wrong result I got out of my query? </pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important"><br></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important">best regards,</pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important"><br></pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important">Roland</pre><pre style="padding:5px;margin-top:0px;margin-bottom:0px;overflow:auto;word-wrap:normal;color:rgb(0,0,0);font-size:13px;line-height:17.55px;font-family:Consolas,Courier,monospace!important"><br></pre></div><div><font face="Consolas, Lucida Console, monospace"><span style="font-size:12px;white-space:pre-wrap"><br clear="all"></span></font><div><br></div>-- <br><div class="gmail_signature">Roland Bouman<br>blog: <a href="http://rpbouman.blogspot.com/" target="_blank">http://rpbouman.blogspot.com/</a><br>twitter: @rolandbouman<br>linkedin: <a href="http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro" target="_blank">http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro</a><br><br>Author of "Pentaho Solutions" (Wiley, ISBN: 978-0-470-48432-6 <a href="http://tinyurl.com/lvxa88" target="_blank">http://tinyurl.com/lvxa88</a>) and "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9 <a href="http://tinyurl.com/33r7a8m" target="_blank">http://tinyurl.com/33r7a8m</a>)</div>
</div></div>