[Mondrian] RE: Mondrian change 13915: XMLA queries now return information about the filter axis, even if it is empty

Sergey Mazin sergey.mazin at skype.net
Thu Dec 2 06:32:18 EST 2010


Julian,

 

I tested change 13915.

Excel 2007 expects at least one member to be returned on SlicerAxis (it
can be default member of Measures hierarchy if there is nothing else to
return).

 

1) PivotTables with more then one measure selected fail to display
results. MDX query:

 

with set [XL_Row_Dim_0] as
'VisualTotals(Distinct(Hierarchize({Ascendants([Store].[USA].[OR]),
Descendants([Store].[USA].[OR]), Ascendants([Store].[USA].[CA]),
Descendants([Store].[USA].[CA])})))'

select {[Measures].[Number of Employees], [Measures].[Count]} DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS,

  NON EMPTY
Hierarchize(Intersect(DrilldownMember({{DrilldownLevel({[Store].[All
Stores]})}}, {[Store].[USA]}), [XL_Row_Dim_0])) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON ROWS

from [HR]

 

2) Adding hierarchy to PivotTable before adding any measure fails to
display results. MDX query:

 

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Store].[All Stores]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS  

FROM [HR] CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR,
FORE_COLOR, FONT_FLAGS

 

3) PivotTables with filter including multiple members from the same
hierarchy fail to update when pressing  "Refresh" button. MDX query:

 

WITH MEMBER [Store Type].[XL_PT0] AS 'strtomember("[Store Type].[Gourmet
Supermarket]").UniqueName' MEMBER [Store Type].[XL_PT1] AS
'strtomember("[Store Type].[Deluxe Supermarket]").UniqueName'

SELECT {[Store Type].[XL_PT0],[Store Type].[XL_PT1]} ON 0

FROM [HR] CELL PROPERTIES VALUE

 

I attached XmlaHandler.java file. It contains changes I've done to make
these 3 cases work with Excel 2007.

 

Best regards,

Sergey

 

From: Julian Hyde [mailto:jhyde at pentaho.com] 
Sent: Wednesday, November 17, 2010 10:39 PM
To: 'Matt Campbell'; Sergey Mazin
Cc: 'Mondrian developer mailing list'
Subject: Mondrian change 13915: XMLA queries now return information about
the filter axis, even if it is empty

 

Matt,

 

Can you please review change 13915.

 <http://p4web.eigenbase.org/@md=d&c=6PU@/13915?ac=10>
http://p4web.eigenbase.org/@md=d&c=6PU@/13915?ac=10

I know that your team added the DataOmitDefaultSlicer flag to prevent
dozens of unused dimensions coming back in the slicer each request. When
was no WHERE clause, or the WHERE clause evaluated to an empty set, there
would be no members in the slicer, and therefore no slicer axis would be
returned. This is a problem for the olap4j driver, since it needs to
distinguish between a missing WHERE clause (one tuple with zero members)
and a WHERE clause that evaluates to empty (zero tuples).

 

So, I have changed the mondrian XMLA server's response. It always returns
a slicer axis. In the cases above, the slicer will either have no tuples
or one tuple with no positions, so the change adds only a few bytes to
each XMLA response.

 

Sergey,

 

I know you are interested in running Mondrian with Excel 2007. You might
also wish to review this change.

 

Julian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20101202/f352ac49/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: XmlaHandler.java
Type: text/java
Size: 115729 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20101202/f352ac49/attachment.bin 


More information about the Mondrian mailing list