PDA

View Full Version : [Mondrian] MDX from Excel 2007



Anton Nikitin
05-30-2007, 11:20 AM
Today I was experimenting with Excel 2007 pivot tables and found that it
generates a bit crazy MDX statements.



For example, very simple GUI navigation steps cause the following MDX:



WITH

SET [XL_Row_Dim_0] AS
'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
Customers].[USA]), Descendants([Customers].[All Customers].[USA])})))'

SELECT

NON EMPTY Hierarchize({[Time].[Year].members}) ON COLUMNS ,

NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Customers].[All
Customers]})}, [XL_Row_Dim_0])) ON ROWS

FROM [Sales]

WHERE ([Measures].[Store Sales])



Mondrian doesn't correctly processes this one (results differ from MSAS
2000).



Anton



P.S. IMHO it may concern Pentaho Spreadsheet Services


_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Julian Hyde
05-31-2007, 11:00 AM
Logged a bug for this one:
http://sourceforge.net/tracker/index.php?func=detail
<http://sourceforge.net/tracker/index.php?func=detail&aid=1728980&group_
id=35302&atid=414613> &aid=1728980&group_id=35302&atid=414613.

Julian


_____

From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org]
On Behalf Of Anton Nikitin
Sent: Wednesday, May 30, 2007 8:17 AM
To: 'Mondrian developer mailing list'
Subject: [Mondrian] MDX from Excel 2007



Today I was experimenting with Excel 2007 pivot tables and found that it
generates a bit crazy MDX statements.



For example, very simple GUI navigation steps cause the following MDX:



WITH

SET [XL_Row_Dim_0] AS
'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
Customers].[USA]), Descendants([Customers].[All Customers].[USA])})))'

SELECT

NON EMPTY Hierarchize({[Time].[Year].members}) ON COLUMNS ,

NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Customers].[All
Customers]})}, [XL_Row_Dim_0])) ON ROWS

FROM [Sales]

WHERE ([Measures].[Store Sales])



Mondrian doesn't correctly processes this one (results differ from MSAS
2000).



Anton



P.S. IMHO it may concern Pentaho Spreadsheet Services


_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Will Gorman
06-12-2007, 09:30 AM
Anton,

Would it be possible to post the expected results from Excel 2007 / MSAS
2000 on this query? I'm in the process of getting access to a MSAS
server but it may be a few days.

Thanks!

Will

On Wed, 2007-05-30 at 19:17 +0400, Anton Nikitin wrote:
> Today I was experimenting with Excel 2007 pivot tables and found that
> it generates a bit crazy MDX statements.
>
>
>
> For example, very simple GUI navigation steps cause the following MDX:
>
>
>
> WITH
>
> SET [XL_Row_Dim_0] AS
> 'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
> Customers].[USA]), Descendants([Customers].[All
> Customers].[USA])})))'
>
> SELECT
>
> NON EMPTY Hierarchize({[Time].[Year].members}) ON COLUMNS ,
>
> NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Customers].[All
> Customers]})}, [XL_Row_Dim_0])) ON ROWS
>
> FROM [Sales]
>
> WHERE ([Measures].[Store Sales])
>
>
>
> Mondrian doesn’t correctly processes this one (results differ from
> MSAS 2000).
>
>
>
> Anton
>
>
>
> P.S. IMHO it may concern Pentaho Spreadsheet Services
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Julian Hyde
06-12-2007, 04:20 PM
PS Anton log a bug for this and assign to Will. The easiest thing for us
would be a patch to XmlaBasicTest.ref.xml with an extra TestCase element
containing the XML request and response.

The easier you make this for us, the sooner we can fix it. :)

Julian


> -----Original Message-----
> From: mondrian-bounces (AT) pentaho (DOT) org
> [mailto:mondrian-bounces (AT) pentaho (DOT) org] On Behalf Of Will Gorman
> Sent: Tuesday, June 12, 2007 6:29 AM
> To: Mondrian developer mailing list
> Subject: Re: [Mondrian] MDX from Excel 2007
>
> Anton,
>
> Would it be possible to post the expected results from Excel
> 2007 / MSAS
> 2000 on this query? I'm in the process of getting access to a MSAS
> server but it may be a few days.
>
> Thanks!
>
> Will
>
> On Wed, 2007-05-30 at 19:17 +0400, Anton Nikitin wrote:
> > Today I was experimenting with Excel 2007 pivot tables and
> found that
> > it generates a bit crazy MDX statements.
> >
> >
> >
> > For example, very simple GUI navigation steps cause the
> following MDX:
> >
> >
> >
> > WITH
> >
> > SET [XL_Row_Dim_0] AS
> > 'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
> > Customers].[USA]), Descendants([Customers].[All
> > Customers].[USA])})))'
> >
> > SELECT
> >
> > NON EMPTY Hierarchize({[Time].[Year].members}) ON COLUMNS ,
> >
> > NON EMPTY
> Hierarchize(Intersect({DrilldownLevel({[Customers].[All
> > Customers]})}, [XL_Row_Dim_0])) ON ROWS
> >
> > FROM [Sales]
> >
> > WHERE ([Measures].[Store Sales])
> >
> >
> >
> > Mondrian doesn't correctly processes this one (results differ from
> > MSAS 2000).
> >
> >
> >
> > Anton
> >
> >
> >
> > P.S. IMHO it may concern Pentaho Spreadsheet Services
> >
> >
> > _______________________________________________
> > Mondrian mailing list
> > Mondrian (AT) pentaho (DOT) org
> > http://lists.pentaho.org/mailman/listinfo/mondrian
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Anton Nikitin
06-13-2007, 11:50 AM
Will,

I've tried to reproduce this MDX one more time. Actually it is a bit more
complicated in Excel 2007 (the difference only in DIMENSION/CELL
PROPERTIES):

WITH SET [XL_Row_Dim_0] AS
'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
Customers].[USA]), Descendants([Customers].[All Customers].[USA])})))'
SELECT NON EMPTY Hierarchize({[Time].[Year].members}) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY
Hierarchize(Intersect({DrilldownLevel({[Customers].[All Customers]})},
[XL_Row_Dim_0])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM
[Sales] WHERE ([Measures].[Store Sales]) CELL PROPERTIES VALUE,
FORMAT_STRING, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MSAS-2000 result is:

Store Sales Column Labels
Row Labels 1997
USA 565238.13
Grand Total 565238.13

(Hope I haven't made any changes in my MS Foodmart database...)

Please keep in mind that I didn't originally used Pentaho Spreadsheet
Services to check this MDX in Mondrian - but simply typed it in AD-hoc query
test JSP. It is possible, Excel will generate a bit different MDX when
connecting to Spreadsheet Services ODBO Provider...

Regards,
Anton

-----Original Message-----
From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org] On
Behalf Of Will Gorman
Sent: Tuesday, June 12, 2007 5:29 PM
To: Mondrian developer mailing list
Subject: Re: [Mondrian] MDX from Excel 2007

Anton,

Would it be possible to post the expected results from Excel 2007 / MSAS
2000 on this query? I'm in the process of getting access to a MSAS
server but it may be a few days.

Thanks!

Will

On Wed, 2007-05-30 at 19:17 +0400, Anton Nikitin wrote:
> Today I was experimenting with Excel 2007 pivot tables and found that
> it generates a bit crazy MDX statements.
>
>
>
> For example, very simple GUI navigation steps cause the following MDX:
>
>
>
> WITH
>
> SET [XL_Row_Dim_0] AS
> 'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
> Customers].[USA]), Descendants([Customers].[All
> Customers].[USA])})))'
>
> SELECT
>
> NON EMPTY Hierarchize({[Time].[Year].members}) ON COLUMNS ,
>
> NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Customers].[All
> Customers]})}, [XL_Row_Dim_0])) ON ROWS
>
> FROM [Sales]
>
> WHERE ([Measures].[Store Sales])
>
>
>
> Mondrian doesn't correctly processes this one (results differ from
> MSAS 2000).
>
>
>
> Anton
>
>
>
> P.S. IMHO it may concern Pentaho Spreadsheet Services
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Will Gorman
06-14-2007, 09:50 AM
Good news, Mondrian returns the exact values below when running this
query verbatim.

Will

On Wed, 2007-06-13 at 19:49 +0400, Anton Nikitin wrote:
> Will,
>
> I've tried to reproduce this MDX one more time. Actually it is a bit more
> complicated in Excel 2007 (the difference only in DIMENSION/CELL
> PROPERTIES):
>
> WITH SET [XL_Row_Dim_0] AS
> 'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
> Customers].[USA]), Descendants([Customers].[All Customers].[USA])})))'
> SELECT NON EMPTY Hierarchize({[Time].[Year].members}) DIMENSION PROPERTIES
> PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY
> Hierarchize(Intersect({DrilldownLevel({[Customers].[All Customers]})},
> [XL_Row_Dim_0])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM
> [Sales] WHERE ([Measures].[Store Sales]) CELL PROPERTIES VALUE,
> FORMAT_STRING, BACK_COLOR, FORE_COLOR, FONT_FLAGS
>
> MSAS-2000 result is:
>
> Store Sales Column Labels
> Row Labels 1997
> USA 565238.13
> Grand Total 565238.13
>
> (Hope I haven't made any changes in my MS Foodmart database...)
>
> Please keep in mind that I didn't originally used Pentaho Spreadsheet
> Services to check this MDX in Mondrian - but simply typed it in AD-hoc query
> test JSP. It is possible, Excel will generate a bit different MDX when
> connecting to Spreadsheet Services ODBO Provider...
>
> Regards,
> Anton
>
> -----Original Message-----
> From: mondrian-bounces (AT) pentaho (DOT) org [mailto:mondrian-bounces (AT) pentaho (DOT) org] On
> Behalf Of Will Gorman
> Sent: Tuesday, June 12, 2007 5:29 PM
> To: Mondrian developer mailing list
> Subject: Re: [Mondrian] MDX from Excel 2007
>
> Anton,
>
> Would it be possible to post the expected results from Excel 2007 / MSAS
> 2000 on this query? I'm in the process of getting access to a MSAS
> server but it may be a few days.
>
> Thanks!
>
> Will
>
> On Wed, 2007-05-30 at 19:17 +0400, Anton Nikitin wrote:
> > Today I was experimenting with Excel 2007 pivot tables and found that
> > it generates a bit crazy MDX statements.
> >
> >
> >
> > For example, very simple GUI navigation steps cause the following MDX:
> >
> >
> >
> > WITH
> >
> > SET [XL_Row_Dim_0] AS
> > 'VisualTotals(Distinct(Hierarchize({Ascendants([Customers].[All
> > Customers].[USA]), Descendants([Customers].[All
> > Customers].[USA])})))'
> >
> > SELECT
> >
> > NON EMPTY Hierarchize({[Time].[Year].members}) ON COLUMNS ,
> >
> > NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Customers].[All
> > Customers]})}, [XL_Row_Dim_0])) ON ROWS
> >
> > FROM [Sales]
> >
> > WHERE ([Measures].[Store Sales])
> >
> >
> >
> > Mondrian doesn't correctly processes this one (results differ from
> > MSAS 2000).
> >
> >
> >
> > Anton
> >
> >
> >
> > P.S. IMHO it may concern Pentaho Spreadsheet Services
> >
> >
> > _______________________________________________
> > Mondrian mailing list
> > Mondrian (AT) pentaho (DOT) org
> > http://lists.pentaho.org/mailman/listinfo/mondrian
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian