mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select NON EMPTY {[Date of Sale].[All Date of Sales].[2005].[Q2, 2005]} ON COLUMNS from [Sales] ]

Pentaho BI Platform Tracking | Deepak Paramanand | 8 years ago
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  1. 0

    I have set up a Private Dimension Date of Sale as part a Cube Sales of the standard Waremart.xml as <Cube name="Sales" cache="true" enabled="true"> <Table name="RT_SALESALL" schema="WAREMART"> </Table> <DimensionUsage source="Customer" name="Customer" foreignKey="CUST_ID"> </DimensionUsage> <DimensionUsage source="Product" name="Product" foreignKey="PROD_ID"> </DimensionUsage> <DimensionUsage source="Term" name="Payment Method" foreignKey="TERMS_ID"> </DimensionUsage> <DimensionUsage source="Promotion" name="Promotion" foreignKey="PROMO_ID"> </DimensionUsage> <Dimension type="TimeDimension" foreignKey="DATE_ID" name="Date of Sale"> <Hierarchy hasAll="true" primaryKey="DAY_ID"> <Table name="RT_TIMEBASE" schema="WAREMART"> </Table> <Level name="Year" table="RT_TIMEBASE" column="YEAR_NAME" ordinalColumn="YEAR_ID" type="String" uniqueMembers="true" levelType="TimeYears" hideMemberIf="IfBlankName"> </Level> <Level name="Quarter" table="RT_TIMEBASE" column="QUARTER_NAME" ordinalColumn="QUARTER_ID" type="Time" uniqueMembers="true" levelType="TimeQuarters" hideMemberIf="IfBlankName"> </Level> <Level name="Month" table="RT_TIMEBASE" column="MONTH_NAME" ordinalColumn="MONTH_ID" type="String" uniqueMembers="true" levelType="TimeMonths" hideMemberIf="IfBlankName"> </Level> <Level name="Week" table="RT_TIMEBASE" column="WEEK_DESC" ordinalColumn="WEEK_ID" type="Time" uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="IfBlankName"> </Level> <Level name="Day" table="RT_TIMEBASE" column="DAY_NAME" ordinalColumn="DAY_ID" type="Time" uniqueMembers="true" levelType="TimeDays" hideMemberIf="IfBlankName"> </Level> </Hierarchy> </Dimension> <Dimension type="TimeDimension" foreignKey="DATE_ID" name="Date of First Shipment"> <Hierarchy hasAll="true" primaryKey="DAY_ID"> <Table name="RT_TIMEBASE" schema="WAREMART"> </Table> <Level name="Year" table="RT_TIMEBASE" column="YEAR_NAME" type="String" uniqueMembers="true" levelType="TimeYears" hideMemberIf="IfBlankName"> </Level> <Level name="Quarter" table="RT_TIMEBASE" column="QUARTER_NAME" type="Time" uniqueMembers="true" levelType="TimeQuarters" hideMemberIf="IfBlankName"> </Level> <Level name="Month" table="RT_TIMEBASE" column="MONTH_NAME" type="String" uniqueMembers="true" levelType="TimeMonths" hideMemberIf="IfBlankName"> </Level> <Level name="Week" table="RT_TIMEBASE" column="WEEK_DESC" type="Time" uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="IfBlankName"> </Level> <Level name="Day" table="RT_TIMEBASE" column="DAY_NAME" type="Time" uniqueMembers="true" levelType="TimeDays" hideMemberIf="IfBlankName"> </Level> </Hierarchy> </Dimension> <Dimension type="TimeDimension" foreignKey="SHP_COMPLETE_DTE" name="Date Shipment Complete"> <Hierarchy hasAll="true" primaryKey="DAY_ID"> <Table name="RT_TIMEBASE" schema="WAREMART"> </Table> <Level name="Year" table="RT_TIMEBASE" column="YEAR_NAME" type="Time" uniqueMembers="true" levelType="TimeYears" hideMemberIf="IfBlankName"> </Level> <Level name="Quarter" table="RT_TIMEBASE" column="QUARTER_NAME" type="Time" uniqueMembers="true" levelType="TimeQuarters" hideMemberIf="IfBlankName"> </Level> <Level name="Month" table="RT_TIMEBASE" column="MONTH_NAME" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="IfBlankName"> </Level> <Level name="Week" table="RT_TIMEBASE" column="WEEK_DESC" type="Time" uniqueMembers="true" levelType="TimeWeeks" hideMemberIf="IfBlankName"> </Level> <Level name="Day" table="RT_TIMEBASE" column="DAY_NAME" type="Time" uniqueMembers="true" levelType="TimeDays" hideMemberIf="IfBlankName"> </Level> </Hierarchy> </Dimension> <DimensionUsage source="Gender" name="Customer Gender" foreignKey="CUST_ID"> </DimensionUsage> <DimensionUsage source="Education" name="Customer Education Level" foreignKey="CUST_ID"> </DimensionUsage> <DimensionUsage source="Time" name="Time" caption="Date of Product Sale" foreignKey="DATE_ID"> </DimensionUsage> <Measure name="Unit Sales" column="PRICE" datatype="Numeric" formatString="$ ###," aggregator="sum" visible="true"> </Measure> <Measure name="Quantity" column="QTY" datatype="Numeric" formatString="###," aggregator="sum" visible="true"> </Measure> <Measure name="Dollar Sales" column="TOTAL" datatype="Numeric" formatString="$ ###," aggregator="sum" visible="true"> </Measure> </Cube> Here the Quarters are in the form Q2, 2005 etc. When i run the query : select {[Date of Sale].[Quarter].[Q2, 2005]} on columns from Sales i get the error : 2009-04-27 17:36:05,843 ERROR [mondrian.gui.QueryPanel] mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select NON EMPTY {[Date of Sale].[All Date of Sales].[2005].[Q2, 2005]} ON COLUMNS from [Sales] ] at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:811) at mondrian.olap.Util.newInternal(Util.java:1472) at mondrian.olap.Util.newError(Util.java:1488) at mondrian.rolap.RolapConnection.execute(RolapConnection.java:589) at mondrian.gui.QueryPanel.executeButtonActionPerformed(QueryPanel.java:235) at mondrian.gui.QueryPanel.access$100(QueryPanel.java:53) at mondrian.gui.QueryPanel$3.actionPerformed(QueryPanel.java:176) at javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.setPressed(Unknown Source) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source) at java.awt.Component.processMouseEvent(Unknown Source) at javax.swing.JComponent.processMouseEvent(Unknown Source) at java.awt.Component.processEvent(Unknown Source) at java.awt.Container.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Window.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source) Caused by: java.lang.NumberFormatException: Illegal TIME literal: Q2, 2005 at mondrian.spi.impl.JdbcDialectImpl.quoteTimeLiteral(JdbcDialectImpl.java:370) at mondrian.spi.Dialect$Datatype$6.quoteValue(Dialect.java:720) at mondrian.spi.impl.JdbcDialectImpl.quote(JdbcDialectImpl.java:619) at mondrian.rolap.agg.ValueColumnPredicate.toSql(ValueColumnPredicate.java:163) at mondrian.rolap.agg.ListColumnPredicate.toSql(ListColumnPredicate.java:293) at mondrian.rolap.RolapStar$Column.createInExpr(RolapStar.java:1276) at mondrian.rolap.agg.AbstractQuerySpec.nonDistinctGenerateSql(AbstractQuerySpec.java:98) at mondrian.rolap.agg.AbstractQuerySpec.generateSqlQuery(AbstractQuerySpec.java:169) at mondrian.rolap.agg.AggregationManager.generateSql(AggregationManager.java:229) at mondrian.rolap.agg.SegmentLoader.createExecuteSql(SegmentLoader.java:337) at mondrian.rolap.agg.SegmentLoader.load(SegmentLoader.java:75) at mondrian.rolap.agg.Aggregation.load(Aggregation.java:171) at mondrian.rolap.agg.AggregationManager.loadAggregation(AggregationManager.java:89) at mondrian.rolap.FastBatchingCellReader$Batch.loadAggregation(FastBatchingCellReader.java:493) at mondrian.rolap.FastBatchingCellReader$Batch.loadAggregation(FastBatchingCellReader.java:430) at mondrian.rolap.FastBatchingCellReader.loadAggregation(FastBatchingCellReader.java:206) at mondrian.rolap.FastBatchingCellReader.loadAggregations(FastBatchingCellReader.java:187) at mondrian.rolap.RolapResult.executeBody(RolapResult.java:792) at mondrian.rolap.RolapResult.<init>(RolapResult.java:414) at mondrian.rolap.RolapConnection.execute(RolapConnection.java:560) ... 28 more 2009-04-27 17:36:05,843 ERROR [mondrian.gui.QueryPanel] java.lang.NumberFormatException: Illegal TIME literal: Q2, 2005 at mondrian.spi.impl.JdbcDialectImpl.quoteTimeLiteral(JdbcDialectImpl.java:370) at mondrian.spi.Dialect$Datatype$6.quoteValue(Dialect.java:720) at mondrian.spi.impl.JdbcDialectImpl.quote(JdbcDialectImpl.java:619) at mondrian.rolap.agg.ValueColumnPredicate.toSql(ValueColumnPredicate.java:163) at mondrian.rolap.agg.ListColumnPredicate.toSql(ListColumnPredicate.java:293) at mondrian.rolap.RolapStar$Column.createInExpr(RolapStar.java:1276) at mondrian.rolap.agg.AbstractQuerySpec.nonDistinctGenerateSql(AbstractQuerySpec.java:98) at mondrian.rolap.agg.AbstractQuerySpec.generateSqlQuery(AbstractQuerySpec.java:169) at mondrian.rolap.agg.AggregationManager.generateSql(AggregationManager.java:229) at mondrian.rolap.agg.SegmentLoader.createExecuteSql(SegmentLoader.java:337) at mondrian.rolap.agg.SegmentLoader.load(SegmentLoader.java:75) at mondrian.rolap.agg.Aggregation.load(Aggregation.java:171) at mondrian.rolap.agg.AggregationManager.loadAggregation(AggregationManager.java:89) at mondrian.rolap.FastBatchingCellReader$Batch.loadAggregation(FastBatchingCellReader.java:493) at mondrian.rolap.FastBatchingCellReader$Batch.loadAggregation(FastBatchingCellReader.java:430) at mondrian.rolap.FastBatchingCellReader.loadAggregation(FastBatchingCellReader.java:206) at mondrian.rolap.FastBatchingCellReader.loadAggregations(FastBatchingCellReader.java:187) at mondrian.rolap.RolapResult.executeBody(RolapResult.java:792) at mondrian.rolap.RolapResult.<init>(RolapResult.java:414) at mondrian.rolap.RolapConnection.execute(RolapConnection.java:560) at mondrian.gui.QueryPanel.executeButtonActionPerformed(QueryPanel.java:235) at mondrian.gui.QueryPanel.access$100(QueryPanel.java:53) at mondrian.gui.QueryPanel$3.actionPerformed(QueryPanel.java:176) at javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.setPressed(Unknown Source) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source) at java.awt.Component.processMouseEvent(Unknown Source) at javax.swing.JComponent.processMouseEvent(Unknown Source) at java.awt.Component.processEvent(Unknown Source) at java.awt.Container.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Window.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source) on the schema workbench. The Pentaho server doesnt even report this error. What is wrong with my schema file?

    Pentaho BI Platform Tracking | 8 years ago | Deepak Paramanand
    mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select NON EMPTY {[Date of Sale].[All Date of Sales].[2005].[Q2, 2005]} ON COLUMNS from [Sales] ]

    Root Cause Analysis

    1. java.lang.NumberFormatException

      Illegal TIME literal: Q2, 2005

      at mondrian.spi.impl.JdbcDialectImpl.quoteTimeLiteral()
    2. mondrian.spi.impl
      JdbcDialectImpl.quoteTimeLiteral
      1. mondrian.spi.impl.JdbcDialectImpl.quoteTimeLiteral(JdbcDialectImpl.java:370)
      1 frame
    3. mondrian.spi
      Dialect$Datatype$6.quoteValue
      1. mondrian.spi.Dialect$Datatype$6.quoteValue(Dialect.java:720)
      1 frame
    4. mondrian.spi.impl
      JdbcDialectImpl.quote
      1. mondrian.spi.impl.JdbcDialectImpl.quote(JdbcDialectImpl.java:619)
      1 frame
    5. mondrian.rolap.agg
      ListColumnPredicate.toSql
      1. mondrian.rolap.agg.ValueColumnPredicate.toSql(ValueColumnPredicate.java:163)
      2. mondrian.rolap.agg.ListColumnPredicate.toSql(ListColumnPredicate.java:293)
      2 frames
    6. mondrian.rolap
      RolapStar$Column.createInExpr
      1. mondrian.rolap.RolapStar$Column.createInExpr(RolapStar.java:1276)
      1 frame
    7. mondrian.rolap.agg
      AggregationManager.loadAggregation
      1. mondrian.rolap.agg.AbstractQuerySpec.nonDistinctGenerateSql(AbstractQuerySpec.java:98)
      2. mondrian.rolap.agg.AbstractQuerySpec.generateSqlQuery(AbstractQuerySpec.java:169)
      3. mondrian.rolap.agg.AggregationManager.generateSql(AggregationManager.java:229)
      4. mondrian.rolap.agg.SegmentLoader.createExecuteSql(SegmentLoader.java:337)
      5. mondrian.rolap.agg.SegmentLoader.load(SegmentLoader.java:75)
      6. mondrian.rolap.agg.Aggregation.load(Aggregation.java:171)
      7. mondrian.rolap.agg.AggregationManager.loadAggregation(AggregationManager.java:89)
      7 frames
    8. mondrian.rolap
      RolapConnection.execute
      1. mondrian.rolap.FastBatchingCellReader$Batch.loadAggregation(FastBatchingCellReader.java:493)
      2. mondrian.rolap.FastBatchingCellReader$Batch.loadAggregation(FastBatchingCellReader.java:430)
      3. mondrian.rolap.FastBatchingCellReader.loadAggregation(FastBatchingCellReader.java:206)
      4. mondrian.rolap.FastBatchingCellReader.loadAggregations(FastBatchingCellReader.java:187)
      5. mondrian.rolap.RolapResult.executeBody(RolapResult.java:792)
      6. mondrian.rolap.RolapResult.<init>(RolapResult.java:414)
      7. mondrian.rolap.RolapConnection.execute(RolapConnection.java:560)
      7 frames
    9. mondrian.gui
      QueryPanel$3.actionPerformed
      1. mondrian.gui.QueryPanel.executeButtonActionPerformed(QueryPanel.java:235)
      2. mondrian.gui.QueryPanel.access$100(QueryPanel.java:53)
      3. mondrian.gui.QueryPanel$3.actionPerformed(QueryPanel.java:176)
      3 frames
    10. Java RT
      EventDispatchThread.run
      1. javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      2. javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
      3. javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      4. javax.swing.DefaultButtonModel.setPressed(Unknown Source)
      5. javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
      6. java.awt.Component.processMouseEvent(Unknown Source)
      7. javax.swing.JComponent.processMouseEvent(Unknown Source)
      8. java.awt.Component.processEvent(Unknown Source)
      9. java.awt.Container.processEvent(Unknown Source)
      10. java.awt.Component.dispatchEventImpl(Unknown Source)
      11. java.awt.Container.dispatchEventImpl(Unknown Source)
      12. java.awt.Component.dispatchEvent(Unknown Source)
      13. java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      14. java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      15. java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      16. java.awt.Container.dispatchEventImpl(Unknown Source)
      17. java.awt.Window.dispatchEventImpl(Unknown Source)
      18. java.awt.Component.dispatchEvent(Unknown Source)
      19. java.awt.EventQueue.dispatchEvent(Unknown Source)
      20. java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
      21. java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
      22. java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      23. java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      24. java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      25. java.awt.EventDispatchThread.run(Unknown Source)
      25 frames