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

Roland Bouman roland.bouman at gmail.com
Mon Mar 28 06:14:19 EDT 2016


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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160328/c1f7c9ef/attachment.html 


More information about the Mondrian mailing list