[Mondrian] Mondrian vs Olap4j vs Ole DB for OLAP Named Axes definitions

[Wright, Jeff]

I thought the lesson was don’t ever use more than 3 axes ☺

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Roland Bouman
Sent: Monday, March 28, 2016 9:25 AM
To: Mondrian developer mailing list <mondrian at pentaho.org>
Subject: Re: [Mondrian] Mondrian vs Olap4j vs Ole DB for OLAP Named Axes definitions

Wait - I stand corrected. I only get this when using the named axis. ON Axis(x) or simply ON x work as expected.

I guess that from my pov, that solves it.

(And I guess the lesson I leaned is to never, ever use axis names, at least not if you want your MDX to be portable across engines.)

On Mon, Mar 28, 2016 at 12:14 PM, Roland Bouman <roland.bouman at gmail.com<mailto:roland.bouman at gmail.com>> wrote:
Hi all,

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.

Consider this query:

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]

I execute this over XML/A and I get a result. But not all is well. Consider this fragment of the AxisInfo:

        <AxesInfo>
...
          <AxisInfo name="Axis3">
            <HierarchyInfo name="Product">
              <UName name="[Product].[MEMBER_UNIQUE_NAME]"/>
              <Caption name="[Product].[MEMBER_CAPTION]"/>
              <LName name="[Product].[LEVEL_UNIQUE_NAME]"/>
              <LNum name="[Product].[LEVEL_NUMBER]"/>
              <DisplayInfo name="[Product].[DISPLAY_INFO]"/>
            </HierarchyInfo>
          </AxisInfo>
          <AxisInfo name="Axis4">
            <HierarchyInfo name="Education Level">
              <UName name="[Education Level].[MEMBER_UNIQUE_NAME]"/>
              <Caption name="[Education Level].[MEMBER_CAPTION]"/>
              <LName name="[Education Level].[LEVEL_UNIQUE_NAME]"/>
              <LNum name="[Education Level].[LEVEL_NUMBER]"/>
              <DisplayInfo name="[Education Level].[DISPLAY_INFO]"/>
            </HierarchyInfo>
          </AxisInfo>
          <AxisInfo name="SlicerAxis"/>
        </AxesInfo>

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.

I also took a look at the docs. First of all, this:

https://msdn.microsoft.com/en-us/library/ms146052.aspx

which lists the named axes in this order:


COLUMNS | ROWS | PAGES | SECTIONS | CHAPTERS

and this

https://msdn.microsoft.com/en-us/library/windows/desktop/ms713616(v=vs.85).aspx



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.



When I compare this to both the olap4j and the mondrian documentation:



http://www.olap4j.org/api/org/olap4j/Axis.Standard.html

http://www.olap4j.org/2.0/api/org/olap4j/Axis.Standard.html

http://mondrian.pentaho.com/api/mondrian/olap/AxisOrdinal.StandardAxisOrdinal.html



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.



Finally, Oracle Essbase (https://docs.oracle.com/cd/E12825_01/epm.111/esb_techref/frameset.htm?mdx_axis.htm) also seems to reverse CHAPTERS and SECTIONS with respect to the Ole DB Standard.



I just wanted to check my assumptions with the list before proceeding to file bugs:



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 w/re to olad4j vs oledb to naming convention

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.



Finally, does someone have a suggestion for working around or avoiding the wrong result I got out of my query?



best regards,



Roland




--
Roland Bouman
blog: http://rpbouman.blogspot.com/
twitter: @rolandbouman
linkedin: http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro

Author of "Pentaho Solutions" (Wiley, ISBN: 978-0-470-48432-6 http://tinyurl.com/lvxa88) and "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9 http://tinyurl.com/33r7a8m)



--
Roland Bouman
blog: http://rpbouman.blogspot.com/
twitter: @rolandbouman
linkedin: http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro

Author of "Pentaho Solutions" (Wiley, ISBN: 978-0-470-48432-6 http://tinyurl.com/lvxa88) and "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9 http://tinyurl.com/33r7a8m)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160328/5c5ab4d2/attachment-0001.html