[Mondrian] No result in query - Problem with VisualTotals

Manuel Darveau manueldarveau at gmail.com
Thu Oct 1 12:15:54 EDT 2009


Hi,

Thanks for the fast reply!

Do you have any idea when will this be possibly fixed?

Thanks again.

Manuel

On Tue, Sep 29, 2009 at 5:56 PM, Julian Hyde <jhyde at pentaho.com> wrote:

>  I think you're running into 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
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20091001/86ad8dc2/attachment.html 


More information about the Mondrian mailing list