[Mondrian] No result in query - Problem with VisualTotals

Julian Hyde jhyde at pentaho.com
Tue Sep 29 17:56:59 EDT 2009


I think you're running into  <http://jira.pentaho.com/browse/MONDRIAN-295>
http://jira.pentaho.com/browse/MONDRIAN-295. 


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Manuel Darveau
Sent: Tuesday, September 29, 2009 2:34 PM
To: mondrian at pentaho.org
Subject: [Mondrian] No result in query - Problem with VisualTotals


Hi all,

I am having troubles with an MDX query using the VisualTotals keyword. I
must first say that the MDX query is generated by the Simba O2X excel plugin
and that I can't really change it.

Basically, this is a simple query with 1 measure and 1 dimension but with a
filter on that dimension. My cube is representing rentals and there is a
dimension for subscriber types (1 hierarchy, 2 levels).

I am trying to display the number of rentals on the "Casual" subscriber
type. Here is the query generated by Simba O2X:
QUERY A:
with set [XL_Row_Dim_0] as
'VisualTotals(Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual])})))'
select NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Subscription].[All
Subscriptions]})}, [XL_Row_Dim_0])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME
ON COLUMNS
from [Rentals]
where [Measures].[RentalsCount]

If I execute this query in excel, I get no output (empty table) and in the
MDXQueryServlet, I get:

java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
	at java.util.ArrayList.RangeCheck(ArrayList.java:547)


	at java.util.ArrayList.get(ArrayList.java:322)
	at
mondrian.web.servlet.MDXQueryServlet.processRequest(MDXQueryServlet.java:95)
	at
mondrian.web.servlet.MDXQueryServlet.doGet(MDXQueryServlet.java:228)

I guess the servlet does not handle empty responses very well.

If I execute the same query without the VisualTotals, I get valid output:
QUERY B:
with set [XL_Row_Dim_0] as
'Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual])}))'
select NON EMPTY Hierarchize(Intersect({DrilldownLevel({[Subscription].[All
Subscriptions]})}, [XL_Row_Dim_0])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME
ON COLUMNS
from [Rentals]

[Subscription].[All Subscriptions]	 [Subscription].[All
Subscriptions].[Casual]	
246	 71	

I did some investigation on the content of XL_Row_Dim_0:
QUERY C:
select [Measures].[RentalsCount] ON COLUMNS,
VisualTotals(Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual])}))) ON ROWS
from [Rentals]
Results: 
 	 [Measures].[RentalsCount]	
[Subscription].[All Subscriptions]	 246	
[Subscription].[All Subscriptions].[Casual]	 71	
[Subscription].[All Subscriptions].[Casual].[24h]	 71	

And if I remove the VisualTotals:
QUERY D:
select [Measures].[RentalsCount] ON COLUMNS,
Distinct(Hierarchize({Ascendants([Subscription].[All
Subscriptions].[Casual]), Descendants([Subscription].[All
Subscriptions].[Casual])})) ON ROWS
from [Rentals]

I get the same result:
Results: 
 	 [Measures].[RentalsCount]	
[Subscription].[All Subscriptions]	 246	
[Subscription].[All Subscriptions].[Casual]	 71	
[Subscription].[All Subscriptions].[Casual].[24h]	 71	

I don't understand why query A and B does not return the same results if
query C and D output the same thing. The only difference between A and B is
the content of XL_Row_Dim_0 and I assume it is the same since C and D output
the same thing.

I have to confess that I am far from an expert in MDX query so I may be
missing something obvious here.

Here is a simplified version of my schema:
<Schema name="Bixi">
  <Cube name="Rentals" caption="Rentals" cache="true" enabled="true">
    <Table name="RentalFact">
    </Table>
     <Dimension type="StandardDimension" foreignKey="subscriptionType_id"
name="Subscription" caption="Subscription">
      <Hierarchy hasAll="true" primaryKey="id">
        <Table name="SubscriptionTypeDim">
        </Table>
        <Level name="UserType" column="userType_en_CA" type="String"
uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="User
Type">
        </Level>
        <Level name="SubscriptionType" column="name_en_CA" type="String"
uniqueMembers="false" levelType="Regular" hideMemberIf="Never"
caption="Subscription">
        </Level>
      </Hierarchy>
    </Dimension>
    <Measure name="RentalsCount" column="id" datatype="Numeric"
aggregator="count" caption="Nb Rentals" visible="true">
    </Measure>
  </Cube>
</Schema>

I can provide test data if needed (sql script for the database).

I understand that it is not easy to debug such problem without the actual
setup but any help will be greatly appreciated.

BTW, mondrian is a great product and help us a LOT in the analysis of our
data.

Thank you!

Manuel Darveau


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20090929/8dfcbd5c/attachment.html 


More information about the Mondrian mailing list