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

Mon Mar 28 19:38:32 EDT 2016

Well, it does seem that olap4j is not consistent with XMLA. And I’d consider that a bug.

However I don’t recall being drunk when I defined the order. I suspect that Microsoft have quietly changed the order since the first MDX specification. That would explain why Oracle/Essbase[1] and IBM[2] have made the same mistake that Mondrian and olap4j have.

Does anyone have a copy of the MDX language specification (or the OLE DB for OLAP specification)?

Julian

[1] https://docs.oracle.com/cd/E60665_01/financialscs_gs/FAAPI/mdxp_api_sample.html <https://docs.oracle.com/cd/E60665_01/financialscs_gs/FAAPI/mdxp_api_sample.html>

[2] https://books.google.com/books?id=y7y6AgAAQBAJ&lpg=PA272&ots=IeQ89JQjwk&dq=%22COLUMNS%2C%20ROWS%2C%20PAGES%2C%20%20CHAPTERS%2C%20SECTIONS%22&pg=PA272#v=onepage&q=%22COLUMNS,%20ROWS,%20PAGES,%20%20CHAPTERS,%20SECTIONS%22&f=false <https://books.google.com/books?id=y7y6AgAAQBAJ&lpg=PA272&ots=IeQ89JQjwk&dq=%22COLUMNS%2C%20ROWS%2C%20PAGES%2C%20%20CHAPTERS%2C%20SECTIONS%22&pg=PA272#v=onepage&q=%22COLUMNS,%20ROWS,%20PAGES,%20%20CHAPTERS,%20SECTIONS%22&f=false> 

> On Mar 28, 2016, at 6:33 AM, Wright, Jeff <jeff.s.wright at truvenhealth.com <mailto:jeff.s.wright at truvenhealth.com>> wrote:
> 
> I thought the lesson was don’t ever use more than 3 axes J
>  
> From: mondrian-bounces at pentaho.org <mailto:mondrian-bounces at pentaho.org> [mailto: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 <mailto: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 <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 <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/api/org/olap4j/Axis.Standard.html>
> http://www.olap4j.org/2.0/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 <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 <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/ <http://rpbouman.blogspot.com/>
> twitter: @rolandbouman
> linkedin: http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro <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 <http://tinyurl.com/lvxa88>) and "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9 http://tinyurl.com/33r7a8m <http://tinyurl.com/33r7a8m>)
> 
> 
>  
> -- 
> Roland Bouman
> blog: http://rpbouman.blogspot.com/ <http://rpbouman.blogspot.com/>
> twitter: @rolandbouman
> linkedin: http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro <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 <http://tinyurl.com/lvxa88>) and "Pentaho Kettle Solutions" (Wiley, ISBN: 978-0-470-63517-9 http://tinyurl.com/33r7a8m <http://tinyurl.com/33r7a8m>)
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160328/ace75ecd/attachment-0001.html 