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

Roland Bouman roland.bouman at gmail.com
Mon Mar 28 09:24:52 EDT 2016


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>
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/32dfb618/attachment-0001.html 


More information about the Mondrian mailing list