Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-695

Unexpected data set may returned when MDX slicer contains multiple dimensions

    Details

      Description

      By default "mondrian.olap.fun.crossjoin.optimizer.size" is set to zero and "mondrian.native.nonempty.enable" is set to true, this way mondrian will calculate non empty values via native SQL, and in many cases, it works as expected. However, if complex slicer involved in MDX query, mondrian may return unexpected data set.

      For example, the below MDX query returns nothing from mondrian with default settings:
      select NON EMPTY (

      {[Status].Children} * {[Measures].[ItemCount]}) ON COLUMNS,
      NON EMPTY [Time].Children ON ROWS
      from [SpaceView]
      where ({[Space].[All Spaces]} * {([Date].[All Dates].[2010].[1].[31] : [Date].[All Dates].[2010].[2].[6])})

      Fortunately, When I switched to following settings, I got all I need.
      mondrian.native.nonempty.enable=false
      mondrian.olap.fun.crossjoin.optimizer.size=65535

      Looks like I have to disable crossjoin optimizer and native SQL for non empty to make it work. To make it a little bit easier for you guys, here below are SQL logs for different settings:
      1) default mondrian settings
      0 [http-8080-1] DEBUG mondrian.sql - 0: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."year_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      "dim_date"."year_num" = 2010
      group by
      "dim_date"."year_num"
      order by
      "dim_date"."year_num" ASC
      ]
      0 [http-8080-1] DEBUG mondrian.sql - 0: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."year_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      "dim_date"."year_num" = 2010
      group by
      "dim_date"."year_num"
      order by
      "dim_date"."year_num" ASC
      ]
      140 [http-8080-1] DEBUG mondrian.sql - 0: , exec 125 ms
      140 [http-8080-1] DEBUG mondrian.sql - 0: , exec 125 ms
      156 [http-8080-1] DEBUG mondrian.sql - 0: , exec+fetch 141 ms, 1 rows
      156 [http-8080-1] DEBUG mondrian.sql - 0: , exec+fetch 141 ms, 1 rows
      156 [http-8080-1] DEBUG mondrian.sql - 1: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."month_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."year_num" = 2010) and
      "dim_date"."month_num" = 1
      group by
      "dim_date"."month_num"
      order by
      "dim_date"."month_num" ASC
      ]
      156 [http-8080-1] DEBUG mondrian.sql - 1: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."month_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."year_num" = 2010) and
      "dim_date"."month_num" = 1
      group by
      "dim_date"."month_num"
      order by
      "dim_date"."month_num" ASC
      ]
      297 [http-8080-1] DEBUG mondrian.sql - 1: , exec 126 ms
      297 [http-8080-1] DEBUG mondrian.sql - 1: , exec 126 ms
      297 [http-8080-1] DEBUG mondrian.sql - 1: , exec+fetch 126 ms, 1 rows
      297 [http-8080-1] DEBUG mondrian.sql - 1: , exec+fetch 126 ms, 1 rows
      297 [http-8080-1] DEBUG mondrian.sql - 2: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 1 and "dim_date"."year_num" = 2010) and
      "dim_date"."day_of_month" = 31
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      297 [http-8080-1] DEBUG mondrian.sql - 2: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 1 and "dim_date"."year_num" = 2010) and
      "dim_date"."day_of_month" = 31
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      453 [http-8080-1] DEBUG mondrian.sql - 2: , exec 141 ms
      453 [http-8080-1] DEBUG mondrian.sql - 2: , exec 141 ms
      468 [http-8080-1] DEBUG mondrian.sql - 2: , exec+fetch 156 ms, 1 rows
      468 [http-8080-1] DEBUG mondrian.sql - 2: , exec+fetch 156 ms, 1 rows
      468 [http-8080-1] DEBUG mondrian.sql - 3: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."month_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."year_num" = 2010) and
      "dim_date"."month_num" = 2
      group by
      "dim_date"."month_num"
      order by
      "dim_date"."month_num" ASC
      ]
      468 [http-8080-1] DEBUG mondrian.sql - 3: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."month_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."year_num" = 2010) and
      "dim_date"."month_num" = 2
      group by
      "dim_date"."month_num"
      order by
      "dim_date"."month_num" ASC
      ]
      609 [http-8080-1] DEBUG mondrian.sql - 3: , exec 125 ms
      609 [http-8080-1] DEBUG mondrian.sql - 3: , exec 125 ms
      609 [http-8080-1] DEBUG mondrian.sql - 3: , exec+fetch 125 ms, 1 rows
      609 [http-8080-1] DEBUG mondrian.sql - 3: , exec+fetch 125 ms, 1 rows
      609 [http-8080-1] DEBUG mondrian.sql - 4: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 2 and "dim_date"."year_num" = 2010) and
      "dim_date"."day_of_month" = 6
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      609 [http-8080-1] DEBUG mondrian.sql - 4: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 2 and "dim_date"."year_num" = 2010) and
      "dim_date"."day_of_month" = 6
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      765 [http-8080-1] DEBUG mondrian.sql - 4: , exec 140 ms
      765 [http-8080-1] DEBUG mondrian.sql - 4: , exec 140 ms
      765 [http-8080-1] DEBUG mondrian.sql - 4: , exec+fetch 140 ms, 1 rows
      765 [http-8080-1] DEBUG mondrian.sql - 4: , exec+fetch 140 ms, 1 rows
      1359 [http-8080-1] DEBUG mondrian.mdx - 0: select NON EMPTY ({[Status].Children}

      *

      {[Measures].[ItemCount]}) ON COLUMNS
      ,
      NON EMPTY [Time].Children ON ROWS
      from [SpaceView]
      where ({[Space].[All Spaces]} * {([Date].[All Dates].[2010].[1].[31] : [Date].[All Dates].[2010].[2].[6])})

      1359 [http-8080-1] DEBUG mondrian.mdx - 0: select NON EMPTY ({[Status].Children} * {[Measures].[ItemCount]}

      ) ON COLUMNS
      ,
      NON EMPTY [Time].Children ON ROWS
      from [SpaceView]
      where (

      {[Space].[All Spaces]}

      *

      {([Date].[All Dates].[2010].[1].[31] : [Date].[All Dates].[2010].[2].[6])}

      )

      1375 [http-8080-1] DEBUG mondrian.sql - 5: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."month_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."year_num" = 2010)
      group by
      "dim_date"."month_num"
      order by
      "dim_date"."month_num" ASC
      ]
      1375 [http-8080-1] DEBUG mondrian.sql - 5: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."month_num" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."year_num" = 2010)
      group by
      "dim_date"."month_num"
      order by
      "dim_date"."month_num" ASC
      ]
      1485 [http-8080-1] DEBUG mondrian.sql - 5: , exec 94 ms
      1485 [http-8080-1] DEBUG mondrian.sql - 5: , exec 94 ms
      1500 [http-8080-1] DEBUG mondrian.sql - 5: , exec+fetch 109 ms, 12 rows
      1500 [http-8080-1] DEBUG mondrian.sql - 5: , exec+fetch 109 ms, 12 rows
      1500 [http-8080-1] DEBUG mondrian.sql - 6: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 1 and "dim_date"."year_num" = 2010)
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      1500 [http-8080-1] DEBUG mondrian.sql - 6: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 1 and "dim_date"."year_num" = 2010)
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      1656 [http-8080-1] DEBUG mondrian.sql - 6: , exec 140 ms
      1656 [http-8080-1] DEBUG mondrian.sql - 6: , exec 140 ms
      1656 [http-8080-1] DEBUG mondrian.sql - 6: , exec+fetch 140 ms, 31 rows
      1656 [http-8080-1] DEBUG mondrian.sql - 6: , exec+fetch 140 ms, 31 rows
      1656 [http-8080-1] DEBUG mondrian.sql - 7: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."year_num" as "c0"
      from
      "dim_date" as "dim_date"
      group by
      "dim_date"."year_num"
      order by
      "dim_date"."year_num" ASC
      ]
      1656 [http-8080-1] DEBUG mondrian.sql - 7: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."year_num" as "c0"
      from
      "dim_date" as "dim_date"
      group by
      "dim_date"."year_num"
      order by
      "dim_date"."year_num" ASC
      ]
      1797 [http-8080-1] DEBUG mondrian.sql - 7: , exec 125 ms
      1797 [http-8080-1] DEBUG mondrian.sql - 7: , exec 125 ms
      1797 [http-8080-1] DEBUG mondrian.sql - 7: , exec+fetch 125 ms, 2 rows
      1797 [http-8080-1] DEBUG mondrian.sql - 7: , exec+fetch 125 ms, 2 rows
      1797 [http-8080-1] DEBUG mondrian.sql - 8: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 2 and "dim_date"."year_num" = 2010)
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      1797 [http-8080-1] DEBUG mondrian.sql - 8: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_date"."day_of_month" as "c0"
      from
      "dim_date" as "dim_date"
      where
      ("dim_date"."month_num" = 2 and "dim_date"."year_num" = 2010)
      group by
      "dim_date"."day_of_month"
      order by
      "dim_date"."day_of_month" ASC
      ]
      1860 [http-8080-1] DEBUG mondrian.sql - 8: , exec 47 ms
      1860 [http-8080-1] DEBUG mondrian.sql - 8: , exec 47 ms
      1860 [http-8080-1] DEBUG mondrian.sql - 8: , exec+fetch 47 ms, 28 rows
      1860 [http-8080-1] DEBUG mondrian.sql - 8: , exec+fetch 47 ms, 28 rows
      1875 [http-8080-1] DEBUG mondrian.sql - 9: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_item_status"."status_id" as "c0",
      "dim_item_status"."status_name" as "c1"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id"
      group by
      "dim_item_status"."status_id",
      "dim_item_status"."status_name"
      order by
      "dim_item_status"."status_id" ASC
      ]
      1875 [http-8080-1] DEBUG mondrian.sql - 9: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_item_status"."status_id" as "c0",
      "dim_item_status"."status_name" as "c1"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id"
      group by
      "dim_item_status"."status_id",
      "dim_item_status"."status_name"
      order by
      "dim_item_status"."status_id" ASC
      ]
      2110 [http-8080-1] DEBUG mondrian.sql - 9: , exec 219 ms
      2110 [http-8080-1] DEBUG mondrian.sql - 9: , exec 219 ms
      2125 [http-8080-1] DEBUG mondrian.sql - 9: , exec+fetch 234 ms, 2 rows
      2125 [http-8080-1] DEBUG mondrian.sql - 9: , exec+fetch 234 ms, 2 rows
      2172 [http-8080-1] DEBUG mondrian.sql - 10: Segment.load: executing sql [
      select
      "dim_date"."year_num" as "c0",
      "dim_date"."month_num" as "c1",
      "dim_date"."day_of_month" as "c2",
      count(distinct "fact_space_history"."item_id") as "m0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6
      group by
      "dim_date"."year_num",
      "dim_date"."month_num",
      "dim_date"."day_of_month"
      ]
      2172 [http-8080-1] DEBUG mondrian.sql - 10: Segment.load: executing sql [
      select
      "dim_date"."year_num" as "c0",
      "dim_date"."month_num" as "c1",
      "dim_date"."day_of_month" as "c2",
      count(distinct "fact_space_history"."item_id") as "m0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6
      group by
      "dim_date"."year_num",
      "dim_date"."month_num",
      "dim_date"."day_of_month"
      ]
      2360 [http-8080-1] DEBUG mondrian.sql - 10: , exec 172 ms
      2360 [http-8080-1] DEBUG mondrian.sql - 10: , exec 172 ms
      2376 [http-8080-1] DEBUG mondrian.sql - 10: , exec+fetch 188 ms, 1 rows
      2376 [http-8080-1] DEBUG mondrian.sql - 10: , exec+fetch 188 ms, 1 rows
      2376 [http-8080-1] DEBUG mondrian.sql - 11: RolapStar.Column.getCardinality: executing sql [
      select
      count(distinct "dim_item_status"."status_id") as "c0"
      from
      "dim_item_status" as "dim_item_status"
      ]
      2376 [http-8080-1] DEBUG mondrian.sql - 11: RolapStar.Column.getCardinality: executing sql [
      select
      count(distinct "dim_item_status"."status_id") as "c0"
      from
      "dim_item_status" as "dim_item_status"
      ]
      2438 [http-8080-1] DEBUG mondrian.sql - 11: , exec 62 ms
      2438 [http-8080-1] DEBUG mondrian.sql - 11: , exec 62 ms
      2454 [http-8080-1] DEBUG mondrian.sql - 11: , exec+fetch 78 ms, 1 rows
      2454 [http-8080-1] DEBUG mondrian.sql - 11: , exec+fetch 78 ms, 1 rows
      2454 [http-8080-1] DEBUG mondrian.sql - 12: Segment.load: executing sql [
      select
      "dim_date"."year_num" as "c0",
      "dim_date"."month_num" as "c1",
      "dim_date"."day_of_month" as "c2",
      "dim_item_status"."status_id" as "c3",
      count(distinct "fact_space_history"."item_id") as "m0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id" and
      "dim_item_status"."status_id" in (0, 1)
      group by
      "dim_date"."year_num",
      "dim_date"."month_num",
      "dim_date"."day_of_month",
      "dim_item_status"."status_id"
      ]
      2454 [http-8080-1] DEBUG mondrian.sql - 12: Segment.load: executing sql [
      select
      "dim_date"."year_num" as "c0",
      "dim_date"."month_num" as "c1",
      "dim_date"."day_of_month" as "c2",
      "dim_item_status"."status_id" as "c3",
      count(distinct "fact_space_history"."item_id") as "m0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id" and
      "dim_item_status"."status_id" in (0, 1)
      group by
      "dim_date"."year_num",
      "dim_date"."month_num",
      "dim_date"."day_of_month",
      "dim_item_status"."status_id"
      ]
      2719 [http-8080-1] DEBUG mondrian.sql - 12: , exec 234 ms
      2719 [http-8080-1] DEBUG mondrian.sql - 12: , exec 234 ms
      2735 [http-8080-1] DEBUG mondrian.sql - 12: , exec+fetch 250 ms, 2 rows
      2735 [http-8080-1] DEBUG mondrian.sql - 12: , exec+fetch 250 ms, 2 rows
      2735 [http-8080-1] DEBUG mondrian.sql - 13: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_item_status"."status_id" as "c0",
      "dim_item_status"."status_name" as "c1"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id"
      group by
      "dim_item_status"."status_id",
      "dim_item_status"."status_name"
      order by
      "dim_item_status"."status_id" ASC
      ]
      2735 [http-8080-1] DEBUG mondrian.sql - 13: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_item_status"."status_id" as "c0",
      "dim_item_status"."status_name" as "c1"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id"
      group by
      "dim_item_status"."status_id",
      "dim_item_status"."status_name"
      order by
      "dim_item_status"."status_id" ASC
      ]
      2813 [http-8080-1] DEBUG mondrian.sql - 13: , exec 62 ms
      2813 [http-8080-1] DEBUG mondrian.sql - 13: , exec 62 ms
      2813 [http-8080-1] DEBUG mondrian.sql - 13: , exec+fetch 62 ms, 2 rows
      2813 [http-8080-1] DEBUG mondrian.sql - 13: , exec+fetch 62 ms, 2 rows
      2813 [http-8080-1] DEBUG mondrian.sql - 14: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_time"."hour_num" as "c0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_time" as "dim_time"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."time_id" = "dim_time"."time_id"
      group by
      "dim_time"."hour_num"
      order by
      "dim_time"."hour_num" ASC
      ]
      2813 [http-8080-1] DEBUG mondrian.sql - 14: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_time"."hour_num" as "c0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_time" as "dim_time"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."time_id" = "dim_time"."time_id"
      group by
      "dim_time"."hour_num"
      order by
      "dim_time"."hour_num" ASC
      ]
      3141 [http-8080-1] DEBUG mondrian.sql - 14: , exec 312 ms
      3141 [http-8080-1] DEBUG mondrian.sql - 14: , exec 312 ms
      3141 [http-8080-1] DEBUG mondrian.sql - 14: , exec+fetch 312 ms, 2 rows
      3141 [http-8080-1] DEBUG mondrian.sql - 14: , exec+fetch 312 ms, 2 rows
      3173 [http-8080-1] DEBUG mondrian.sql - 15: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_item_status"."status_id" as "c0",
      "dim_item_status"."status_name" as "c1"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id"
      group by
      "dim_item_status"."status_id",
      "dim_item_status"."status_name"
      order by
      "dim_item_status"."status_id" ASC
      ]
      3173 [http-8080-1] DEBUG mondrian.sql - 15: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_item_status"."status_id" as "c0",
      "dim_item_status"."status_name" as "c1"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."status" = "dim_item_status"."status_id"
      group by
      "dim_item_status"."status_id",
      "dim_item_status"."status_name"
      order by
      "dim_item_status"."status_id" ASC
      ]
      3267 [http-8080-1] DEBUG mondrian.sql - 15: , exec 63 ms
      3267 [http-8080-1] DEBUG mondrian.sql - 15: , exec 63 ms
      3267 [http-8080-1] DEBUG mondrian.sql - 15: , exec+fetch 63 ms, 2 rows
      3267 [http-8080-1] DEBUG mondrian.sql - 15: , exec+fetch 63 ms, 2 rows
      3282 [http-8080-1] DEBUG mondrian.sql - 16: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_time"."hour_num" as "c0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_time" as "dim_time"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."time_id" = "dim_time"."time_id"
      group by
      "dim_time"."hour_num"
      order by
      "dim_time"."hour_num" ASC
      ]
      3282 [http-8080-1] DEBUG mondrian.sql - 16: SqlMemberSource.getMemberChildren: executing sql [
      select
      "dim_time"."hour_num" as "c0"
      from
      "dim_date" as "dim_date",
      "fact_space_history" as "fact_space_history",
      "dim_time" as "dim_time"
      where
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      "dim_date"."year_num" = 2010 and
      "dim_date"."month_num" = 2 and
      "dim_date"."day_of_month" = 6 and
      "fact_space_history"."time_id" = "dim_time"."time_id"
      group by
      "dim_time"."hour_num"
      order by
      "dim_time"."hour_num" ASC
      ]
      3376 [http-8080-1] DEBUG mondrian.sql - 16: , exec 78 ms
      3376 [http-8080-1] DEBUG mondrian.sql - 16: , exec 78 ms
      3376 [http-8080-1] DEBUG mondrian.sql - 16: , exec+fetch 78 ms, 2 rows
      3376 [http-8080-1] DEBUG mondrian.sql - 16: , exec+fetch 78 ms, 2 rows
      3392 [http-8080-1] DEBUG mondrian.mdx - 0: exec: 2017 ms
      3392 [http-8080-1] DEBUG mondrian.mdx - 0: exec: 2017 ms

      2) turn off native SQL for non empty and disable crossjoin optimizer
      ...
      select
      "dim_time"."hour_num" as "c0",
      "dim_item_status"."status_id" as "c1",
      count(distinct "fact_space_history"."item_id") as "m0"
      from
      "dim_time" as "dim_time",
      "fact_space_history" as "fact_space_history",
      "dim_item_status" as "dim_item_status",
      "dim_date" as "dim_date"
      where
      "fact_space_history"."time_id" = "dim_time"."time_id" and
      "fact_space_history"."status" = "dim_item_status"."status_id" and
      "fact_space_history"."date_id" = "dim_date"."date_id" and
      ((("dim_date"."year_num", "dim_date"."month_num", "dim_date"."day_of_month") in ((2010, 1, 31), (2010, 2, 1), (2010,
      2, 2), (2010, 2, 3), (2010, 2, 4), (2010, 2, 5), (2010, 2, 6))))
      group by
      "dim_time"."hour_num",
      "dim_item_status"."status_id"
      ...

      Hope this can be solved in the next version. It really took me some time to find the work around

      Thanks,
      Derek Wu

      1. CrossJoinFunDef_MONDRIAN-695.patch
        2 kB
        Jason Edwards
      2. NonEmptyTest_MONDRIAN-695.patch
        3 kB
        Jason Edwards
      3. SqlConstraintUtils_MONDRIAN-695.patch
        4 kB
        Jason Edwards
      4. SqlContextConstraint_MONDRIAN-695.patch
        2 kB
        Jason Edwards

        Activity

        Hide
        Jason Edwards added a comment -

        Patch for non-native NECJ code path

        Show
        Jason Edwards added a comment - Patch for non-native NECJ code path
        Hide
        Jason Edwards added a comment - - edited

        Patch for native nonempty code path

        Show
        Jason Edwards added a comment - - edited Patch for native nonempty code path
        Show
        Luc Boudreau added a comment - Fixed. Added the unit test to keep track of it. http://ci.pentaho.com/job/mondrian-3.2/214/testReport/mondrian.rolap/NonEmptyTest/testNonEmptyCJWithMultiPositionSlicer/
        Hide
        Golda David added a comment -

        As per Luc this is purely programmatic. He has created a unit test for it. The unit test passes and hence this is being marked as resolved.

        Show
        Golda David added a comment - As per Luc this is purely programmatic. He has created a unit test for it. The unit test passes and hence this is being marked as resolved.
        Hide
        Golda David added a comment -

        Validated this issue.

        Show
        Golda David added a comment - Validated this issue.

          People

          • Assignee:
            Golda David
            Reporter:
            Derek Wu
          • Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: