#72 XMLA: Issue with query involving multi level dimensions

[Sandro Impastato]

Execution environment:

Eclipse RCP plugin development project with JDK 1.6
olap4j-1.0.1.500.jar
olap4j-xmla-1.0.1.500.jar

Server environment:
Pentaho CE 4.1 (with Mondrian 3.3) on Tomcat 6.0 with mySql JDBC driver 5.1.18
Relational DB: Infobright CE 4.0.6

The connection works fine: I can correctly connect to Mondrian and explore the catalogue (cubes, dimension, and so on).

The issue occours when I try to perform some queries, for example I have my Daily Time dimension with a hierarchy made by Year - Month and Day. If in my query I use the Year level, the query works fine, if I use the Month or the Day level I occour in the following Exception:

java.lang.IndexOutOfBoundsException: Index: 2, Size: 2
at java.util.ArrayList.RangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at org.olap4j.driver.xmla.DeferredNamedListImpl.get(DeferredNamedListImpl.java:104)
at org.olap4j.driver.xmla.DeferredNamedListImpl.get(DeferredNamedListImpl.java:48)
at org.olap4j.driver.xmla.XmlaOlap4jCellSet.populate(XmlaOlap4jCellSet.java:265)
at org.olap4j.driver.xmla.XmlaOlap4jStatement.executeOlapQuery(XmlaOlap4jStatement.java:353)
at org.olap4j.driver.xmla.XmlaOlap4jStatement.executeOlapQuery(XmlaOlap4jStatement.java:362)
at com.dataexplorer.olapmodel.OlapModelHelper.executeMDXQuery(OlapModelHelper.java:179)
at com.dataexplorer.commands.ExecuteQueryCommand.execute(ExecuteQueryCommand.java:24)
at org.eclipse.ui.internal.handlers.HandlerProxy.execute(HandlerProxy.java:293)
at org.eclipse.core.commands.Command.executeWithChecks(Command.java:476)
at org.eclipse.core.commands.ParameterizedCommand.executeWithChecks(ParameterizedCommand.java:508)
at org.eclipse.ui.internal.handlers.HandlerService.executeCommand(HandlerService.java:169)
at org.eclipse.ui.internal.handlers.SlaveHandlerService.executeCommand(SlaveHandlerService.java:241)
at org.eclipse.ui.menus.CommandContributionItem.handleWidgetSelection(CommandContributionItem.java:829)
at org.eclipse.ui.menus.CommandContributionItem.access$19(CommandContributionItem.java:815)
at org.eclipse.ui.menus.CommandContributionItem$5.handleEvent(CommandContributionItem.java:805)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2701)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2665)
at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2499)
at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:679)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:668)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
at com.dataexplorer.Application.start(Application.java:20)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577)
at org.eclipse.equinox.launcher.Main.run(Main.java:1410)
at org.eclipse.equinox.launcher.Main.main(Main.java:1386)

This is the query which caused the exception:

SELECT
[Measures].[Number of events] ON COLUMNS,
[Daily Time - Event Creation].[default].[Month].members ON ROWS
FROM [Event Objects]

This is how the Daily Time dimension is defined in my Mondrian schema file (note that in the cube the Daily Time dimension has several usages):

<dimension type="StandardDimension" visible="true" highcardinality="false" name="Daily Time">
<hierarchy name="default" visible="true" hasall="true">
</hierarchy></dimension>

<level name="Year" visible="true" column="id_year" type="Integer" uniquemembers="false" leveltype="Regular" hidememberif="Never">
</level>
<level name="Month" visible="true" column="id_yearmonth" namecolumn="des_month_year" type="Integer" uniquemembers="false" leveltype="Regular" hidememberif="Never">
</level>
<level name="Day" visible="true" column="idKey" namecolumn="des_month_year_day" type="Integer" uniquemembers="true" leveltype="Regular" hidememberif="Never">
<property name="Number of days in current month" column="num_days_in_the_month" type="String">
</property>
<property name="Number of days in current year" column="num_days_in_the_year" type="String">
</property>
<property name="Number of day in the month" column="num_day_of_the_month" type="String">
</property>
<property name="Number of day in the year" column="num_day_of_the_year" type="String">
</property>
</level>

And this is how the Measure is defined:

<measure name="Number of events" column="cod_event_id" aggregator="distinct-count" visible="true">
</measure>

This issue occours whenever I try to perform a query on dimensions with more than one level and never happens on the top most level (for example in the Year level of the Daily Time dimension).

Also note that the query above works just fine on Saiku or other tools, included the old wREX which implemented its own XMLA api.

This is the JAVA code which I use:

// Contains the connection to the MBIS Mondrian schema
private static OlapConnection olapConnection;

// Loads the driver class
Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");

    // Unwraps the SQL Connection object to an OlapConnection one
    olapConnection = (OlapConnection) DriverManager.getConnection
    (
            "jdbc:xmla:Server=" + serverUrl
            , serverUsername
            , serverPassword
    );

public static CellSet executeMDXQuery(String mdx)
{
CellSet result = null;

    try 
    {
        OlapStatement statement = olapConnection.createStatement();

        MdxParserFactory pFactory = olapConnection.getParserFactory();
        MdxParser parser = pFactory.createMdxParser(olapConnection);
        MdxValidator validator = pFactory.createMdxValidator(olapConnection);
        SelectNode parsedObject = validator.validateSelect(parser.parseSelect(mdx));
        System.out.println(parsedObject);
        // this is the line on which in the stack trace the error occours
        result = statement.executeOlapQuery(parsedObject);

        CellSetFormatter formatter =
                new RectangularCellSetFormatter(false);
            formatter.format(result, new PrintWriter(System.out, true));

        return result;
    }
    catch (IndexOutOfBoundsException ex)
    {
        ex.printStackTrace();
        return null;
    }
    catch (OlapException ex) 
    {
        ex.printStackTrace();
        return null;
    }
}

Thanks for your help.